Need correct syntax for SQL statement in VBA
Posted on 2013-10-24
I am having trouble executing the below code in Access VBA. I assume my SQL statement syntax is off. Possibly with Date or Currency?
Dim strInvoiceID As String
Dim curInvoiceAmt As Currency
Dim datInvoiceDate As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strUser As String
strInvoiceID = Form_frmDashboard.txtLstBoxInvID.Value
curInvoiceAmt = Form_frmDashboard.txtInvRcvdAmt.Value
datInvoiceDate = Format(Form_frmDashboard.txtInvRcvdDate, "yyyy\/mm\/d")
Set db = CurrentDb()
strSQL = "Select * from [tblReceivableManagament]"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
'clear existing log in that may not have cleared out
strUser = "'" & fncUserID & "'"
strInvoiceID = "'" & strInvoiceID & "'"
strSQL = "INSERT INTO tblReceivableManagement (Invoice#, InvoicePaidDate, InvoicePaidAmount, UserID)VALUES(" & strInvoiceID & ", #" & datInvoiceDate & "#,curInvoiceAmt, " & strUser & ")"