Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    lookup record and compare (2003 sp2)

    I have been trying to build a simple function that looks at the MSysObjects table for a specific Id and compares the DateUpdate field to the current date. If the DateUpdate date is not equal to today the function returns true. Pretty simple. I thought I could do this with a SQL statement and this is as far as I get:

    Public Function CheckTableUpdate(lngID As Long)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT IIf(Format([DateUpdate],""mmddyyy"")<Format(Date()," _
    & """mmddyyy""),[DateUpdate],"") AS DtUpdate" _
    & "FROM MSysObjects;"
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    At this line I get an error Object variable of With Block variable not set.

    The rest would be an if statement to check the DtUpdate field for null.

    Any help would be appreciated.
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup record and compare (2003 sp2)

    1) You never set the variable db. Add the line

    Set db = CurrentDb

    2) Although you took care to double the quotes around the formats, you forgot to do that for the empty string. Try
    <code>
    strSQL = "SELECT IIf(DateUpdate < Date(), DateUpdate, '') AS DtUpdate FROM MSysObjects"
    </code>
    3) Since the code is incomplete, I don't know what the role of lngID is, and how it will work.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: lookup record and compare (2003 sp2)

    You have left out the line

    Set db =CurrentDB
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lookup record and compare (2003 sp2)

    I always miss the little things. Thanks guys.

    I formated Date() and DateUpdate because the time is included in DateUpdate and is therefore always less than Date(). This was the only way I could get it to work.

    lngID is the Id from MSysObjects that I would pass as an argument when I call the function.

    But I still need more help. I thought I could use an IF statement to check for the Null.

    If IsNull(rst.DtUpdate) Then
    CheckTableUpdate = True
    Else
    MsgBox "Records are current."
    End If
    End function

    Now I get an error at .DtUpdate Method or data menber not found. I figured I was in trouble when DtUpdate did not appear in the list. How do I refer to it?
    Thanks
    chuck

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup record and compare (2003 sp2)

    I don't understand what you're doing. The DateUpdate field in the MSysObjects table contains the date the design of the table (or other database object) was last updated, it has nothing to do with the date of the records in the table.

    And since you never assign Null to DtUpdate, how can you expect it ever to be Null?

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lookup record and compare (2003 sp2)

    The SQL produces Null when DateUpdate equals Date(). If I copy the SQL into a query and add the Id field and ID it produces one record with one field. If the result is Null in the DtUpdate field my function will be true and I will know the procedure has run when I call the function.

    My procedure to update the table completely empties the table and repopulates it with current data from the update file. Perhaps this constitutes the design change you speak of, because the DateUpdate field changes each time the procedure runs. Am I making any sense yet?
    Thanks
    chuck

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup record and compare (2003 sp2)

    The SQL as posted by you produces an empty string "" if DateUpdate is the current date. That is *not* the same as Null!

    Do you use a delete query statement followed by an append query, or do you recreate the table by using a make-table query?

  8. #8
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lookup record and compare (2003 sp2)

    Ah ha! You got me on that one. I forgot about the empty string not being the same as null. I was hoping not to uncover all my ignorance but now that I have I can only get smarter...with your help of course.

    When I first wrote the IIF statement I thought of assinging "Null" but didn't think that would work in a date field. What would you do?

    I do use a delete query and then an append query to repopulate the table.
    Thanks
    chuck

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup record and compare (2003 sp2)

    There is no objection against using Null. (In fact, a date field can be Null but it cannot be an empty string, but you're not populating a field in a table here, you're returning a value in a recordset, so anything goes)

    I don't understand why the DateUpdate value of the table would change when you run a delete query and an append query against the table - it doesn't work that way for me. But if it consistently works for you, that's fine.

  10. #10
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lookup record and compare (2003 sp2)

    Perhaps the change to the modification date is an Access 2003 thing. Aren't you using 2002? Anyway, the modification date is consistently changed each time I empty and repopulate. But I still need to point to the DtUpdate field to test it. How can I do that? Or is there just a better approach to the matter.

    My updating is an automated process that affects multiple databases and multiple tables in each database. I also have an automated backup procedure that compacts and copies each database each day. I wanted to incorporate the test of the modification dates within that process and then produce a report listing the results for each table. Right now I just send myself an email if something fails.
    Thanks
    chuck

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup record and compare (2003 sp2)

    I'd simply open a recordset on the MSysObjects table, then loop through the records and compare the DateUpdate field in each record to the current date, and act as needed.

    ...
    Set rst = db.OpenRecordset("MSysObjects", dbOpenDynaset)

    Do While Not rst.EOF
    If rst!DateUpdate < Date Then
    ...
    Else
    ...
    End If
    rst.MoveNext
    Loop

    rst.Close
    ...

    (Yes, I'm still using Access 2002 at home)

  12. #12
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lookup record and compare (2003 sp2)

    I am really weak on the Do Loop. So if I have 6 non-sequencial Object Id's how could I loop just through the 6?
    Thanks
    chuck

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup record and compare (2003 sp2)

    You could restrict the recordset to those IDs:

    Dim strSQL As String
    strSQL = "SELECT * FROM MSysObjects WHERE ID In (2, 3, 5, 8, 13, 21)"
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    Do While Not rst.EOF
    ...

    Or you could loop through all records and add a check:

    Set rst = db.OpenRecordset("MSysObjects", dbOpenDynaset)
    Do While Not rst.EOF
    Select Case rst!ID
    Case 2, 3, 5, 8, 13, 21
    ...
    Case Else
    ...
    End Select
    ...

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup record and compare (2003 sp2)

    BTW instead of the MSysObjects table, you could also use the CurrentData.AllTables collection and look at the Name and DateModified properties of each item.

  15. #15
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: lookup record and compare (2003 sp2)

    Once again Hans, you have been a tremendous help, and you stay up late too.
    Thanks
    chuck

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •