Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automatic date increment (2000 all updates)

    A database contains appointments and events. Two of the events have an EventID of 2 (Anniversaries) and 3 (Birthdays)

    What I am attempting to do is, upon opening of the Main Menu, run a module to automatically increment an Anniversary or Birthday event by one year...if the date of the existing record is less than the current date.

    I have two queries. One is called QueryGreaterThanDate. It shows records which have dates greater than the current date. The SQL is
    <code>SELECT tblDates.MyDate, tblEvents.Event, tblDates.Notes
    FROM tblEvents INNER JOIN tblDates ON tblEvents.EventID = tblDates.EventID
    WHERE (((tblDates.MyDate)>Date()) AND ((tblEvents.Event)="Birthday")) OR (((tblEvents.Event)="Anniversary"));</code>

    The second query is called QueryCheckForIn. The SQL is
    <code>SELECT tblDates.MyDate, tblDates.EventID, tblEvents.Event, tblDates.Notes, tblDates.Completed
    FROM tblEvents INNER JOIN tblDates ON tblEvents.EventID = tblDates.EventID
    WHERE (((tblDates.MyDate)<Date()) AND ((tblDates.EventID)=2 Or (tblDates.EventID)=3) AND ((tblDates.Notes) Not In (Select tblDates.Notes FROM QueryGreaterThanDate)) AND ((tblDates.Completed)=No));</code>

    The module that is called has the following structure
    <code>Public Function Daily()

    Dim strSQL As String
    Dim strSQL2 As String
    Dim strSQL3 As String

    DoCmd.OpenQuery "QueryCheckForIn"

    If Not IsNull("[QueryCheckForIn].MyDate") Then
    strSQL = "UPDATE tblDates SET tblDates.MyDate = DateAdd(""yyyy"",1,[MyDate]), tblDates.Completed = No " _
    & "WHERE tblDates.MyDate < Date() And tblDates.EventID = 2 Or tblDates.EventID = 3 And tblDates.Notes = Queries!QueryCheckForIn.Notes"
    Else

    strSQL = "UPDATE tblDates SET tblDates.Completed = Yes " _
    & "WHERE (((tblDates.MyDate)<Date()));"

    End If

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    'DoCmd.Close acQuery, "QueryGreaterThanDate"
    DoCmd.Close acQuery, "QueryCheckForIn"

    End Function</code>

    The module results in a run-time error, and the problem is in the WHERE clause, specifically the tblDates.Notes = Queries!QueryCheckForIn.Notes part.

    Any suggestion as to how to fix this so that the query field is properly referenced?

    Thanks.

    Tom

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

    Re: automatic date increment (2000 all updates)

    1) The test

    If Not IsNull("[QueryCheckForIn].MyDate") Then

    is useless - you're not checking whether a field value is null, but whether the literal string "[QueryCheckForIn].MyDate" is null, which it is not, obviously.

    2) There is no Queries collection, so Queries!QueryCheckForIn.Notes makes no sense.

    You should open a DAO or ADODB recordset on QueryCheckForIn, and refer to fields in the recordset.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: automatic date increment (2000 all updates)

    You also use OpenQuery like a recordset. It doesn't operate how you think it does, it in fact runs the query and shows the results in it's own window, much like if you manually open a query manually from the database window and run it there.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic date increment (2000 all updates)

    Patt
    That does the job. Here's a Module that is called when the Main Menu is opened...

    <code>
    Public Function Daily()

    Dim strSQL As String

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QueryCheckForIn"
    DoCmd.Close acQuery, "QueryCheckForIn"

    strSQL = "UPDATE tblDates SET tblDates.Completed = Yes " _
    & "WHERE (((tblDates.MyDate)<Date()));"

    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    End Function</code>

    The Query updates Anniversary and Birthday events. The SQL statement updates other records as needed.

    The SQL for "QueryCheckForIn" is as follows...
    <code>UPDATE tblEvents INNER JOIN tblDates ON tblEvents.EventID = tblDates.EventID SET tblDates.MyDate = DateAdd("yyyy",1,[MyDate])
    WHERE (((tblDates.MyDate)<Date()) AND ((tblDates.EventID)=2 Or (tblDates.EventID)=3) AND ((tblDates.Notes) Not In (Select tblDates.Notes FROM QueryGreaterThanDate)) AND ((tblDates.Completed)=No));
    </code>

    Thanks, Patt.
    - - - - - -

    Hans, thanks also for your tips on my errors, I knew they were problematic, and was trying to figure out how to do it correctly.

    Tom

Posting Permissions

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