How do I compare a text field to a number field?

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

Guest

There are 2 sperate databases (not mine and I cannot change them) and they
store the same field in different ways, one as a text and one as a number
field. I need to be able to do a compare between the two. Hoping to do it
without VB code. Any Access tricks?
 
Is there any function built into Access already that can be used? If not,
then just just use this in a module?
 
The type conversion functions are also recognized by Jet for use in queries.
Where exactly are you trying to do this comparison? The more we know, the
easier it is to help.
 
Is there any function built into Access already that can be used? If not,
then just just use this in a module?
Click to expand...

There are several:

Val([textfield])

will return the numeric value of the first numeric substring of the
text field (i.e. Val("123") = 123, Val("123.456") = 123.456,
Val("123ABC") = 123

Format([numfield], #) will return a text value equal to the default
numeric format for the field type; that is, Format(123, #) will return
"123", but Format(123., #) will return "123.00" unless you've set the
default decimals differently.

Format([numfield], "000000") will return a six-character zero filled
text string like "000123".

CStr() will convert a number to a string, honoring the field's default
format (I believe).

John W. Vinson[MVP]
 
Back
Top