Creating a query that pulls data witin 90 day's from the current date.

[mbenton]

Hi, I have created a simple query with a date field and other information. I only need the query to pull from the data in a 90 day window continually. In other words data dated older than 90 days is no longer relevant. I specified in my criteria the following: Between Date() and DateAdd("M",-3,Date()). I thought this would pull the data from 3 months back but it's not pulling any data. Is the criteria incorrect? Thanks.

[orange]

Show the entire query SQL.
Do you want 90 days or 3 months?
What exactly do you want this data for?

[Micron]

Date-90 should work. However, if your fields contain time values, using Between with dates (and no time) poses a special problem. If the field values were
07/01/2016 2:00:00 PM and 07/13/2016 5:00:00 AM, then for
Between #07/01/2016# And #07/13/2016#, you will get no values for 07/13/2016 because no time was specified. The second date cutoff time will be 00:00:00 (midnight).
>= and <= operators or adding a suitable hour to the end date (DateAdd) is required.

[Micron]

glitch caused me to double post (all original text disappeared when trying to edit)

[mbenton]

To give background: We have three shifts of workers. Often 2 or more people who work different shifts may work on the same account. I've been asked to create a Comments Log that will flag accounts and append them to a table where comments could be written if necessary. We only need the comments log to cover a 90 day moving window of time. The date field that I am using in my query does not have time format bound to it. This is my complete SQL statement for the query:


SELECT Format([DateEntered],"mm-dd-yyyy") AS [Log Date], AppendLogtbl.OrderNo, AppendLogtbl.RequestorName, AppendLogtbl.ExpediteWorker, AppendLogtbl.ExpediteReason, AppendCommentstbl.[Issue/Problem], AppendCommentstbl.InitialActionTaken, AppendCommentstbl.ResponseFollowup, AppendCommentstbl.Completed, Format([UserCreated],"mm-dd-yyyy") AS [User Created]
FROM AppendLogtbl INNER JOIN AppendCommentstbl ON AppendLogtbl.NotesID = AppendCommentstbl.NotesID
WHERE (((Format([DateEntered],"mm-dd-yyyy")) Between Date() And DateAdd("m",-3,Date())));

When I ran the above query I got nothing back although I'm certain that I have a few comments appended that would fall into the range.

[mbenton]

I realize that the source table for the query does have the date and time format: "6/6/2016 1:00:00 PM" but I convert the format in my query.

[Micron]

I would either
- remove the criteria and see if you get any results. If not, suspect the join relationship, or your use of the format and date functions vs system settings (although I don't see what happened to your use of the DateValue function.
- test the join by building the query in stages - start a new query with one table and add components when the results of the query look as expected
- zip and post a db copy here, since reading your sql doesn't do much for me. There are too many factors that can't be seen without being able to look at the tables and joins.

[mbenton]

Thanks, I'm almost certain that it's the date/time format in the source table that's causing my query not to work. Unfortunately I can't change the table format due to company restrictions, however, I can add a second date field (without the time) and base my query criteria off that date. I'll post my results, thanks everyone for your help.

[mbenton]

It worked when I added the second date field. This is odd to me, I'm not sure why it wouldn't accept the converted date field from my query, yet when I added a new date field with the same format it worked! I guess Access doesn't like to query off converted date fields. ~~ **thanks.