query that joins and expression to a table data type mismatch

[Ksinay]

I have table(table1) RxNum that is set as a number and each entry has 11 numbers. I only need the first 8. I made a query (query1) to take the last 3 digits off. I have another table (table2)with that has the RxNum with the required 8 digits so I can find the matches. I made a second query (query2) that includes query1 and table2 to match the RxNum fields. I get a "type mismatch in expression". I have been trying to figure out how to change the expression in query2 to convert to a number but I cant figure out. I don't know code but could try if that is the only way to fix the expression to yield the results as a number.

[ssanfu]

When is a number not a number? When it is in a text type field.

It sounds like one field is a text type field and the other is a numeric field (Integer/Long/Single or Double).

In Query1, maybe try:

Val(Left(RxNum,8))

[Ksinay]

YEEEESSSSS!!!!! I have been searching on-line for 2 hours. THANKS!

[ssanfu]

Great..

Ready to mark this solved?

[Ksinay]

Yes, I am new to this.. where do you select that option

[ssanfu]

Top of the page... Thread Tools (in (2nd) the green bar)