MS Access sql help please!

[Joshei]

Assuming the actual variables and fields are correct could people check this syntax for me? I am confused with the syntax, really, at the beginning of each line and the ends especially. And am I right in assuming that the local txt variables are referenced with the two amperstands? This is for MS Access.

Thanks in advance, I'm pretty sure there are problems! (each line has been separated by a blank line)

DoCmd.RunSQL ("INSERT INTO tblBalance (StartDate,CashSales,CCSales,WireSales,CheckSales, BillToSales,CashBalance_Calc,CCBalance_Calc, " _

& " WireBalance_Calc , CheckBalance_Calc, BillToBalance_Calc, CashBalance_Beg, CheckBalance_Beg, CCBalance_Beg, " _

& " WireBalance_Beg , BillToBalance_Beg, CashRefund, CCRefund, CashBalance_Count, " _

& " CCBalance_Count , WireBalance_Count, CheckBalance_Count, BillToBalance_Count, CashOut, CCOut, WireOut, CheckOut, BillToOut, CashBalance_End, " _

& " CashCheck_End , CCBalance_End, WireBalance_End, BillToBalance_End, OrderCount, PTAcheck, PTAcash, PTACC ) " _

& " VALUES (#" & txtDate & "#," & txtCashSales & "," & txtCCSales & "," & txtWireSales & "," & txtCheckSales & "," & txtBillToSales & "," & txtCashBalance_Calc & "," _

& " & txtCCBalance_Calc & "," & txtWireBalance_Calc & "," & txtCheckBalance_Calc & "," & txtBillToBalance_Calc & "," & txtCashBalance_Beg & "," _

& " & txtCheckBalance_Beg & "," & txtCCBalance_Beg & "," & txtWireBalance_Beg & "," & txtBillToBalance_Beg & "," & txtCashRefund & "," & txtCCRefund& "," _

& " & txtCashBalance_Count & "," & txtCCBalance_Count & "," _

& " & txtWireBalance_Count & "," & txtCheckBalance_Count & "," & txtBillToBalance_Count & "," & txtCashout & "," _

& " & txtCCOut & "," & txtWireOut & "," & txtCheckOut & "," & txtBillToOut & "," & txtCashBalance_End & "," & txtCashCheck_End & "," _

& " & txtCCBalance_End & "," & txtWireBalance_End & "," & txtBillToBalance_End & "," & txtOrderCnt & "," & txtPTACheck & "," & txtPTACash & "," _

& " & txtPTACC & ")")


Josheir

 

[SkipVought]

Hi,

"am I right in assuming that the local txt variables are referenced with the two amperstands?"

Ampersand (&) is a VB concatenation symbol and has nothing to do with referencing anything.

Assign your SQL string to a variable.

Use a Debug.Print to print the value in the variable in the Immediate Window.

COPY that string and PASTE it into the GUI SQL editor to test whether it manually does what you expect it to do. This is standard programmer debugging procedure.

 

[Joshei]

Thanks Skip, I really enjoyed the Debug.Print. The GUI SQL Editor that you mentioned is this the editor in Access?

Thank you,
Joshei

 

[SkipVought]

Yes, the Access Graphical User Interface where you can edit your SQL.

Did you try PASTING the Debog.Print string into the SQL editor and running that code?

So what happens???