Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need correct syntax for SQL statement in VBA

Posted on 2013-10-24
3
Medium Priority
?
658 Views
Last Modified: 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
           
        Else
    End If

strUser = "'" & fncUserID & "'"
strInvoiceID = "'" & strInvoiceID & "'"




            strSQL = "INSERT INTO tblReceivableManagement (Invoice#, InvoicePaidDate, InvoicePaidAmount, UserID)VALUES(" & strInvoiceID & ", #" & datInvoiceDate & "#,curInvoiceAmt, " & strUser & ")"
           

            db.Execute (strSQL)
0
Comment
Question by:marku24
3 Comments
 
LVL 18

Assisted Solution

by:lludden
lludden earned 200 total points
ID: 39598853
Put brackets around field names with special characters:

strSQL = "INSERT INTO tblReceivableManagement ([Invoice#], InvoicePaidDate, InvoicePaidAmount, UserID)VALUES(" & strInvoiceID & ", #" & datInvoiceDate & "#,curInvoiceAmt, " & strUser & ")"
            

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 800 total points
ID: 39598882
In addition to the brackets, you need to separate all of the variables from the sql string, and treat the commas between values as part of the string like this:


            strSQL = "INSERT INTO tblReceivableManagement ([Invoice#], InvoicePaidDate, InvoicePaidAmount, UserID) VALUES(" & strInvoiceID & ", #" & datInvoiceDate & "#," & curInvoiceAmt & ", " & strUser & ")"

Open in new window


This assumes Invoice# and userID are numeric.


if InvoiceNumber and UserID are TEXT, you'll need quotes for delimiters around those values like this:

            strSQL = "INSERT INTO tblReceivableManagement ([Invoice#], InvoicePaidDate, InvoicePaidAmount, UserID) VALUES('" & strInvoiceID & "', #" & datInvoiceDate & "#," & curInvoiceAmt & ", '" & strUser & "')"

Open in new window

0
 

Author Closing Comment

by:marku24
ID: 39598967
nice job, thank you.  That special character screwed me up.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question