CODEWITHSUNDEEP
sqlms-access
Doug Coats
Doug Coats

Reputation: 7107

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);

Upvotes: 1

Views: 2090

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

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);

Upvotes: 1

Related Questions