Results 1 to 5 of 5

Thread: date question

  1. #1
    Join Date
    Dec 2003
    Posts
    138

    Unanswered: date question

    Is it possible to automatically find the date for every 4th Tuesday?

    Here is my dilemma:

    We have kids who come into our program and we must calculate a date for them to see the Dr. It is required that they meet with him within 30 days of entry. The problem is - he is only here on Tuesdays. I need to calculate a date for the Tuesday before the thirty days are up. Any suggestions?

    Dendalee

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    How about something like:

    [myToday]+29-Weekday([myToday],3)

    where myToday is the starting date presumably today.

    So:
    if today is a monday then the return date will be 22 days hence
    if today is a tuesday then the return date will be 28 days hence
    if today is a wednesday then the return date will be 27 days hence
    etc

    Is that what you were after ?

    Chris

  3. #3
    Join Date
    Dec 2003
    Posts
    138
    sort of, but not quite. I need it to look at the date of entry field and say ok the Tuesday before the thirty days are up is......

    I'm not sure if I'm describing this in a clear way. Let me know if it's still confusing.

  4. #4
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    That's what the example does. Just replace [myToday] with the date you want to check.

    If it's in a table, you could do for all the records in it like this:
    Code:
    SELECT Table1.myDate,
      DateAdd("d",29-Weekday([myDate],3),[myDate]) AS LastTuesday
    FROM Table1
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    As wayne says, my example gives you the 4th tuesday after the given date.

    If you want the last tuesday before or on 30 days after the given date then try:

    [myToday]+31-Weekday([myToday]+2,3)

    hth
    Chris

Posting Permissions

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