Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,

    I know this is likely easy to do, but I can't seem to get it.

    In a query, I want to return the next record from a table whos date is greater than today. For example, the table has 2 fields: meetingdate and venue.
    I want to return the record which contains the next date after today?

    How is this done?

    Thanks,

    Van

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='vanleblanc' post='766919' date='23-Mar-2009 23:22'][/quote]
    The where clause is SQL would be something like this :
    WHERE (YourDateField)=DateAdd("d",1,Date())
    or in design view :
    [attachment=83019ayAfter.jpg]
    Attached Images Attached Images
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Francois,

    doesn't that just find the next day (ie. today +1)? what if the next meeting date is 5 days from now for example?

    Van

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='vanleblanc' post='766963' date='24-Mar-2009 02:04'][/quote]
    Ok. Right.
    What about this one :
    WHERE (YourField)=DLookUp("YourField","YourTable","YourF ield > date()");
    or in design :
    [attachment=83022ayAfter2.jpg]
    Change the names where adequate and in design view change the Belgian ; by the US ,
    Attached Images Attached Images
    Francois

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    merci boucoup!

    works great.

    Van

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='vanleblanc' post='766966' date='24-Mar-2009 02:23'][/quote]
    This will work ok only if your table is sorted on your MeetingDate. If not, create a query with only one field, MeetingDate, sort it ascending and do the DLookUp on the new query instead of the table.
    Francois

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I would do this in two steps.

    First query finds all records where the date is greater than today. > Date()

    The second query would find the record with the smallest date from the records in the first query.
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='johnhutchison' post='766981' date='24-Mar-2009 08:37']I would do this in two steps.

    First query finds all records where the date is greater than today. > Date()

    The second query would find the record with the smallest date from the records in the first query.[/quote]
    You can do that in one query:
    - Create a query based on the table.
    - Select View | Totals or click the Totals button on the toolbar (or ribbon).
    - Add the MeetingDate twice to the design grid, and no other fields.
    - Set the Total option for the first column to Min.
    - Set the Total option for the second column to Where.
    - This will clear the Show check box for the second column. That's OK.
    - Enter the following condition in the Criteria row for the second column:

    >Date()

    - Save this query.

    To retrieve the entire record for the first date after today:
    - Create a new query based on the original table and on the query just saved.
    - Join them on MeetingDate vs MinOfMeetingDate.
    - Enter * from the table (or just the fields you need) to the design grid.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, that works really well. just the solution I was looking for.

    Regards,
    Van

Posting Permissions

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