I want to search a field that contains a # in some of the records

M

mcgj

I have a query that I am trying to create and one of the fields I want to use
a criteria on has values in it that are #s. Is there some fuction that I can
use to have these converted to text? I think they are being looked at by
Access as precursors to dates?

Any help would be greatly appreciated.
 
M

mcgj

I forgot to mention that I am using the LIKE statement and as I experiemented
further I found that the LIKE makes it return any number ahead of the value.

So there is are two values in the field of the table #5 and #15. If I use
the LIKE '#5' the query returns 15 instead of 5. How can I set it up to get
5? I want the LIKE statement to stay because I want the user to be able to
use * and get all the records.
 
K

KARL DEWEY

You need to have the criteria thus --
Like "*" & [Enter criteria] & "*"
If they enter '#5' they will get '#5' and '#51' and anything else that
starts with #5.
This can be avoided if there are spaces or common character after the number
by including it in the criteria entry such as this ---
'#5 ' or #5,'
 
J

John W. Vinson

I forgot to mention that I am using the LIKE statement and as I experiemented
further I found that the LIKE makes it return any number ahead of the value.

So there is are two values in the field of the table #5 and #15. If I use
the LIKE '#5' the query returns 15 instead of 5. How can I set it up to get
5? I want the LIKE statement to stay because I want the user to be able to
use * and get all the records.
Click to expand...

Does your criterion contain a literal # character? Because if so, # is a
wildcard for a single numeric digit. That is,

LIKE "#5"

will match 15, or 85, but will not match 5 or 365.

If you want the user to be able to get all the records *or* to match a
specific number, you can use a criterion

WHERE [fieldname]= [Enter number to find:] OR [Enter number to find:] IS NULL

If the user types 5 it will find the records where the field is equal to 5; if
they just hit Enter without typing anything, it will find all records.

John W. Vinson [MVP]
 
J

John W. Vinson

You need to have the criteria thus --
Like "*" & [Enter criteria] & "*"
If they enter '#5' they will get '#5' and '#51' and anything else that
starts with #5.
This can be avoided if there are spaces or common character after the number
by including it in the criteria entry such as this ---
'#5 ' or #5,'
Click to expand...

Actually, they won't - because the # character is ITSELF a wildcard (matching
any numeric digit). A criterion of #5, with the leading and trailing
asterisks, will NOT find a text field containing the string "#5" but will
return all records containing any digit followed by a 5, anywhere within the
string - e.g. "15", "ABC35DEF", "#25" but not "#5".


John W. Vinson [MVP]
 
M

mcgj

Using the information you guys gave me I was able to deduce that your
information combined with the Cstr function does what I want it to do. So
here is what I used.

Like "*" & CStr([Enter Number:]) & "*"

If the user now enters 5 he gets #5 or #15 or just plain old 5. I think
this is going to meet our needs.

Thank you for your help.

John W. Vinson said:
I forgot to mention that I am using the LIKE statement and as I experiemented
further I found that the LIKE makes it return any number ahead of the value.

So there is are two values in the field of the table #5 and #15. If I use
the LIKE '#5' the query returns 15 instead of 5. How can I set it up to get
5? I want the LIKE statement to stay because I want the user to be able to
use * and get all the records.
Click to expand...

Does your criterion contain a literal # character? Because if so, # is a
wildcard for a single numeric digit. That is,

LIKE "#5"

will match 15, or 85, but will not match 5 or 365.

If you want the user to be able to get all the records *or* to match a
specific number, you can use a criterion

WHERE [fieldname]= [Enter number to find:] OR [Enter number to find:] IS NULL

If the user types 5 it will find the records where the field is equal to 5; if
they just hit Enter without typing anything, it will find all records.

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