Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  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

    DateSerial (2002 SP-2)

    Well, spent the better part of the last hour searching MSKB and the Lounge search and still can't find a comprehensive source for determining date parameters. My feeble gray matter still can't seem to wrap itself around the concept. I had been using <DateSerial(Year(Date(),1,1)) to return last years orders (which worked just fine because there was only one years worth of prior orders "2002") but this year it, of course, returns all years prior to this year "2002" and "2003". I tried a number of ways to get all dates in previous year (not all previous years) with Between(DateSerial(Year(Date()-1),1,1))And(DateSerial(Year(Date()-1),12,31)) but that doesn't work either. Anyone know of a complete source/tutorial for DateSerial (assuming that's what I should be using)?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DateSerial (2002 SP-2)

    Is there a particular reason you're using DateSerial? If the field is a date, the simple way is to set up a calculated field in your query using Year([DateField]) and then set the criteria to Year()-1. This assumes that DateField is the name of the field being used to filter. That should return any records where the dates are for last year.
    Charlotte

  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: DateSerial (2002 SP-2)

    Charlotte:

    I want to set up several different date ranges for a Filter by Form (and save as queries). I do not want to have to set up calculated fields just for these date parameters. If I just use Year()-1, of course, in the filter criteria it returns:
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    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: DateSerial (2002 SP-2)

    Finally!
    Between (DateSerial(Year(Date())-1,1,1)) And (DateSerial(Year(Date())-1,12,31))
    Returns all records for previous year (regardless of current year).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: DateSerial (2002 SP-2)

    I don't understand why you object to setting up a calculated field for the date parameters; it is often the most efficient way to get things done. But anyway, the brackets are wrong in your expression. Try

    Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31)

    In the expression you posted, you had DateSerial(Year(Date()-1),1,1). If you look at the brackets carefully, starting at the current date Date(), you will see that first 1 is subtracted, resulting in yesterday's date; then the year is calculated, resulting in 2004 (since yesterday was in 2004 too); finally DateSerial(2004,1,1) is calculated, i.e. January 1, 2004. You also don't have spaces between the parts of the expression.

    In the expression I propose, I have DateSerial(Year(Date())-1,1,1). Here, first the year of the current date is calculated: Year(Date()), resulting in 2004, and only then is 1 subtracted, resulting in 2003; finally DateSerial(2003,1,1) is calculated, i.e. January 1, 2003/

    There is no special tutorial for using DateSerial, as far as I know. You just have to look carefully at the individual parts, and analyze what they do.

  6. #6
    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: DateSerial (2002 SP-2)

    Hans:

    <img src=/S/argue.gif border=0 alt=argue width=50 height=25>
    Well, I suppose this day was bound to come. I respectfully (and somewhat frightfully) submit my position to the Master:
    This "function" is as applied to Filter by Form for a continuous form (already crowded). Why would I create additional (redundant) fields to calculate dates? Both of the following work perfectly fine (even though I lack the knowledge to explain them <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    Previous Year:
    Between (DateSerial(Year(Date())-1,1,1)) And (DateSerial(Year(Date())-1,12,31))
    Previous Month:
    Between (DateSerial(Year(Date()),Month(Date())-1,1)) And (DateSerial(Year(Date()),Month(Date()),0))

    Both (and several others) are being saved/called as queries from the Filter by Form window (right-click). If there is something wrong with doing it this way (or simply less efficient) I remain your humble (though somewhat argumentative) student. I would not wish to mislead any casual observer(s).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: DateSerial (2002 SP-2)

    There is nothing wrong with these expressions - congratulations on working it out on your own before I posted essentially the same one.

    In your original question, you didn't mention that this was meant to be used in Filter by Form, so both Charlotte and I assumed that you were designing a query. In a query, adding a calculated field is often more efficient and/or easier to understand than constructing a complicated expression. I agree that it is not efficient to include any number of calculated fields in a form just to have them available for Filter by Form.

    (You could use a calculated field in Advanced Filter; this option is more powerful and flexible than Filter by Form, but requires the user to know how queries are designed.)

  8. #8
    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: DateSerial (2002 SP-2)

    Hans:

    Sorry about the confusion. My original question was really a general one concerning date parameters (although I am beginning to learn that there are no real "general" questions in Access); my apologies to both you and Charlotte. I did not mention the Filter by Form until <post#=368325>post 368325</post#>. Thanks again for your always present, and generous, help/advise.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  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: DateSerial (2002 SP-2)

    Hmm. . .
    Nibbling at the fruit of my success, I've come upon a worm in my logic.
    Current date (4/30), trying to return the previous week (Sun-Sat; 4/18-4/24). This would be along the same lines as the other (previous year, previous month); ie. return previous full week regardless of current date.
    Between (DateSerial(Year(Date()),Month(Date()),Weekday(Dat e())+1)) And (DateSerial(Year(Date()),Month(Date()),Weekday(Dat e())+7))
    Returns 4/7 - 4/13; which makes perfectly good sense to me, but I can't figure out how to get the intended results. Any advise?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: DateSerial (2002 SP-2)

    Try this:

    Between Date()-Weekday(Date())-6 And Date()-Weekday(Date())

    Explanation: Weekday(Date()) returns a number corresponding to the day of the week: Sunday = 1, Monday = 2 etc. By subtracting this from the current date, you get last Saturday: for example, today (as I write this) is Friday, so Weekday(Date()) = 6. Today minus 6 days is last Saturday. The Sunday before that is another 6 days back.

  11. #11
    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: DateSerial (2002 SP-2)

    Hans:

    Before I start running with this, and as I understand this, it would return last weeks dates with the criteria based on todays date. That being the case, would it not then work only for today? By that I mean to say, can one return last weeks dates (Sun-Mon) regardless of the current day of the present week? Did I say that right?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: DateSerial (2002 SP-2)

    >> return last weeks dates (Sun-Mon) regardless of the current day of the present week

    That is exactly what the expression I posted does.
    Today is a Friday, so Weekday(Date()) = 6. The expression becomes "Between (today - 12 days) And (today - 6 days)" where today=Friday.
    Tomorrow is a Saturday, so Weekday(Date()) = 7. The expression becomes "Between (today - 13 days) And (today - 7 days)" where today=Saturday.
    Day after tomorrow is a Sunday, so Weekday(Date()) = 1. The expression becomes "Between (today - 7 days) And (today - 1 days)" where today=Sunday.

  13. #13
    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: DateSerial (2002 SP-2)

    Hans:
    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    ...I need a break.
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    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: DateSerial (2002 SP-2)

    This is just kiling me.
    If "Between Date()-Weekday(Date())-6 And Date()-Weekday(Date())" returns the previous week, what on earth returns the current week (regardless of the current day of the current week)? Nothing I've tried works.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: DateSerial (2002 SP-2)

    Try this:

    Between Date()-Weekday(Date())-6+7 And Date()-Weekday(Date())+7

Page 1 of 2 12 LastLast

Posting Permissions

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