Results 1 to 5 of 5
  1. #1
    OverPlayed is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    6

    Cool Date Driven If statement in VBA

    Hello,

    I have a set of query's that I need to run each day off a button to update table. In VBA I am trying to ensure that the query's do not run multiple time on one day. I want to use an if statement that would compare the current date against a date field in a table to ensure that they do not mach before executing all the query's. The VBA below does not seem to be recognizing the SQL statement and is causing it to always think the process has been previously executed on that day.

    Private Sub ExecuteQuerys_Click()


    DoCmd.SetWarnings False


    Dim db As Database
    Dim t As DAO.Recordset
    Dim SQL As String
    Dim msg As String, Ans As Variant
    Set db = CurrentDb()

    SQL = "Select max(tbl_RSS.Today)from tbl_RSS"
    If Date > SQL

    Then


    Ans = MsgBox("Are you sure you want to update report tables?", vbYesNo)

    Select Case Ans
    Case vbYes
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"
    DoCmd.OpenQuery "App"


    MsgBox "Tables have been updated and are ready for validation", vbOKOnly, "Report Release"
    Case vbNo


    End Select
    Else
    MsgBox "Today's reports have already been updated, to validate data select report from below", vbCritical
    End If
    End Sub

    Any help would be greatly appreciated!!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That SQL is just a string variable holding the statement, not data.

    Must use the string to open a recordset object then read value from a field of the recordset.

    Or just use DMax() instead of recordset code.

    If Date() > Nz(DMax("[Today]", "tbl_RSS"),0) Then

    Please use CODE tags when posting lengthy code snips to retain indentation and readability.

    And why are you running the same action query 10 times?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried to run this code?
    What is the purpose of recordset "t"? You don't seem to use it?
    How and when does
    Code:
    tbl_RSS.Today
    get valued?

    You actually run App query 10 times?

    OOooops: I see June has posted.

  4. #4
    OverPlayed is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    6
    Quote Originally Posted by June7 View Post
    That SQL is just a string variable holding the statement, not data.

    Must use the string to open a recordset object then read value from a field of the recordset.

    Or just use DMax() instead of recordset code.

    If Date() > Nz(DMax("[Today]", "tbl_RSS"),0) Then

    Please use CODE tags when posting lengthy code snips to retain indentation and readability.

    And why are you running the same action query 10 times?
    Im not running the same query 10 times... its an example. Thanks

  5. #5
    OverPlayed is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    6
    I just went with the DMax() thanks for your help! It worked perfectly.

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

Similar Threads

  1. Table design for a dates driven workflow in Access
    By mbenton in forum Database Design
    Replies: 6
    Last Post: 05-03-2016, 08:39 AM
  2. Report driven by form combo box
    By Oscar.Ingalls in forum Reports
    Replies: 1
    Last Post: 01-01-2013, 01:28 AM
  3. Database driven website....new ground for me
    By avarusbrightfyre in forum SQL Server
    Replies: 1
    Last Post: 10-09-2011, 08:19 PM
  4. IIF statement using a date
    By elegant in forum Queries
    Replies: 1
    Last Post: 05-26-2011, 11:00 AM
  5. Problem with parameter driven combo box
    By clydet2 in forum Queries
    Replies: 0
    Last Post: 04-06-2009, 12:19 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