VBA SQL syntax

[cyd44]

I am having problems with an SQL statement in VBA.

My code is

I am using a Form and the coding is as follows:-

BRef = Me.ID
BAmount = Me.Total_Cost
BPaid = False
BDue = Me.Payment_Date
StrSQL = "INSERT INTO tblInvoice (Book_Ref, Amount, Paid, PaymentDate) VALUES  ( " & BRef & " , " & BAmount & ", " & BPaid & ", " & BDue & " )"
DoCmd.RunSQL (StrSQL)

The above code does work (in a fashion) but creates a new invoice putting in the ID and a date of 31/12/1899 only.

I realise that we need a specific syntax for different variables and the variables above are
Bref = Integer
BAmount - Currency
Paid - Boolean
BDue = Date

Could anyone please help

 

[epon78]

In Access, text must go inside quotes (double quotes or single quotes), dates inside hashes, and otherwise I guess you are fine. Though in VBA string literal text must also go inside double quotes - so that can be confusing at first.

StrSQL = "INSERT INTO tblInvoice " & _
    "(Book_Ref, Amount, Paid, PaymentDate) VALUES (" & _
    BRef & ", " & Amount & ", " & BPaid & ", #" & BDue & "#" & ")"

 

[cyd44]

Many thanks for the syntax, this has worked a treat