Doug Coats Doug Coats - 4 months ago 33
SQL Question

Joining Two Tables with Different Data types MS ACCESS

I am trying to experiment with joining different data types from different tables. I thought I had the syntax correct but apparently I must be doing something wrong.

The syntax error says type mismatch in expression so I am sure I am not correctly converting the data types

Here is my join

SELECT CLng([Inventory].ItemID) AS [Item#],
Sum([Invoice].Quantity) AS [Order QTY]
FROM [Invoice] INNER JOIN [Inventory] ON [Invoice].ItemID = CLng([Inventory].ItemID)
WHERE ((([Invoice].OrderNumber) BETWEEN 1 And 23))
GROUP BY CLng([Inventory].ItemID);

Answer

Why where inside () ? try remove and convert number to str

 SELECT CLng([Inventory].ItemID) AS [Item#],
 Sum([Invoice].Quantity) AS [Order QTY] 
 FROM [Invoice] INNER JOIN [Inventory] ON  Str ( [Invoice].ItemID)  =  [Inventory].ItemID
 WHERE [Invoice].OrderNumber) BETWEEN 1 And 23
 GROUP BY CLng([Inventory].ItemID); 

or

  SELECT CLng([Inventory].ItemID) AS [Item#],
 Sum([Invoice].Quantity) AS [Order QTY] 
 FROM [Invoice] INNER JOIN [Inventory] ON CLng( Str ( [Invoice].ItemID))  = CLng( [Inventory].ItemID)
 WHERE [Invoice].OrderNumber) BETWEEN 1 And 23
 GROUP BY CLng([Inventory].ItemID);