Wildcard and Not Like

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to write a query where I allow the user to enter a start and end
number as the criteria for the query. This is working well and returning all
the info I want. However, it is also returning values that I don't need. In
order to narrow it down further, I would like to have it take the numbers
entered by the user and weed out those where the 4th character is an 8. The
character structure for the numbers is M1001R0.
To find all between M1001R0 and M1099R0 is done as follows:
The user enters the starting number of M1001 and the ending number of M1099.
The criteria is-- Between [Frame Seq start] And [Frame Seq end] & "zzzzzR0".
This gives me all numbers between M1001R0 and M1099R0 without the user
having to enter the R0. I tried adding-- And Not Like "***8*R0", and this
dropped everything containing an 8 anywhere in the characters. I've tried
several variations, but they all either drop all items with an "8" or none.
Does anyone have an idea for me??

Thanks in advance!!
 
I am trying to write a query where I allow the user to enter a start and end
number as the criteria for the query. This is working well and returning all
the info I want. However, it is also returning values that I don't need. In
order to narrow it down further, I would like to have it take the numbers
entered by the user and weed out those where the 4th character is an 8. The
character structure for the numbers is M1001R0.
To find all between M1001R0 and M1099R0 is done as follows:
The user enters the starting number of M1001 and the ending number of M1099.
The criteria is-- Between [Frame Seq start] And [Frame Seq end] & "zzzzzR0".
This gives me all numbers between M1001R0 and M1099R0 without the user
having to enter the R0. I tried adding-- And Not Like "***8*R0", and this
dropped everything containing an 8 anywhere in the characters. I've tried
several variations, but they all either drop all items with an "8" or none.
Does anyone have an idea for me??

Thanks in advance!!
Click to expand...

You're suffering because you're using what's called an "intelligent
key" - a field which consists of several subfields. The text string
M1001R0 IS NOT A NUMBER, and will not let you sort numerically.

Is there any chance that you could split this field into (perhaps)
three fields, the "M", an Integer, and the "R0"?

If not, try

NOT LIKE "?##8#*"

The * wildcard means *ANY* substring of any length; you can be more
specific by using ? - any single character - or # - any numeric digit.
You'll also want to change the BETWEEN, I suspect: try
= [Frame Seq start] AND <= [Frame Seq end]
Click to expand...


John W. Vinson[MVP]
 
This worked great. Thanks very much for your help. I've always been
confused about which wildcard did what.

John Vinson said:
I am trying to write a query where I allow the user to enter a start and end
number as the criteria for the query. This is working well and returning all
the info I want. However, it is also returning values that I don't need. In
order to narrow it down further, I would like to have it take the numbers
entered by the user and weed out those where the 4th character is an 8. The
character structure for the numbers is M1001R0.
To find all between M1001R0 and M1099R0 is done as follows:
The user enters the starting number of M1001 and the ending number of M1099.
The criteria is-- Between [Frame Seq start] And [Frame Seq end] & "zzzzzR0".
This gives me all numbers between M1001R0 and M1099R0 without the user
having to enter the R0. I tried adding-- And Not Like "***8*R0", and this
dropped everything containing an 8 anywhere in the characters. I've tried
several variations, but they all either drop all items with an "8" or none.
Does anyone have an idea for me??

Thanks in advance!!
Click to expand...

You're suffering because you're using what's called an "intelligent
key" - a field which consists of several subfields. The text string
M1001R0 IS NOT A NUMBER, and will not let you sort numerically.

Is there any chance that you could split this field into (perhaps)
three fields, the "M", an Integer, and the "R0"?

If not, try

NOT LIKE "?##8#*"

The * wildcard means *ANY* substring of any length; you can be more
specific by using ? - any single character - or # - any numeric digit.
You'll also want to change the BETWEEN, I suspect: try
= [Frame Seq start] AND <= [Frame Seq end]
Click to expand...


John W. Vinson[MVP]
Click to expand...
 
Back
Top