Insert into query that just wont work.

[todmac]

Hi there,


I have tried this many different ways, and I am still getting an error that there are too few parameters, expects 2.
Any help is greatly appreciated.

 Private Sub Submit_Click()
'On Error GoTo Err_Handler
Dim StrSql As String
Dim status As String
Status2 = "In Progress"
Dim Currentuser2 As String
Currentuser2 = "John Doe"
Dim Now2 As Date
Now2 = Now()
StrSql = "INSERT into CLAIMS (Claim_Number, Vendor, Bus_Type, Location, Odometer, Date_Claim_Started, Date_of_Failure, Description, Unit_Number, Date_Submitted, Amount_Claimed_Before_Taxes, Amount_Claimed_After_Taxes, Amount_Paid, Reason_for_Short_Pay, Claim_Status, Claimed_By, Parts_to_be_returned, Date_of_Parts_Shipping) values (" & Me.Claim_Number & ", " & Me.Vendor & ", " & Me.Bus_Type & ", " & Me.Location & ", " & Me.Odometer & ", #" & Now2 & "#, # " & Me.Date_of_Failure & " #, " & Me.Description & ", " & Me.Unit_Number & ", # " & Me.Date_Submitted & " #, " & Me.Amount_Claimed_Before_Taxes & ", " & Me.Amount_Claimed_After_Taxes & ", " & Me.Amount_Paid & ", " & Me.Reason_for_Short_Pay & ", '" & Status2 & "', '" & Currentuser2 & "', " & Me.Parts_to_be_returned & ", # " & Me.Date_of_Parts_Shipping & " #)"
CurrentDb.Execute (StrSql)
End Sub

[Dal Jeanis]

Usually, if the rest is coded correctly, that too few parameters message indicates that something in your expression is equating to NULL.

I would check the syntax on the dates, though. I don't think there is supposed to be a space between the # and the date.

'#11/02/2014#' not '# 11/02/2014 #'

[orange]

status2 is not defined don't know if that's relevant

You could try a Debug.Print strsql and comment the
CurrentDb.Execute (StrSql)

and see if there is anything obvious in the print.

I agree with Dal regarding the space before the Date.

[todmac]

Thank you very much to you both.
It wasn't actually the date fields, I did more experimenting with your suggestions and realized that two of the fields I was inserting into were field type: memo and that they would likely have to be defined as text. I turned "& me.Description &" into '" & me.description &"' and the same with the other one and now it works fine.
Thanks again.