Error in SQL update code in VBA

[Nick Cooper]

When I enter a date in a field named 'Payment Date' in a form derived from a table named 'Bookings', I want that same date to be added to all other records that share the same Invoice Number 'Inv No'.

I have tried the following code but it seems to glitch at the end of the 'SET' line.

Private Sub Payment_Date_BeforeUpdate(Cancel As Integer)
Dim strSQL As String
Dim SelectedDate As Date
Dim SelectedInvoice As Long

SelectedDate = [Payment Date]
SelectedInvoice = [Inv No]
strSQL = "UPDATE [Bookings] " & _
         "SET    [Payment Date] = " & SelectedDate & _
         "WHERE ([Inv No] = SelectedInvoice)"
Call CurrentDb.Execute(strSQL)
End Sub

Where have I gone wrong pse, and does anyone know a good link for learning VBA/SQL syntax?!

[nico5038]

A date needs to be surrounded by #'s signs so use:

 strSQL = "UPDATE [Bookings] " & _
          "SET    [Payment Date] = #" & SelectedDate & _
          "# WHERE ([Inv No] = SelectedInvoice)"

Nic;o)

[nico5038]

Oops, the SelectedInvoice should be concatenated, so use:

 strSQL = "UPDATE [Bookings] " & _
          "SET    [Payment Date] = #" & SelectedDate & _
          "# WHERE ([Inv No] = " & SelectedInvoice)

Nic;o)

[colintis]

Just a bit of supplement based on nico5038's answer. To make sure the date is updated correctly into the table (e.g. The date 05/09/2010 5th Sept, 2010, may confused the database known as 09/05/2010 9th May, 2010), you need to clarify the format to suit the database's standard. Update the line for getting the selecteddate value

SelectedDate = format([Payment Date], "mm/dd/yyyy")

[Nick Cooper]

Apologies for delay in getting back.
I have tried the recommended code, ie:

Private Sub Payment_Date_BeforeUpdate(Cancel As Integer)
Dim strSQL As String
Dim SelectedDate As Date
Dim SelectedInvoice As Long
SelectedDate = Format([Payment Date], "dd-mmm-yy")
SelectedInvoice = [Inv No]
strSQL = "UPDATE [Bookings] " & _
         "SET    [Payment Date] = #" & SelectedDate & _
         "# WHERE ([Inv No] = " &  SelectedInvoice)
Call CurrentDb.Execute(strSQL)
End Sub

and I get 'Compile error: Expected: End of Statement' with the cursor resting on the final bracket of the 'Where' line.
Any further suggestions please?

[nico5038]

Hmm, guess it's a typo, change SelectedInvoice ) into SelectedInvoice

Nic;o)

[Nick Cooper]

That did it, although I had to remove the leading bracket too. Thanks for help. Can you recommend a good place to find an explanation of the use of punctuation in SQL/VBA, concatenation etc please?

Nick

[nico5038]

Normally I use the Access Help (when VBA is active) to check for the syntax of functions and statements.

Success with your application !

Nic;o)

[NeoPa]

A couple of points :

1. Colintis point of formatting the literal date is valid, only it doesn't depend on the database. It always requires the SQL standard format which is m/d/yyyy (See Literal DateTimes and Their Delimiters (#)).
2. The end of the line, to match the opening parenthesis of the original version, should end : SelectedInvoice & ")"
   
   Losing the opening parenthesis is a workable alternative of course.

[NeoPa]

Nico:
Normally I use the Access Help (when VBA is active) to check for the syntax of functions and statements.

Access Help also provides assistance for the Jet-SQL side of things. See Finding Jet SQL Help.