The criteria needs to match the data type of the field.
When you open the table in design view, if the field is Text, you must use
the quotes as delimters. If the field is Date/Time, you must use the # as
delimiter. If the field is Number, you must not use any character as
delimiter.
If the field is a calculated quey field, it still has a data type. For
example, Text fields align left, whereas numbers and dates align right
when you view the query output. You can use CLng(), CCur(), CDbl(),
CVDate() etc to coerce Access into understanding the data type so that it
matches the way you formed your criteria. More info on that:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
You can force the field to be treated as Text by using the Format()
function. For example, you might type this kind of thing into the Field
row in your query:
Format([MyNumberField], "0000.00")
Now it is a text field, so you can match the text.
There is a way to cheat and use the quotes, by using the Like operator. In
general, I'm not sure that's a good idea though.
Hopefully you are aware that a text sort works character by character
(left to right), so it sorts like this:
1
1001
19
2
21
39999
4
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Parker said:
I can use this syntax to filter by this if the value is formatted as a
number (" & Me!levellist.Value & "). I would like to have the value
formatted as text. I've tried ("'" & Me!levellist.Value & '"") and (" &
'Me!levellist.Value' & ") to no avail. I if someone could tell me what I'm
doing wrong or direct me somewhere for this information I would be
grateful. Thank-you.
