Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date periods (Access 97/Windows 98)

    I am getting very frustrated with dates in queries any I wonder if someone can tell me the answer.

    I want to get all the data out of a report for a complete year.

    OK I know that I can put

    between #1/1/01# and #31/12/01#

    and that works well. I also know that I can use

    between date()-30 and date()

    to get the last 30 days information

    But is there a start of year function and an end of year function rather like the date function?

    The thing is that there are a lot of queries in the database that use

    between #1/1/01# and #31/12/01#

    and as I just design the database for others to use, it means at then end of the year I have to go through every query changing the year for o1 or 02 or whatever. The ideal solution would be for it know the beginning and end of the year as a function so I could put

    between startofyear and endofyear

    Does anyone have any ideas?
    Thanks in advance for any advice

    Michael

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date periods (Access 97/Windows 98)

    Try

    dateserial(datepart("yyyy",date()),1,1)

    and

    dateserial(datepart("yyyy",date())-1,12,31)

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date periods (Access 97/Windows 98)

    Hi MIchael,
    There are various ways to do this. You could store the relevant dates in a table and look them up from there; you could use parameters in your query; you could have a main form with fields on it for start and end date and have all your queries reference that. Incidentally, if you added a field to your query to extract the year from your date field (e.g. CurYr: Year([datefield]) you could then simply add a criterion of 2001 or whatever for that field.
    The danger of using something like Year(Date()) is that you might be running the report on 1st Jan for the previous year and it won't work.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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