VALUE Worksheet Function?

[Anthem]

Hello-

I am attempting to use the VALUE worksheet function in a Query. The field in the table that I am querying is a text field that usually contains numbers, but can also contain text. That is why I have it set to text. Anyways I want to do a Between search on the field, ie : Between 99999999 and 999999999999. I tried to use the Value worksheet function to do this but I keep getting a data mismatch error. In my query I created an expression as follows;

VariableField: Value([Field])

Does anyone have any suggestions as to how to accomplish this using the Value function? Or perhaps does anyone have any other ideas?

Thanks,

David

 

[Nikita6003]

Hi Anthem,

Use VAL in Access to convert text to numbers, but BEWARE: VAL will pick up on any string that CONTAINS a number and return the number part & ignore the text part.

If you use VAL([Field]) with a criterium of Between 100 And 1000 you'll not only get all numbers between 100 and 1000, but also alls trings where the numeric part falls between 100 and 1000. Use IsNumeric to determine if a textfield contains a numeric value or not

Hope this helps

Cheers
Nikki