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
note the intrinsic Access/VBA functions date() and now() trip up a lot of people
dateRecorded >= dateadd("d", -90, date())
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.
whereas datediff finds the number of specified time/date periods
dateRecorded >= datesub("d", 90, date())
which woudl give the difference in days between the two dates. you woudl need to check the order was right
DateDiff ( "d", date(), daterecorded)
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
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
a word of caution now is the current system date and time of the computer running the query
select my, column, list from mytable
where dateRecorded >= dateadd("d",-90,date())
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
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
so if you want all rows whise daterecorded is within the last 90 days taking note of the time element
dateRecorded >= dateadd("d",-90,date())
dateRecorded >= dateadd("d",-90,now())
Last edited by healdem; 06-06-16 at 09:57.
I'd rather be riding on the Tiger 800 or the Norton