Results 1 to 9 of 9
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Date Parameters (XPDev)

    I have two queries (qryMasterArchive and qryMasterCurrent) upon which several other queries/forms/reports are built.

    qryMasterArchive has a date criteria of <=(#12/31/02#) ; returns all orders prior to this year
    qryMasterCurrent has a date criteria of >=(#01/01/03#) ; returns all orders this year

    This all works just fine until New Years rolls around, then of course you need to get back into the queries to adjust the criteria. I've been trying to come up with a way to determine the "current year" for the criteria expression, but my "date math" skills ain't too hot. It's probably not as difficult as I'm making it, but it doesn't take much to send me over the edge. Two hours now and the only thing I haven't done is crash the app. MSHelp doesn't take kindly to "current year", or even "date" for that matter, it returns everything but what I want to know.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Parameters (XPDev)

    The "formula" for today's date is Date().

    January 1 in the current year is DateSerial(Year(Date()), 1, 1) and December 31 in the current year is DateSerial(Year(Date()), 12, 31) or, slightly sneakier, DateSerial(Year(Date()), 13, 0).

    All of these use the DateSerial function in combination with the Date function. DateSerial(year, month, day) returns the date value corresponding to the specified year, month and day.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Date Parameters (XPDev)

    Uuuuhhh. . . <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Me no speak Netherlandish <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    How does that apply to "find all orders for last year" without specifying what the current year is by user input?
    Nice to hear from you again Hans!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Parameters (XPDev)

    One way to find orders for last year is to add a calculated column Year(DateField) where DateField is the field you want to restrict to last year, and set the criteria for this field to Year(Date())-1.
    Date() is today, Year(Date()) is the current year, and Year(Date())-1 is last year. You don't need to see this calculated column, only use it to restrict the records returned, so clear the View check box.

    Another way is to put criteria directly under the DateField field (whatever its name in your queries is); it would be
    Between DateSerial(Year(Date())-1, 1, 1) And DateSerial(Year(Date())-1, 12, 31)
    Today, this would evaluate to Between DateSerial(2002, 1, 1) And DateSerial(2002, 12, 31).

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Date Parameters (XPDev)

    Sorry friend. I typically went into confusion mode upon seeing something foreign (no pun intended - to either of us)
    Armed with your reply (and suppressing panic) I tried the following:

    >(DateSerial(Year(Date()),12,31)) returns all orders for last year (and without having to input what year this is).
    Between (DateSerial(Year(Date()),1,1)) And (DateSerial(Year(Date()),12,31)) returns all orders for the current year (and without having to input what year this is)

    Cool! Is that still going to work at midnight on 12/31/03?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Parameters (XPDev)

    Hmm, >(DateSerial(Year(Date()),12,31)) currently evaluates to >DateSerial(2003,12,31), in other words, orders for next year and after, not for last year.

    Criteria like these will change their range by one year strictly at the moment the new year begins.

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Date Parameters (XPDev)

    Ooops, had the little "thingy" going the wrong way in the post

    <font color=red><</font color=red>(DateSerial(Year(Date()),12,31))

    That returns all orders for last year, and before I suppose.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Parameters (XPDev)

    <(DateSerial(Year(Date()),12,31) currently evaluates to <DateSerial(2003, 12, 31), that is, before December 31, 2003. To get everything during 2002 and before, use <DateSerial(Year(Date()), 1, 1)

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Date Parameters (XPDev)

    Sorry, my daughter called (long distance).

    I swear (I just checked the results again) and <(DateSerial(Year(Date()),12,31)) is returning nothing but last years orders. Go figure!
    At any rate, I couldn't have gotten here without your help (again). Thanks so much for your input.

    EDIT: I just zoomed the criteria cell and discovered that there was something else in there "And <=(#12/31/02#) " Sorry about that. I guess when I was editing the entry I should have zoomed it, I thought I had hit "end", but I guess not. That's not the first time I've had that happen - zoom is probably the only way to assure what's actually there.

    Again, thanks for the help; you were right as usual <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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