Access Update SQL run time error 3075

[jeanvliet2012]

Could someone tell me what's wrong with my zSQL string? I am working on it for 2 days and got no where. I want to put a IF statement (=IF('Report - key'!$B$7="ALL","ALL",'2012 data'!B2)) to a ACCESS Text field using SQL Update. Here is my code:

Dim zSQL As String
Dim zmsg As String
Dim DQ As String

DQ = """"

zSQL = "UPDATE Supplier_Meeting_Planning SET [ID3] = ' " & "=IF(" & "'" & "Report - key" & "'" & "!$B$7=" & DQ & "ALL" & DQ & "," & DQ & "ALL" & DQ & "," & "'" & "2012 data" & "'" & "!B2)"
Debug.Print zSQL
DoCmd.RunSQL zSQL

It is very painful to put ' and " in the string. Is there a easy way to do this? Your suggestion is greatly appreciate.

Jean

 

[pbaldy]

For starters, it's IIf(), not IF(). What's the result of the Debug.Print?

 

[jeanvliet2012]

I am new to this and for some reason, I posted my reply last Friday but it is not displayed on the thread.
Here is the the debug.print result:
Run-time error '3075'; Syntax error (missing operator) in query expression"=IF('Report-key'!$B$7="ALL","ALL:,'2012 data'!B2)'.
I can't use IIF in here. The reason is that I want to put the entire IF statment (=IF('Report-key'!$B$7="ALL","ALL:,'2012 data'!B2)) to a ACCESS field and download it to Excel worksheet. In Excel, the IF statement will be converted to a value. Currently, this worksheet has more than 100,000 records. It took about 10-15 mins to do the recalculation.
Thanks for your help.
Jean

 

[pbaldy]

That's not the result of the Debug.Print, it's the error resulting from trying to run the SQL. If you weren't aware:

http://www.baldyweb.com/ImmediateWindow.htm

 

[jeanvliet2012]

Hi pbaldy,
thanks for the instruction.
During the weekend, I did more research in internet. I re-did my SQL string. Here is it:
zSQL = "UPDATE Supplier_Meeting_Planning SET [ID3] = '" & "=IF('Report - key'!$B$7=""ALL"",""ALL"",'2012 data'!B2)'"
Here is the result from Debug.Print zSQL:
UPDATE Supplier_Meeting_Planning SET [ID3] = '=IF('Report - key'!$B$7="ALL","ALL",'2012 data'!B2)'
I am still getting runtime error 3075 - missing operator in query expression.
I have lots of "single quote" in the SQL string. Am I confusing the compiler?

Thanks for your help.
Jean:banghead:

 

[pbaldy]

Yes; the second ' ends the string, leaving everything after out in the cold. This is a shot in the dark, but try:

zSQL = "UPDATE Supplier_Meeting_Planning SET [ID3] = " & Chr(34) & "=IF('Report - key'!$B$7='ALL','ALL','2012 data'!B2)" & Chr(34)

 

[jeanvliet2012]

Hi,
Thank you very much. It works.
Jean

 

[pbaldy]

Ah good, glad it worked. Happy to help.