Solved: Wildcard Characters Question

[malik641]

Hey All,

Just curious to know if the following is possible. When searching for different characters, the criteria 'Like "b[ae]ll"' will find values such as "ball", and "bell". Now, when searching a range of characters like 'Like "b[a-i]ll"' this will find "ball", "bell", "bill", etc.

The text I'm looking for (a small example) is one any of these items:
2-pt
2pt
3-pt
3pt
4-pt
4pt

So, how can I perform a search to look for a hyphen "-" or NO hyphen at all "" all in one search? Right now I'm using 2 search criterias:
Like "[2-4]-pt"
Like "[2-4]pt"

I have a feeling there's a way to find this. I'm just having a hard time figuring it out.

Thanks,
Joseph

[mdmackillop]

I'm no expert on this, but how about [2-4]*pt

[johnske]

Like "*-*"

[Bob Phillips]

They let anything through

'-----------------------------------------------------------------
Public Function IsValid(Inp As String, Pattern As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = Pattern
IsValid = .Test(Inp)
End With
Set oRegEx = Nothing
End Function


and run like so



?IsValid ("4-pt","[2-4]|-pt")

[malik641]

Thanks Malcolm and John. but as Bob said, they let anything through.


Bob,


I was hoping for a non-VBA solution to this, although I will try out the regular expressions code you gave me because I need to start learning that. What I'm doing is......well, first let me start by stating that I'm new to Access development and I haven't done much Database development. Okay, that being said, what I'm doing is I created a small Access database that links (appropriate) tables from our main SQL database to perform some simple queries. The queries look at a text field and part of my criteria was what I asked in the first post. Instead of placing the same text inside all the queries, I decided to put them in a table called tCriteria. All the queries now refer to that one table for easy modification. As I was building the table, this question came up. That's basically why I'm not looking for a VBA solution. Is there a non-VBA solution to this?


Thanks

[Norie]

Malik

It might help if you told us where you are doing this search.

Different applications have different wildcards and also different ways of dealing with special characters.

[malik641]

I thought I did :)


Okay, that being said, what I'm doing is I created a small Access database that links (appropriate) tables from our main SQL database to perform some simple queries.

[Norie]

Malik

I'm sorry but it still isn't clear, to me anyway, where this is happening, but that's probably just me on a Friday eveining.

Are you creating queries in Access?

[malik641]

Norie,

Sorry for not being clear. Yes, everything I'm doing is in access. But, in my access database that I created I'm linking tables from my company's SQL Server database. So the queries I'm referring to for this thread are queries that search and filter through the SQL Server database, but I believe that the text comparisons I perform ('Like "[2-3]pt"') are evaluated in Access...but now that I think about it, I'm not so sure. They should be evaluated in Access, since the asterisk * is not bringing up any errors.

[Norie]

Joseph

I think this is a bit beyond me, it's been some time since I worked with Access and other databases.

In fact I normally just used Access alone, importing the data from the legacy databases.

I did find this in the Help files but I don't actually understand it.:dunno


A hyphen (–) can appear either at the beginning (after an exclamation point if one is used) or at the end of charlist to match itself. In any other location, the hyphen is used to identify a range of characters.

[johnske]

Thanks Malcolm and John. but as Bob said, they let anything through...
:doh: Sorry for being a bit thick, but I thought the requirement was just to "perform a search to look for a hyphen" - any hyphen

[malik641]

No problem John.

I've made a small Access file so everyone knows what I'm talking about. Forgive me if this is a mess, but it works sooo :rolleyes:.

Anyway there are 3 main tables: tChoose, tCriteria, and tText
tText: Holds the text to perform the query on
tCriteria: Has criteria examples I came up with to test out
tChoose: Used to hold a single record from tCriteria that the following query uses in its WHERE clause

There is 1 query: qChoose
qChoose: Returns the filtered records of tText using tChoose.Criteria in the WHERE clause as 'Like tChoose.Criteria'

There is 1 form: fChoose
fChoose: Bound to the tChoose table. This is where you wil choose the criteria you want to filter from tText. The more records you add, the query will treat it as "This OR This OR This". You'll see when you look at the form.


I hope this makes sense. And I hope this will bring us to the same page (and gives us something reasonable to work with). Let me know what you guys think :)

[malik641]

By the way, I've moved this thread to Access Help since Norie pointed out how different applications use wildcards differently. Plus, Access is what I'm developing this in.

[omocaig]

Joseph

I think this is a bit beyond me, it's been some time since I worked with Access and other databases.

