How can I change text to number data type in a query? 1

[GS2]

I have a process that imports a file and matches the records to an existing table by a field "CheckNo" to update certain fields in the existing table. Both the imported and existing-table check numbers are defined as text data types.

The problem is that the imported check numbers are padded with zeros and the check numbers in the existing table may or may not have these leading zeros resulting in no matches some of the time. Changing the data type of the field in the existing table is not an option due to this being a third party software package.

I thought if I could convert both fields to a data type of numeric in the query itself that it would eliminate the leading zeros. But haven't been successful in accomplishing this. Suggestions?

 

[Tim1]

Hiya,

I think that you are on the only sensible track with what you are trying to do. The SQL that you need to convert from a text to a number is:

CONVERT (newdatatype, field)

So, assuming your field name is CheckNo, to convert that, the command is

WHERE CONVERT(INT, CheckNo) = CONVERT(INT, importCheckNo).

Hope this makes sense,

Tim

 

[GS2]

Thank you for your response, it made perfect sense but unfortunately didn't work. It returns an error of undefined fuction. I am working in Microsoft's Access 2000. Any other suggestions?

 

[Tim1]

Hiya,

Try the command VAL(CheckNo).

That works OK in Access 97, hopefully Mr Gates won't have changed it again!!

 

[GS2]

Thanks once again. This time better results. It appears that it worked.

I was joining the imported file and the existing table using the check number fields and tried using the VAL function in the join statement and it didn't work - got a "Microsoft Access Can't represent the join expression". But I took away the join using those two fields and added the WHERE clause and used the VAL fuction and that worked. Why it works in the WHERE clause and not the Join statement I have no idea......bottom line for me, it works!

Thank you Tim1