Results 1 to 4 of 4
  1. #1
    Tatter is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    2

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

    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?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Tatter is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    2
    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.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-28-2013, 12:15 PM
  2. Expression Builder Syntax Problem
    By maxmaggot in forum Queries
    Replies: 7
    Last Post: 06-14-2013, 08:29 AM
  3. Date comparison syntax in query criteria
    By SyntaxSocialist in forum Queries
    Replies: 3
    Last Post: 04-09-2013, 08:39 AM
  4. Syntax error in query expression
    By That_Guy in forum Programming
    Replies: 3
    Last Post: 08-17-2012, 09:01 AM
  5. using FindFirst with a Date Criteria (syntax error)
    By ajetrumpet in forum Programming
    Replies: 2
    Last Post: 09-10-2010, 11:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums