Type mismatch in expression

[marksmithy69]

I'm completely new to MS Access so I appologize for the simple question, but how do I write a simple query comparing two fields of different data types? Here is my query:

SELECT [ProEmployeeMaster].[last_name], [ProEmployeeMaster].[first_name], [ProEmployeeMaster].[ssnum], [ProEmployeeMaster].[GlobalID], [SapEmployeeMaster].[GlobalID]
FROM ProEmployeeMaster INNER JOIN SapEmployeeMaster ON FORMAT([ProEmployeeMaster].[ssnum],"000000000")=[SapEmployeeMaster].[SSN]
WHERE [ProEmployeeMaster.GlobalID]=[SapEmployeeMaster].[GlobalID];


Basically I am using the GlobalId on the ProEmployeeMaster table, which is a text data type and comparing it to the GlobalId on the SapEmployeeMaster table, which is a number format. Thanks in advance for your help.

 

[BSman]

Try changing the where clause to read:

WHERE int([ProEmployeeMaster.GlobalID])=[SapEmployeeMaster].[GlobalID]

That should convert the first GlabalID to a number so the two can be compared.

It's generally a good idea to use ID as part of a column name only where you are dealing with a number column (frequently an autonumber column). Use "code" or something similar when naming a (primary) key field that is a text type.

 

[marksmithy69]

Thanks for your input, but I am still getting the same error when I use

WHERE int([ProEmployeeMaster.GlobalID])=[SapEmployeeMaster].[GlobalID].

Still says "Type mismatch in expression". Thanks.

 

[BSman]

Try rebuilding the expression from scratch using the Access query builder and compare the resulting SQL statement to what you wrote. Your error is probably in the ON FORMAT part of your expression, but the designer will write the code correctly.

 

[marksmithy69]

I tried that way as well...the query now runs for a minute or so and comes back with a little different message "Data type mismatch in criteria expression". Before the error came up right away, but now the Running Query status bar at the bottom increases for a minute or so and then brings back this error. Thanks again.

 

[PaulF]

you might try

WHERE Nz([ProEmployeeMaster.GlobalID],0)=Nz([SapEmployeeMaster].[GlobalID],0)


to account for records without a GobalID
PaulF

 

[BSman]

What's the SQL statement that was created by the query builder? I think that your problem may be related to the format statement for the SSN. How is the SSN stored in each table?

You might try making a query on ProEmployeeMaster that selects all records, but redefines ssnum as SSN using your format statement and redefines GlobalID as an integer. Then create your two table query joining on SSN and GlobalID in both tables. Assuming the first query is called qselProEmployeeMaster, the final query would be something like:

SELECT [qselProEmployeeMaster].[last_name], [qselProEmployeeMaster].[first_name], [qselProEmployeeMaster].[SSN], [qselProEmployeeMaster].[GlobalID], [SapEmployeeMaster].[GlobalID]
FROM qselProEmployeeMaster INNER JOIN SapEmployeeMaster ON ([qselProEmployeeMaster].[SSN]=[qselSapEmployeeMaster].[SSN] and ([qselProEmployeeMaster.GlobalID]=[SapEmployeeMaster].[GlobalID]);

By the way, I assume you actually will be getting more data from SapEmployeeMaster than GlobalID. Otherwise there doesn't seem to be any reason to join the two tables.