Syntax error (missing operator) with the dates in the INSERT INTO

[BrianLe]

I get a syntax error (missing operator) with the dates in the INSERT INTO statement in the cmdAddRecord_Click code.

The format of the fields in tblChemicalOrders are as follows:

OrderDate - date/time (only date part used)
ExpectedReceiveDate - date/time (only date part used)
OrderQuantity - number
Notes - text
ChemicalID - number
VendorID - number
OrdererID - number
ChemicalUnitsID - number

I believe I have incorrect syntax in this part of the INSERT INTO statement.

(#" & tbx1.Value & DQ & "," _
& "#" & tbx2.Value & DQ & "," _

Here is the code I am using.

Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
'DoCmd.SetWarnings False


Dim tbx1 As TextBox, tbx2 As TextBox, tbx3 As TextBox, tbx4 As TextBox, lbx1 As ListBox, lbx2 As ListBox, lbx3 As ListBox, lbx4 As ListBox, SQL As String, DQ As String, itm As Variant


Set lbx1 = Me!lbxChemicalID
Set lbx2 = Me!lbxVendorID
Set lbx3 = Me!lbxChemicalUnitsID
Set lbx4 = Me!lbxOrdererID
Set tbx1 = Me!tbxOrderDate
Set tbx2 = Me!tbxExpectedDeliveryDate
Set tbx3 = Me!tbxOrderQuantity
Set tbx4 = Me!tbxNotes
DQ = """"

' ListBox must have the Focus
Me.lbxChemicalID.SetFocus

If lbx1.ListIndex = (-1) Then
MsgBox "No Chemical Selected!"
ElseIf lbx2.ListIndex = (-1) Then
MsgBox "No Vendor Selected!"
Else
For Each itm In lbx2.ItemsSelected


SQL = "INSERT INTO tblChemicalOrders (OrderDate, ExpectedDeliveryDate, OrderQuantity, Notes, ChemicalID, VendorID, ChemicalUnitsID, OrdererID) " & _
"VALUES(#" & tbx1.Value & DQ & "," _
& "#" & tbx2.Value & DQ & "," _
& DQ & tbx3.Value & DQ & "," _
& DQ & tbx4.Value & DQ & "," _
& DQ & lbx1.Column(0) & DQ & "," _
& DQ & lbx2.Column(0) & DQ & "," _
& DQ & lbx3.Column(0) & DQ & "," _
& DQ & lbx4.Column(0) & DQ & ""

Debug.Print SQL
DoCmd.RunSQL SQL

Next
End If

Set lbx1 = Nothing
Set lbx2 = Nothing
Set lbx3 = Nothing
Set lbx4 = Nothing
Set tbx1 = Nothing
Set tbx2 = Nothing
Set tbx3 = Nothing
Set tbx4 = Nothing

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub

Thanks,

Brian

 

[dhookom]

I believe all the DQs aren't necessary except maybe for text fields. I'm not sure why you didn't share the results of the

       Debug.Print SQL

This is the key to your trouble-shooting.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Also, your Dates in Access have to be in the Format:

[tt]#05/05/2011#[/tt]

And don't forget about ( ):
[tt]
INSERT INTO table [blue]([/blue]..., ..., ...[blue])[/blue]
VALUES [blue]([/blue]..., ..., ...[blue])[/blue][/tt]

Have fun.

---- Andy

 

[PHV]

SQL = "INSERT INTO tblChemicalOrders (OrderDate,ExpectedDeliveryDate,OrderQuantity,Notes,ChemicalID,VendorID,ChemicalUnitsID,OrdererID) " & _
"VALUES(#" & tbx1.Value & "#," _
& "#" & tbx2.Value & "#," _
& tbx3.Value & "," _
& DQ & tbx4.Value & DQ & "," _
& lbx1.Column(0) & "," _
& lbx2.Column(0) "," _
& lbx3.Column(0) & "," _
& lbx4.Column(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

PHV, the last ) is missing in your statement, and since tbx4.Value is Notes (I assume user can type anything in it), I would replace single quote with 2 single quotes:

SQL = "INSERT INTO tblChemicalOrders (OrderDate,ExpectedDeliveryDate,OrderQuantity,Notes,ChemicalID,VendorID,ChemicalUnitsID,OrdererID) " & _
"VALUES(#" & tbx1.Value & "#," _
& "#" & tbx2.Value & "#," _
& tbx3.Value & "," _
& [tt]"'"[/tt] & Replace(tbx4.Value, [tt]"'", "''") & "',"[/tt] _
& lbx1.Column(0) & "," _
& lbx2.Column(0) "," _
& lbx3.Column(0) & "," _
& lbx4.Column(0) & "[tt])[/tt]"

Have fun.

---- Andy