please help on syntax '3075' error (missing operator) in query expression ...double quote

[tnguyen315]

My code:
Dim DQ As String

DQ = """"

strSQL = "INSERT INTO " _
& "Audit (EditDate, InsertedBy, RecordID, RecordDate, SourceTable, " _
& " SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) " _
& "VALUES (Now()," _
& DQ & glbGetUserName() & DQ & ", " _
& DQ & RecordID.Value & DQ & ", " _
& DQ & RecordDate.Value & DQ & ", " _
& DQ & frm.RecordSource & DQ & ", " _
& DQ & .Name & DQ & ", " _
& DQ & varBefore & DQ & ", " _
& DQ & varAfter & DQ & ", " _
& DQ & Environ("ComputerName") & DQ & ", " _
& DQ & Environ("UserDomain") & DQ & ", " _
& DQ & Environ("UserProfile") & DQ & ")"

Debug.Print strSQL

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

 

[PHV]

Debug.Print strSQL
What is displayed in the debug window ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tnguyen315]

Thanks PHV for quick reply.
run-time error '3075'

INSERT INTO Audit (EditDate, InsertedBy, RecordID, RecordDate, SourceTable, SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) VALUES (Now(),"tnguy", "45744", "6/7/2012", "inspect", "inspmemo", "testing on what you will see is 36", "testing on what you will see is 36"", "NGU990", "HCES", "C:\Users\tnguy")

 

[PHV]

The error is here:
", "testing on what you will see is 36[!]"[/!]",

I'd replace this:
[tt]DQ = """"[/tt]
with this:
[tt]DQ = "'"[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tnguyen315]

Thanks PHV...
Got it worked...
I tried the code:


Dim DQ As String

DQ = """"

strSQL = "INSERT INTO " _
& "Audit (EditDate, InsertedBy, RecordID, RecordDate, SourceTable, " _
& " SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) " _
& "VALUES (Now()," _
& DQ & glbGetUserName() & DQ & ", " _
& DQ & RecordID.Value & DQ & ", " _
& DQ & RecordDate.Value & DQ & ", " _
& DQ & frm.RecordSource & DQ & ", " _
& DQ & .Name & DQ & ", " _
& "'" & Replace(varBefore, "'", "''", , , vbTextCompare) & "'" & ", " _
& "'" & Replace(varAfter, "'", "''", , , vbTextCompare) & "'" & ", " _
& DQ & Environ("ComputerName") & DQ & ", " _
& DQ & Environ("UserDomain") & DQ & ", " _
& DQ & Environ("UserProfile") & DQ & ")"

Debug.Print strSQL

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Again, thank you very much PHV. I'm so appreciated.
TN

 

[tnguyen315]

Please help again,
This code is worked on the test database, but when i relink the table and change to the production database i still got the error 3075 (missing operator)
Table on both databases are the same

I changed as PHV suggested

I'd replace this:
DQ = """"
with this:
DQ = "'"


Test dba
debug.print sql

INSERT INTO Audit (EditDate, InsertedBy, RecordID, SourceTable, SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) VALUES (Now(),'tnguy', '45736', '_CorrectiveActions', 'comCorrectiveAction', 'Mow and maintain grass below 3’ft tall throughout the property, remove and properly discard tires', 'Mow and maintain grass below 3’ft tall throughout the property, remove and properly discard', 'NGU990', 'HCES', 'C:\Users\tnguy')


production dba
debug.print sql

INSERT INTO Audit (EditDate, InsertedBy, RecordID, SourceTable, SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) VALUES (Now(),'tnguy', '45736', '_CorrectiveActions', 'comCorrectiveAction', 'Mow and maintain grass below 3’'ft tall throughout the property, remove and properly discard tires', 'Mow and maintain grass below 3’ft tall throughout the property, remove and properly discard', 'NGU990', 'HCES', 'C:\Users\tnguy')
ft tall throughout the property, remove and properly discard tires', 'NGUY990', 'HCES', 'C:\Users\tnguy')

I noticed the production database replaced
3'ft to 3''ft


Please help on this error.
is there any permission or restriction on sql?
Thanks

 

[Andrzejek]

INSERT INTO Audit (EditDate, InsertedBy, RecordID, SourceTable, SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) VALUES (Now(),'tnguy', '45736', '_CorrectiveActions', 'comCorrectiveAction', 'Mow and maintain grass below 3’'ft tall throughout the property, remove and properly discard tires', 'Mow and maintain grass below 3[red]’[/red][blue]ft tall throughout the property, remove and properly discard', 'NGU990', 'HCES', 'C:\Users\tnguy')
ft tall throughout the property, remove and properly discard tires', 'NGUY990', 'HCES', 'C:\Users\tnguy')[/blue]

This line of your code:[tt]
& "'" & Replace(varAfter, "'", "''", , , vbTextCompare) & "'" & ", " _ [/tt]
"didn't take", the Replace part is missing, I would guess.
You do not replace single quote with 2 single quotes.

Have fun.

---- Andy