text to number query

[norm]

I am using Access 2003, I have 2 seperate databases, each one has a field
called "Vehicles" but unfortunately one database is storing the vehicles as
text and the other is stroring as number. Reason this is done is because we
have vehicles stored in this format A1234, while the other one is storing
1234 with another field called "vehChar" as A. I guess I should export the
table with the "numeric" and vehChar and merge them in Excel, then bring it
back into access. I was just wodering if I am on the right track or can I
create an Expression in Access to join the two fields in a query?
Thanks for your time

 

[John Spencer]

You can join on an expression. You can't use the design view (query
grid) to do so. It cannot handle it

SELECT V.*, V2.*
FROM Vehicles as V INNER JOIN OtherTable as V2
On V.IDNum = V2.VehChar & V2.VehNumbers

If you can only use the query grid then
-- Open a query
-- Add both tables
-- Select the field you want to see
-- Join the two tables by dragging from IDnumber to NumberField
-- Switch views Menu: View: SQL
-- Find the section of the statement that says xxx INNER JOIN yyy ON
and edit it to read
xxx INNER JOIN yyy ON xxx.IDNUM = yyy.VehChar & yyy.VehNumbers


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================