Number Wildcards in String

[pjordan@drcog.org]

For some reason I never had an issue with this sort of thing, but currently I can't seem to get my queries to work.

I have a TEXT field called LOCATION and it has various intersections and addresses in it and have many different formats.

What I'm trying to do, is just query out any one that has a combination of 1 or more numbers after the & in the field.



This is what I've been using before

Like "*& # *"

so it will return records such as:

SMITH RD & 5 MILL LANE

Then I'd revise the query after and then search for ones that have 2 numbers and so on and fix those:

Like "*& ## *"

I can't seem to get this to work.

Now if I query out just records such as
MAIN ST & 967

I can use the

Like "*& ###"

and it works find and returns all double digits.

But I need to have it to find those that also have text after the space as in the above codes and I no longer can seem to get it to search for it using the wild card "*"...

[Micron]

If I understand, you want to find any record where there is a number in the field but it doesn't really matter if it's 1 digit or more, and perhaps doesn't really matter what comes before or after the number. If that's not correct, I suggest you clarify what the goal is rather than what doesn't work. I'm wondering if Regex would help. However, I'm not the one to guide you on that but I can say I've seen others post here with regex that provided simple solutions to similar issues that were more complex than what you might be doing. First though, we'd have to make sure we know what the outcome is supposed to be.

[pjordan@drcog.org]

If I understand, you want to find any record where there is a number in the field but it doesn't really matter if it's 1 digit or more, and perhaps doesn't really matter what comes before or after the number. If that's not correct, I suggest you clarify what the goal is rather than what doesn't work. I'm wondering if Regex would help. However, I'm not the one to guide you on that but I can say I've seen others post here with regex that provided simple solutions to similar issues that were more complex than what you might be doing. First though, we'd have to make sure we know what the outcome is supposed to be.

Close. I'm trying to find any record that has a number right after the &, and then have text after the number.

So these are the types of records I'm hoping to return:
TEST ST & 1234 TEST
TEST ST & 999999 SOMETHING
TEST ST & 3837 DLFHEROUPB

These are Not records I want to return:
TEST ST & 1234
TEST ST & 99TH
TEST ST & 32ND STREET

Hope that clears it up.

[CJ_London]

Then I'd revise the query after and then search for ones that have 2 numbers and so on and fix those:
Code:
Like "*& ## *"
I can't seem to get this to work.

please clary what 'I can't seem to get this to work' means

I just tried it on test data and it returned the expected records

[pjordan@drcog.org]

please clary what 'I can't seem to get this to work' means

I just tried it on test data and it returned the expected records

See when I do it, I'm not returning any records. It worked before, but I'm using the exact same code and I can't seem to get it to return anything, despite there being records with that type of combination in it.

Maybe there is something else going on, as I do this every year using the same queries, except this year it's just not working and thought maybe there's a new way to do it?

Since you can get it to work, then I'll do some testing and see if its a weird corruption of the data or something then...

[Micron]

There might be something awry with your data since last year. If you can't figure it out after additional testing consider posting a zipped copy of your db so the data can be examined.

[Micron]

I just tried it on test data and it returned the expected records

Interesting. I tried all 3 versions on the samples provided and got nothing at all.

[CJ_London]

does the single digit query work?

and another question - you are using an access table, not sql server?

and slightly off topic but

Then I'd revise the query after and then search for ones that have 2 numbers and so on and fix those:

why revise? why not use OR

Like "*& # *" OR Like "*& ## *" OR Like "*& ### *"

[pjordan@drcog.org]

Yes the original filter I have always used was:

Like "*& # *" Or Like "*& ## *" Or Like "*& ### *" Or Like "*& #### *" Or Like "*& ##### *" Or Like "*& ###### *"

Did a test on a new table and that seemed to work as it always has. Not sure why it wasn't working in this table (about 70,000+ records), but for some reason, this year apparently after sifting through the data I wasn't actually able to find any instances of the situation at all. So maybe they had cleaned it up this year. Just figured it's usually quite a number of them and was surprised I wasn't finding any. But that's a good thing, despite throwing me off.

[CJ_London]

Interesting. I tried all 3 versions on the samples provided and got nothing at all.

just copy/pasted the exact samples provided and worked OK for me - you need to have #### or ###### in the like comparison.

@pjordan - always worthwhile to identify at least one instance when you don't get the result you expect

despite there being records with that type of combination in it

looks like you assumed and did not confirm

[Micron]

Not sure what the problem was, but OK now. Did a little M$ reading and it seems that special characters to be taken literally should be in [] although they didn't include the ampersand. Using & or [&] doesn't seem to make a difference

[pjordan@drcog.org]

Not sure what the problem was, but OK now. Did a little M$ reading and it seems that special characters to be taken literally should be in [] although they didn't include the ampersand. Using & or [&] doesn't seem to make a difference

Yes it was strange and I swore I saw some records that would have passed the filter, but guess I just saw it wrong while I was doing another unrelated filter...