Query based on incompatible string lengths

[dwil98059]

I have two disparate data sources that each contain an Employee ID field that I need to perform a join with. One data source enforces a 6 character length, the other data source doesn't. Because of this, Employee IDs in one system are 6 characters long, with preceeding zeros, but the other data source isn't (e.g. 0000405 in system one, but 405 in system two). Even though this is the same employee, I can't figure out how to join the two datasets.

Any ideas?

[kennejd]

Something like this (assuming the employer2 Employee ID is currently formatted like '0000405'):
select
E1.*
,E2.*
from Employer1 E1
InnerJoin Employer2 E2
on E1.EmpID = CLng(E2.EmpID)

[dwil98059]

Any chance of an MS-Access built-in function performing the same thing? This is just a small database, performing a query to pass data out to an Excel spreadsheet. If possible, I'd like to do the join in the MS-Access query builder and not have to build code.

Thanks

[dwil98059]

Thanks for your reply to my MS-Access query question. Can you think of any built-in function within Access that would do something similar? If possible, I want to use the Access query builder to complete this project -- our typical users won't be able to navigate VBA code! Thanks.

[ketbdnetbp]

Create a query using the query builder for the data source with “000405” as the employeeID

Create another query for the data source with “405” as the employeeID

In each of the two queries add a NEW column, RevisedempID: ([employeeID]+100000)

This assumes that there is at least 1 preceeding zero in the employeeID field in each of the rows in the table using 6 digits as the employeeID

Then try to join the two queries based on the RevisedempID

I have not tested this yet, it’s just an idea…