Quotation Marks in SQL Statement

[Skee777]

Hello,

Can someone help me with this problem.

I have a SQL statement that looks like this;

strSQL = "SELECT * FROM Transaction, "Unit-Participator" WHERE Transaction.T_TRAN_NUMBER = "Unit-Participator".U_TRAN_NUMBER AND ((Transaction.T_MLS_NUMBER='990616'))"

The problem is that the Unit-Participator field needs quotation marks around it because of the hyphen. Unfortunatley I cannot change the field name.

I have tried changing the double quotes to single and get the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

ODBC driver does not support the requested properties.

I know the sql connection works because if I remove the Unit-Participator portion, my query returns the correct results. I also know that this query will work, because I used Microsoft Query to test it, but it needs the quotes. When I try to put the query together in Microsoft Query using single quotes, it objects.

How can I use quotes in an ASP SQL statement?

Thanks for your thoughts

Why isn't phonetic spelled the way it sounds?

 

[essnrv]

Try this

strSQL = "SELECT * FROM [Transaction],[Unit-Participator] WHERE [Transaction].[T_TRAN_NUMBER] = [Unit-Participator].[U_TRAN_NUMBER] AND (([Transaction].[T_MLS_NUMBER]='990616'))"

 

[Skee777]

Thanks essnrv,

Unfortunatly it produced the same error. I did find someone that said to use "" like this.

	strSQL = "SELECT * FROM Transaction, ""Unit-Participator"" WHERE Transaction.T_TRAN_NUMBER = ""Unit-Participator"".U_TRAN_NUMBER AND ((Transaction.T_MLS_NUMBER='990616'))"

I believe this will work for me.

Thanks again for your assistance.

Why isn't phonetic spelled the way it sounds?

 

[snowboardr]

How about:

strSQL = "SELECT * FROM Transaction" _
& ", " _
& Chr(34) & "Unit-Participator" & Chr(34)_
& " WHERE Transaction.T_TRAN_NUMBER = "_
& Chr(34) & "Unit-Participator" & Chr(34) _
& ".U_TRAN_NUMBER AND " _
& ((Transaction.T_MLS_NUMBER='990616'))"

www.vzio.com
ASP WEB DEVELOPMENT