WHERE [Characteristics].[abbrev] in ()

[Tom]

Sorry in advance for a rookie question, I've been assigned to convert
an Access application to SQL and I'm starting with essentially no
knowledge of either technology. The SQL Server Migration Assistant
tells me that the following Access Query is not supported in SQL and I
suspect it's the 'in()' that's invalid. Can anyone tell me what the
expression syntax in () is trying to return? Thanks.
SELECT [Characteristics].[description]
FROM characteristics
WHERE [Characteristics].[abbrev] in ()
ORDER BY Description;

 

[Douglas J Steele]

To be valid SQL, you'd need a comma-separated list in between the
parentheses:

SELECT [Characteristics].[description]
FROM characteristics
WHERE [Characteristics].[abbrev] in ("AB", "CD", "EF")
ORDER BY Description;

Do you know how the SQL was generated? If it was being built in VBA code,
based on, say, items selected in a multi-select list box, it looks as though
the list of objects to be searched was blank.

(actually, I suspect the separator may be determined by the List Separator
character in Regional Settings in the Control Panel)

"Tom" wrote in message

Sorry in advance for a rookie question, I've been assigned to convert
an Access application to SQL and I'm starting with essentially no
knowledge of either technology. The SQL Server Migration Assistant
tells me that the following Access Query is not supported in SQL and I
suspect it's the 'in()' that's invalid. Can anyone tell me what the
expression syntax in () is trying to return? Thanks.
SELECT [Characteristics].[description]
FROM characteristics
WHERE [Characteristics].[abbrev] in ()
ORDER BY Description;