Syntax of a SQL Expression which includes a date in the Criteria.

[Tatter]

I am trying to select a single record from a table using a SQL Expression in my VBA Code so that I can move the record to a future date.



My code is as follows:-

Dim NewDb As DAO.Database
Dim Newtbl2 As DAO.Recordset
Dim NewSQL As String

NewSQL = "SELECT * From Pending where [AmountPending] = " & CapAmount & " AND " & "[AccountFrom] = " & CapFrom & " AND " & "[AccountTo] = " & CapTo & " AND " & "[DatePending] = #" & DateValue(CapDate) & "#"

Set NewDb = CurrentDb
Set Newtbl2 = NewDb.OpenRecordset(NewSQL, dbOpenDynaset)

CapDate = DateAdd("ww", 52, CapDate)
Newtbl2.Edit
Newtbl2![Remaining] = CapRem - 1
Newtbl2![Datepending] = CapDate
Newtbl2![MID] = Month(CapDate)
Newtbl2![YID] = Year(CapDate)
If CapFrom = 70 And CapTo = 80 Then
Newtbl2![AmountPending] = 100
ElseIf CapFrom = 80 And (CapTo = 24 Or CapTo = 62) Then
Newtbl2![AmountPending] = 50
End If
Newtbl2.Update
Newtbl2.Close


However, when the code gets to "Newtbl2.Edit", I get an error message come up saying "No Current Record".

Where am I going wrong with the syntax?

[pbaldy]

This will help you check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

You may also have a problem if you use a non-US date format.

[Tatter]

Thank you for pointing me to the document about the debug.print method.

The immediate window showed the variables were being entered correctly.

Also the remainder of the code worked without the error message, and the record was updated.

[pbaldy]

Happy to help.