Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VALUE Worksheet Function?

Status
Not open for further replies.

Anthem

MIS
Nov 14, 2002
6
US
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
 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top