In fact I normally just used Access alone, importing the data from the legacy databases.

I did find this in the Help files but I don't actually understand it.:dunno
Hey Norie,

That quote you found from the help, I believe it's saying that when you're searching for the hyphen you can put it at the begining or end of your bracketed list, if you place it in the middle then it means you want a range search (i.e. you're not looking for the hyphen). If however you are using the exclamation point, which means that you do not want the characters in your list, then you must put the hyphen after the exclamation mark.

why am I thinking I didn't make this any clearer?

hth,
Giacomo

[omocaig]

Joseph,

except for possibly the regEx option I don't think you're going to find a better solution than Like "[2-4]-pt" OR Like "[2-4]pt". Especially since you're doing this in a form and it doesn't sound like you can use any functions on the left hand side, only the right.

Anyway the reason for my post is that I wanted to ask if 2-pt and 2pt are considered the same thing? and if so then isn't the issue really more about tightening up data entry so that whether the user enters 2-pt or 2pt at the data entry point it's saved in the database in a consistent format?

Sorry if it seems I taking this off on a tangent, but IMO that's the root cause.

hth,
Giacomo

[malik641]

except for possibly the regEx option I don't think you're going to find a better solution than Like "[2-4]-pt" OR Like "[2-4]pt". Especially since you're doing this in a form and it doesn't sound like you can use any functions on the left hand side, only the right.
RegEx seems like going overboard for a small task such as this, which is why I'm not going to use it. I think you may be right that there is no better solution than what I've been using. But I just have this feeling there is :)


Anyway the reason for my post is that I wanted to ask if 2-pt and 2pt are considered the same thing? and if so then isn't the issue really more about tightening up data entry so that whether the user enters 2-pt or 2pt at the data entry point it's saved in the database in a consistent format? Yes, 2-pt and 2pt are considered the same. And no, because the field I'm searching is merely a description of the item, there will be entries that do not consist of "2-pt" or any "#-pt" OR "#pt". So it's up to the user to input that portion (if required). There has not been a defined way to enter it, so the users enter either "2-pt" or "2pt". So the issue does have to deal with users and consistency. But please, do not get me started on users and consistency (especially at my job) :)

Unfortunately, the database is not set up in such a way that the 2-pt or 2pt is entered by the system itself. Leaving room for the inconsistency. And I'm not about to go through and change all 87,000+ items that possibly contain 2-pt or 2pt (whichever I want to change). It's just not worth it because the users probably won't remember and will continue to enter it in whatever format they choose.

But about the wildcard question. I'm confused at why you can have a character list like [ae] that will look for "a" OR "e" and not something like "a" or "[null]". I figured since [] is considered a zero-length string (""), then the following criteria 'LIKE "*[2-4][-[]]pt*"' should look for "-" or "". But it's not working like that.

In my example, 'LIKE "[1-4][-ae]pt"' will find "1-pt, 1apt, 1ept, etc". So it says "- OR a OR e". I wonder why it can't find '- OR ""'. Maybe it's because when I look for a character list [...] it's stating that [...] is a place holder for a character. And saying [ae[]] won't work because it can't look for "" ([]) because it's expecting a character regardless. And yet, it's a legal wildcard character list???

I've yet to find any information about advanced wildcard searches. I guess it's not that popular.

[omocaig]

I know what you're say about the null, seems like there should be a way of doing that but I couldn't figure it out either. When I first saw your question I thought the solution was going to be [2-4][-p][pt][t] but that only returns #-pt because of the [t] so you still end up needing an or is null kinda solution

[malik641]

I see your point. I gotta give ya credit, though. That solution looked pretty cool :)



...but now that I look at it further. It could potentially cause some problems. It could result in:
LIKE "[2-4][-p][pt][t]"
(returns)

2-tt
2ppt
2ptt
2-pt

IF those texts exists at all.
When I only want the last one (and 2pt).

Still...nice try.

[malik641]

They let anything through

'-----------------------------------------------------------------
Public Function IsValid(Inp As String, Pattern As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = Pattern
IsValid = .Test(Inp)
End With
Set oRegEx = Nothing
End Function

and run like so



?IsValid ("4-pt","[2-4]|-pt")

Bob,

I don't think I ever thanked you for the code, so thank you. I am now starting to get into Regular Expressions and am having a blast with it :)

I'm not sure if I will go back to my old project (the one this thread was all about) and use this code there, but I know I will definitely be using this code snippet for my future use of RegEx :) Thanks again

Thread Solved