SQL statements in Visual Basic 1

[Melee]

Using the 'DoCmd.RunSQL' in a Private Sub Command_Click(), I can not replicate the results when the same singular statement was ran as a query. To clarify, I have a basic SQl query generated in Access. I am trying to use this fuctional query to create a table at the click of a button. My statement almost works in VB. VB seems to have a problem with the IIf value if true. If the IIf statement is True I would like to insert a character string into the cell. Double quotes around the True value creates an error in the run, and without the quotes either a blank or a -1 is inserted into the cell, in VB.

How do I get VB to acknowledge the SQL character string?

Thank you,
Lee Kendall

 

[raskew]

Please post your code.

 

[RonRegan]

Post the code that you have, please....

Ron


Independent Consultant

Tampa Bay, Florida

 

[Melee]

As requested here is the code.

Private Sub Command203_Click()
On Error GoTo Err_Command203_Click

DoCmd.RunSQL "INSERT INTO [Product Detail Table for Query] ( CompanyName, Checked ) SELECT [ProductsV tbl].[Company Name],IIf([ProductsV tbl].[Roses 06024]=-1,"[Roses 06024]",Null) AS 1 FROM [ProductsV tbl] WHERE (((IIf([ProductsV tbl].[Roses 06024]=[Roses 06024], [Roses 06024] ,Null))=True));"

Exit_Command203_Click:
Exit Sub

Err_Command203_Click:
MsgBox Err.Description
Resume Exit_Command203_Click

End Sub

Lee

 

[xtreme1]

What error is created with the double quotes?

xtreme1
rbio@excite.com

 

[Melee]

Compile error:
Expected: end of statement.

Lee

 

[albywalt]

Replace the quotes with the Chr function; Chr(34) returns one double quote.

DoCmd.RunSQL "INSERT INTO [Product Detail Table for Query] ( CompanyName, Checked ) SELECT [ProductsV tbl].[Company
Name],IIf([ProductsV tbl].[Roses 06024]=-1," & Chr(34) & "[Roses 06024]" & Chr(34) & ",Null) AS 1 FROM [ProductsV tbl] WHERE (((IIf([ProductsV
tbl].[Roses 06024]=[Roses 06024], [Roses 06024] ,Null))=True));"