Date Parameters (XPDev)

Topic

New Reply

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

qryMasterArchive 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.

Reply | Quote

Replies

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.

Reply | Quote

Uuuuhhh. . .

Me no speak Netherlandish

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!

Reply | Quote

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).

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Ooops, had the little “thingy” going the wrong way in the post

<(DateSerial(Year(Date()),12,31))

That returns all orders for last year, and before I suppose.

Reply | Quote

<(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)

Reply | Quote

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

Reply | Quote