Convert Number to text in Access query

[nyamrembo]

Hi,
I have a lookup field that I am trying to query but I am getting "Data type mismatch in expression criteria". The lookup field type is number but field type from original table is text. I tried to change the lookup field to text but when I did that, my query is not yielding results. Is there a way to convert a number field to text? I have checked the web and the forum but I only found text to number.

Thank you much

 

[SkipVought]

Hi,

It depends how the TEXT numeric characters are structured.

Some examples of the Character 1
[tt]
1
1
00001
[/tt]
Each are a DIFFERENT VALUE.

So how are your text numeric characters structured?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nyamrembo]

The field size is long integer...how it's stored, I am not sure, could be 4-8 bytes.

 

[Golom]

A long integer is 8 bytes long.

You can compare text and numeric fields in SQL by doing an on-the-fly conversion of text to numeric assuming that the text field contains only characters that can be converted to numeric values. For example

WHERE Val(SomeTextField) = SomeNumericField

Converting in the other direction (i.e. numeric to text) has the problems that Skip is referring to. For example

Where '0001' = '   1'

returns FALSE even though

Where Val('0001') = Val('   1')

is TRUE.