Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Posts
    12

    Unanswered: Query - using multiple Likes(?) IIf and DateAdd()

    Hi there

    I have been so stuck trying to work out what is probably not a difficult query.
    I would find it easier to do it in VBA but my employers want it done in a query.
    Basically I have to retrieve pricing data for certain restaurants for the last week's entries (Monday - Sunday).
    The query can be run on any day of the following week. This means that I want to get (-7 days - the week in question ) plus whatever day of the week it currently is, i.e., using the Weekday(Date()) I can find out what day it is (Monday = 1). I need to add this value to 7 to know how far back I want to look.
    I'm not that familiar with using IIF in an SQL statement so not sure where to incorporate it to establish how far to look back. The statement below defaults to 7 days.
    The other problem I am looking into is, what is the range for Access when using LIKE? I need to get a couple of different restaurants but whenever I use
    LIKE "*aaa*" AND "*bbb*"...
    I get nothing!

    At the moment I have something like this:

    SELECT tblPurchasingDetail.[Item Number], tblPurchasingDetail.[Item Description], tblPurchasingDetail.Supplier, tblPurchasingDetail.[Package Size], tblPurchasingDetail.[Price Lessperpiece], tblPurchasingDetail.[Invoice Date], tblPurchasingDetail.[Location Name]
    FROM tblPurchasingDetail
    WHERE ((tblPurchasingDetail.[Location Name]) Like '*170 ST*')
    AND ((tblPurchasingDetail.[Invoice Date]) between DateAdd("d", -7, Date()) and Date())

    Any (and I mean any!) offerings of assistance on these topics would be so so appreciated.
    Thanks a lot

    Kate

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Query looks ok to me...

    Could you post a sample result set and why you don't like it?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2005
    Posts
    12
    Hiya
    This query does work ok, but it doesn't do what I want it to do. At the moment it is set to default to 7 days (assuming the query is run on Monday)

    between DateAdd("d", -7, Date()) and Date())

    but I need to be able to increment the (-7) if for example, the query is run on Tuesday, this would then be (-8), Wednesday (-9) and so on.

    I just don't know how to incorporate an IIF statement into this SQL statement!

  4. #4
    Join Date
    Nov 2005
    Posts
    12
    Something like this...

    SELECT tblPurchasingDetail.[Item Number], tblPurchasingDetail.[Item Description], tblPurchasingDetail.Supplier, tblPurchasingDetail.[Package Size], tblPurchasingDetail.[Price Lessperpiece], tblPurchasingDetail.[Invoice Date], tblPurchasingDetail.[Location Name]
    FROM tblPurchasingDetail
    WHERE ((tblPurchasingDetail.[Location Name]) Like '*170*')

    AND ((IIF(WeekDay(Date()) = 0) ((tblPurchasingDetail.[Invoice Date]) between DateAdd("d", -7, Date()) and Date())))
    OR ((IIF(WeekDay(Date()) = 1) ((tblPurchasingDetail.[Invoice Date]) between DateAdd("d", -8, Date()) and Date())));

    I feel confused just looking at it, and of course this doesn't work!

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    perhaps something like:
    DateAdd("d", -7 - weekday(date()) - 1, date())
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2005
    Posts
    12
    Teddy you are indeed a wee access monkey, thanks so much!

    Kate

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Teddy
    perhaps something like:
    DateAdd("d", -7 - weekday(date()) - 1, date())
    Hi Teddy,

    Good to see you again. I was wondering if you could break that down just a bit so I can understand what is going on. Mainly with this -7 - weekday. I have used DateAdd before, just the basic to go back or forward from a set date, but just needed enlightenment on that one part. Looking to learn more.

    thanks and have a nice one,
    BUD

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    On second look, that was just lazy coding on my end. It should be written:

    DateAdd("d", weekday(date()) - 8, date())

    Weekday returns the day of the week. Since Sunday gets counted as the first day, we have to subtract one additional day from the calculation, that's why it's 8 instead of 7
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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