How to convert a string to number or vice vese?

[Ai_Jun_Zhang]

Hi!

I have two tables Atable and BTable and both have a field called AcctNo. In Atable, it is type of Number and in BTable, it is type of Text. When I do a join with these two tables

select * from Atable, BTable where Atable.AcctNo=Btable.AcctNo

I get error saying "type mismatch".

My question here is: How can I convert a type from number to string or string to number in my sql statement?


Thanks,

Aijun.

 

[RuralGuy]

If AcctNo in Atable is a Long Integer then:
select * from Atable, BTable where Atable.AcctNo=Clng(Btable.AcctNo)

Look up Conversion in VBA help from any code page.

 

[ScottGem]

Why are these fields different data types if they are used to join the tables. What you should be doing is converting the field permanently. I would add a Long Integer field to TableB and run an Update query:

UPDATE TableB SET NewAcctNo = Val([AcctNo]);

Then delete the AcctNo field and rename NewAcctNo to Acctno.

 

[WayneRyan]

aijun,

RG's CLng function is the way to join the tables and Scott is
right that you should permanently convert the field.

In the process, there is somewhere that "generates the string key"
to feed the other table. You have to find that place and convert
the string value it currently has and use the CLng function so
that it produces the new, correct value.

Wayne