Help with access sql statement please.

[JoshuaEir]

I've been working on the following sql statement. All the variables are currency besides txtDate (a date) and OrderCount which is an integer.

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)")



When I try to continue with the & variable & on the next lines I get errors. Could someone help me with its completion?

Thank you, I am new to this.

Josheir

[JeffChr]

You should debug your sql in the query builder and then when it works transcribe it to VBA, or don't even bother using VBA; just use the query. It would make your life a lot easier. We can't debug your SQL, we don't have your tables.

[JeffChr]

Sorry - I do see a syntax error. End your lines with the underscore character and start the next line with the ampersand.

[JoshuaEir]

The actual variables should be okay (and this I can fix easily.) I was just hoping someone could help me with the syntax of the overall lines. Like the line after Values, the txtCheckBalance_Beg line and last line of the query.

Like you did, but completely. I don't think I can use the query builder, I'm using local variables in the module that's the whole idea. Thank you for the syntax error help though.

Josheir

[JoshuaEir]

So far...

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)")

Thank you,
JoshEir

[JoshuaEir]

For example is the Values line okay at the end ... "," _

I was hoping for some great Guru!

So far so good,

JoshEir

[JeffChr]

place the ampersand outside the quotes like this:

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, " _
etc

[JoshuaEir]

Thank you JeffChr!

So now it's

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

[JeffChr]

so how does it work? do you get an error?

[JoshuaEir]

I'll let you know, I won't be trying it until Monday at work. I hope it's error free!

If anyone else sees any problems please let me know.

Thanks again Jeff.

JoshEir

[JoshuaEir]

I don't mean to show to much code. This is the code as I understand it now. I am still confused with the syntax, really, at the beginning of each line and the ends especially. I decided to put all the code together and list it here because it is easier to understand this way.

Thanks in advance, I'm pretty sure there are problems!

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 & ")")


I hope I am not assuming top much that this is how to reference these local txt variables.


Josheir

[JoshuaEir]

Jeffchr, I got the query working I'll post it later this week for anyone who is interested!

JoshueEir