Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    Unanswered: Between Last Monday and DateSelected

    I am having a mental blank here...

    What expression can I use in Access to represent dates between the date I have selected in my report dialog (let's just call that SD) and the Monday before it?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Do you mean the days between two dates?
    If so, then use the DateDiff function

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, I am aware of DateDiff, it's expressing "the previous Monday" part that has me stumped atm.

    Thanks though
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So run today you would search for between 24th and 26th of Nov?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    WHERE myDate BETWEEN DateAdd("d", -(Weekday(Date(), vbMonday)) + 1, Date()) AND Date()
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If it helps, I'd use the weekday function too.... and do some realtively simple integer mathmatics on the date. The only problem, I see is if the SD is a Sunday, in which case you will have to do a function,
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pootle flump
    So run today you would search for between 24th and 26th of Nov?
    Correct.

    Quote Originally Posted by pootle flump
    Code:
    WHERE myDate BETWEEN DateAdd("d", -(Weekday(Date(), vbMonday)) + 1, Date()) AND Date()
    Excellent, thank you!! Now I look at this, I am convinced it wasn't a mental blank at all, I don't actually think I have done this before

    Thanks again, I'll fiddle around with this and let you know how I go

    Quote Originally Posted by healdem
    If it helps, I'd use the weekday function too.... and do some realtively simple integer mathmatics on the date. The only problem, I see is if the SD is a Sunday, in which case you will have to do a function,
    Hopefully, they won't be running this on a Sunday! I'll play around with WeekDay() as well and let you know what I finally do

    Thanks guys
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Totally awesome!

    I actually didn't realise what the second argument of Weekday() actually did.

    My expression ended up as:

    =IIf([InvoiceDate]>=DateAdd("d",-(Weekday([Forms]![dlgReporting]![SD],2))+1,[Forms]![dlgReporting]![SD]) And [InvoiceDate]<=[Forms]![dlgReporting]![SD],[Dollars],0)
    Thanks again
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My pleasure I've just realised it could be simplified:
    Code:
    ...DateAdd("d",-Weekday([Forms]![dlgReporting]![SD],3),[Forms]![dlgReporting]![SD]).....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah yes, good job

    I was also wondering if the Sunday issue could be addressed... I'm sure it could, but I am also sure they aren't going to be running this on Sunday
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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