Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: query with dates

    I am trying to produce a report of time entries that were posted in the last 90 days from today's date. I want any date recorded that is 90 days old or less to appear. I am thinking the query would include dateRecorded > (now - 90days) not really sure how to code it.

  2. #2
    Join Date
    May 2016
    Posts
    9
    Provided Answers: 1
    This site is what i used to set up a date range query. It prompts and asks for a start and end date. Not sure if that is ok or not.

    http://allenbrowne.com/casu-08.html

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    in a query,
    select * from table where [MyDate] between date() and DAteAdd("d",-90,Date())

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    although you could use direct integer subtraction you should use one of the datetime functions
    dateadd (or datesub) or possibly datediff would seem the most appropriate

    the easiest is probably
    Code:
    dateRecorded >= dateadd("d", -90, date())
    note the intrinsic Access/VBA functions date() and now() trip up a lot of people
    date() is the current system date
    now() is the current system date and time.
    ..both are the system date/time of the current computer, which may or may not be accurate
    datesub is an alternative to dateadd.
    Code:
    dateRecorded >= datesub("d", 90, date())
    whereas datediff finds the number of specified time/date periods
    Code:
    DateDiff ( "d", date(), daterecorded)
    which woudl give the difference in days between the two dates. you woudl need to check the order was right

    how you put that in a query in the query designer Im not sure. I tend not to use it. but I'd hazard a guess that in the criteria for the daterecorded column you just put
    Code:
    >= dateadd("d",-90,date()
    the query browser generates SQL which is what I tend to use so in SQL view that would be added to an existing where clause or just as a where clause

    Code:
    select my, column, list from mytable
    where dateRecorded >= dateadd("d",-90,date())
    a word of caution now is the current system date and time of the computer running the query
    if you want all sales records posted in the last 90 days then you need to be clear what you mean.

    say its 12:30 on the 6th of June 2016, do you mean
    all sales records from 90 days ago posted on or after 12:30 the 8th of March 2016
    or
    all sales records from 90 days ago posted on or after the 8th of March 2016

    it depends on how you set the value of daterecorded.
    did your users type in the date or have you used a default value such as now()

    if your users typed the date in then its unlikely (unless you let 'em) that they have stored a time).
    if you used a default value set to now() then you have stored the system date AND time, if you used date() then you used the system date only (well to be picky you used the system date with a time component of 00:00:00)

    so if you want all rows whise daterecorded is within the last 90 days irrespective of the time element
    Code:
    dateRecorded >= dateadd("d",-90,date())
    so if you want all rows whise daterecorded is within the last 90 days taking note of the time element
    Code:
    dateRecorded >= dateadd("d",-90,now())
    your call
    Last edited by healdem; 06-06-16 at 10:57.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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