Concatenate, data type mismatch error

[kuslusr1]

I am attemping to use Duane Hookom's code to return a list of item numbers
and concatenate the equipment numbers where those items are used. The items
numbers are listed in the Inventory table, the item numbers and the
associated equipment numbers are in the Sparepart table. I am using Access
2003 and the following expression:

InvUsage: Concatenate("SELECT EQNUM FROM Sparepart WHERE ITEMNUM =" &
[ITEMNUM])

I receive the following error.

Run-time error '-2147217913 (8004e07)': Data type mismatch in criteria
expression. I checked the data types on both tables and they are both text
fields, any help with this issue would be greatly appreciated.

 

[John W. Vinson]

I am attemping to use Duane Hookom's code to return a list of item numbers
and concatenate the equipment numbers where those items are used. The items
numbers are listed in the Inventory table, the item numbers and the
associated equipment numbers are in the Sparepart table. I am using Access
2003 and the following expression:

InvUsage: Concatenate("SELECT EQNUM FROM Sparepart WHERE ITEMNUM =" &
[ITEMNUM])

I receive the following error.

Run-time error '-2147217913 (8004e07)': Data type mismatch in criteria
expression. I checked the data types on both tables and they are both text
fields, any help with this issue would be greatly appreciated.

Text fields require quotemarks around the criterion. Try

InvUsage: Concatenate("SELECT EQNUM FROM Sparepart WHERE ITEMNUM ='" &
[ITEMNUM] & "'")

For readability (don't do it this way, this is just to show you what's there)
that's

InvUsage: Concatenate("SELECT EQNUM FROM Sparepart WHERE ITEMNUM =' " &
[ITEMNUM] & " ' ")

 

[kuslusr1]

Thanks John for the quick response and the right solution.
--
kuslusr1

I am attemping to use Duane Hookom's code to return a list of item numbers
and concatenate the equipment numbers where those items are used. The items
numbers are listed in the Inventory table, the item numbers and the
associated equipment numbers are in the Sparepart table. I am using Access
2003 and the following expression:

InvUsage: Concatenate("SELECT EQNUM FROM Sparepart WHERE ITEMNUM =" &
[ITEMNUM])

I receive the following error.

Run-time error '-2147217913 (8004e07)': Data type mismatch in criteria
expression. I checked the data types on both tables and they are both text
fields, any help with this issue would be greatly appreciated.

Text fields require quotemarks around the criterion. Try

InvUsage: Concatenate("SELECT EQNUM FROM Sparepart WHERE ITEMNUM ='" &
[ITEMNUM] & "'")

For readability (don't do it this way, this is just to show you what's there)
that's

InvUsage: Concatenate("SELECT EQNUM FROM Sparepart WHERE ITEMNUM =' " &
[ITEMNUM] & " ' ")