Syntax Error - Spare pair of eyes

[richied123]

Hi there,

I've gone blind looking for the syntax error in the code below. Can anyone help me spot it?

Sorry if it looks messy - I haven't posted code on the site before.

Thanks,
Richard

'This is the code to ensure UK dates run through the fnDate function
myDateEntered = fnDate(Me.txtDateEntered)
myRequestReceived = fnDate(Me.txtRequestReceived)
myNextActionDate = fnDate(Me.txtNextActionDate)

'Add the form details into the appropriate table
rdSQL = "INSERT INTO tblAccounts (RefNum, DateEntered, EnteredBy, CourtName, AccountType, " & _
"ContactName, RequestReceived,  RequestAuthorised, RequestRejected, ReceiptConfirmed, ReceiptChaser1, " & _
"ReceiptChaser2, CompletionConfirmed, CompletionChaser1, CompletionChaser2, Status, NextAction, NextActionDate, FullyComplete) VALUES" & _
"('" & myRefNum & "', " & myDateEntered & ", '" & Nz(Me.txtEnteredBy, "") & "', '" & Me.txtCourtName & "', '" & Me.cboAccountType & "'," & _
" '" & Me.txtContactName & "', " & myRequestReceived & "," & _
" '" & Nz(Me.cboAuth, "") & "', '" & Nz(Me.cboRejected, "") & "', '" & Nz(Me.cboReceipt, "") & "', '" & Nz(Me.txtReceiptChaser1, "") & "', '" & Nz(Me.txtReceiptChaser2, "") & "'," & _
" '" & Nz(Me.cboComplete, "") & "', '" & Nz(Me.cboCompleteChaser1, "") & "', '" & Nz(Me.cboCompleteChaser2, "") & "', " & OPGStatus & "," & _
" '" & Nz(Me.txtNextAction, "") & "', " & myNextActionDate & ", '" & Nz(Me.cboFullyComplete, "") & "')"

 

[ted.martin]

Not sure but have you got a semi-colon ; at the end of the last line of the SQL inside the final "

 

[richied123]

Tried it but didn't make a difference.

 

[ted.martin]

The there must be something else as your SQL MUST end in ;". I will have another look.

 

[ted.martin]

maybe try the syntax # " & myNextActionDate & " # and myDateEntered OR

' " & myNextActionDate & " '

 

[richied123]

Tried that aswell - wouldn't that produce a type mismatch error anyway?

 

[ted.martin]

Well the only other suggestion is to break the SQL down into a smaller number of string/variable expressions and test each element; adding the next part as you succesfully go along. Once you have found the answer, then you can always go back to the extended string version.

 

[richied123]

Yeh, thanks for your time - will do that.

 

[WayneRyan]

Richard,

Use the Immediate Window and print the value of rdSQL and paste it here:

?rdSQL

Wayne

 

[pbaldy]

As Wayne said, posting the ending SQL will help. However, right off I note you don't have "#" around your date values, as Access requires.

 

[modest]

Next time please better format your query the post too.

i.e.

'This is the code to ensure UK dates run through the fnDate function
myDateEntered = fnDate(Me.txtDateEntered)
myRequestReceived = fnDate(Me.txtRequestReceived)
myNextActionDate = fnDate(Me.txtNextActionDate)

'Add the form details into the appropriate table
rdSQL = "INSERT INTO tblAccounts (" & _
            "RefNum" & _
            ", DateEntered" & _
            ", EnteredBy" & _
            ", CourtName" & _
            ", AccountType" & _
            ", ContactName" & _
            ", RequestReceived" & _
            ", RequestAuthorised" & _
            ", RequestRejected" & _
            ", ReceiptConfirmed" & _
            ", ReceiptChaser1" & _
            ", ReceiptChaser2" & _
            ", CompletionConfirmed" & _
            ", CompletionChaser1" & _
            ", CompletionChaser2" & _
            ", Status" & _
            ", NextAction" & _
            ", NextActionDate" & _
            ", FullyComplete) "
rdSQL = rdSQL & _
        "VALUES " & _
            "('" & myRefNum & "'" & _
            ", " & myDateEntered & _
            ",'" & Nz(Me.txtEnteredBy, "") & "'" & _
            ",'" & Me.txtCourtName & "'" & _
            ",'" & Me.cboAccountType & "'" & _
            ",'" & Me.txtContactName & "'" & _
            ", " & myRequestReceived & _
            ",'" & Nz(Me.cboAuth, "") & "'" & _
            ",'" & Nz(Me.cboRejected, "") & "'" & _
            ",'" & Nz(Me.cboReceipt, "") & "'" & _
            ",'" & Nz(Me.txtReceiptChaser1, "") & "'" & _
            ",'" & Nz(Me.txtReceiptChaser2, "") & "'" & _
            ",'" & Nz(Me.cboComplete, "") & "'" & _
            ",'" & Nz(Me.cboCompleteChaser1, "") & "'" & _
            ",'" & Nz(Me.cboCompleteChaser2, "") & "'" & _
            ", " & OPGStatus & _
            ",'" & Nz(Me.txtNextAction, "") & "'" & _
            ", " & myNextActionDate & _
            ",'" & Nz(Me.cboFullyComplete, "") & "')"

All the syntax looks almost correct, try putting a space after 'Values'. If that doesn't work, what is the exact error message? (take a screen shot of it). And make sure the datatype fields are correct.

 

[WayneRyan]

Nice job Modest,

            ", " & OPGStatus & _  <-- NUMBER, but has a trailing single-quote
            ",'" & Nz(Me.txtNextAction, "") & "'" & _

Wayne

Sorry ... my bad

 

[richied123]

I think that happened when he altered the display of the code. I'm pretty sure that the problem is the dates - The reason there are no # signs around the dates was that it was included in the fnDate function ie. "#" & .... and then entered as string, but I've changed this anyway now.

I've broken up the code as suggested and this seems the best solution - if I still can't find the problem I'll be back. Thanks everyone.