Results 1 to 3 of 3
  1. #1
    unslog is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    8

    Dynamic dates with query

    Hi

    How do I create query to only look at last Wednesday to this tuesday?

    This needs to be dynamic, so the system will always look at last Wednesday to this Tuesday, regardless of which day of current week the query is executed.

    hope this makes sense.

    Thanks

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can use a function such as this:

    Public Function GetTues()

    Dim DateX As Date
    DateX = Date
    Do Until DatePart("w", DateX) = 3
    DateX = DateX - 1
    Loop
    GetTues = DateX

    End Function

    Then your criteria in your query will be : Between GetTues()-7 And GetTues()

    Note that if you have a lot of data the query might be a bit slow to run.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I was thinking about this - don't ask me why! But I was bothered by the time it could take if you have a lot of data. And I realised that it is quite silly to keep doing this same calculation that only needs to be done once. There will only be one date of "what is last Tuesday" so it only needs to be done once, not twice for every record which is the way I gave it to you. I would recommend that you do the GetTues calculation only once and then reference that field in your query criteria. The only two options I can think of is either to put it on a table (do a make table query first with the date as the only column and then bring that table in to your query) or else put it as a textbox on a form and then reference that in your query.

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

Similar Threads

  1. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM
  2. Dynamic Form, Dynamic labels/ captions?
    By JFo in forum Programming
    Replies: 15
    Last Post: 10-12-2011, 08:33 PM
  3. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  4. Dynamic Query
    By pushpm in forum Queries
    Replies: 0
    Last Post: 04-22-2009, 12:58 PM
  5. Dynamic SQL Query
    By Squeaner in forum Queries
    Replies: 0
    Last Post: 09-25-2008, 02:37 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