Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack