Results 1 to 9 of 9

Thread: Date Query

  1. #1
    Join Date
    Mar 2005
    Posts
    16

    Unanswered: Date Query

    hello all,

    i am trying to find the the last friday of the present month. I am using the following which gives me the last day of each month:

    DateSerial(Year(Date()), Month(Date()) + 1, 0)


    Hope someone can help, maybe im going about it wrong with the above!

    Thanks for looking

    iso

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    The following works, but I'm not sure if there is a more effective way. If you don't need to do this calc A LOT (querying thousands of records or something), it'll be fine.
    Code:
    Dim dateLastFriday As Date
    dateLastFriday = DateSerial(Year(Date), Month(Date) + 1, 0)
    
    While Not Weekday(dateLastFriday) = vbFriday
        dateLastFriday = DateAdd("d", dateLastFriday, -1)
    Wend
    Me.Geek = True

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Red face

    Try this:

    Dim LastDay As Date, LastFriday As Date
    LastDay = DateAdd("m",1,DateVariable) - Day(DateVariable)
    LastFriday = LastDay - Weekday(LastDay ) + IIf(Weekday(LastDay ) >= 6, 6, -1)
    Hope this helps,

    Sam
    Last edited by Sam Landy; 06-20-07 at 16:50.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Sam Landy
    Dim LastDay As Date, LastFriday As Date
    LastDay = DateAdd("m",1,DateVariable) - Day(DateVariable)
    LastFriday = LastDay - Weekday(LastDay ) + IIf(Weekday(LastDay ) >= 6, 6, -1)
    Won't you have to use the DateAdd function to add days? I didn't think you could just add Date formats with number formats.
    Me.Geek = True

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Copy and paste it into your Immediate window and keep changing the value of DateVariable. It works.

    I think that dates are stored in Access as numbers, with the whole number(number to the left of the decimal) as the date, and the number to the right of the decimal as the time. In Excel it's definitely like that, so I assume in Access as well.

    Sam

  6. #6
    Join Date
    Mar 2005
    Posts
    16
    Hello Sam & Nick and thanks for both of your replies,

    Where do i put the code? Im just learning really.

    Many Thanks

    iso

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Someone wrote a very similar problem in this thread a few days ago. The link in there provides a nice and easy way(s) to calculate something similar to what you want. I advise you read it (it's fairly easy to follow) and see if you can understand the logic that is involved. Post any questions back here if you want

    Next comes the question of where (or how) do you want this calculation to be displayed...
    George
    Home | Blog

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by iso96
    Hello Sam & Nick and thanks for both of your replies,

    Where do i put the code? Im just learning really.

    Many Thanks

    iso
    Don't have a clue. You didn't show us any of your code.

    Sam

  9. #9
    Join Date
    Mar 2005
    Posts
    16
    Sam,

    Clearly i meant the code you wrote! But never mind ive sorted it thanks for all your help.

    iso

Posting Permissions

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