Date query

T

THOR

I've been racking my brain to come up with this and i'm sure its something
simple i am missing. I have a "Last Report Done" Date and a "Next Projected
Report" Date field. The reports are done annually. I'm trying to make a
query where it tells me who is due for a report within 90 days or less. I
tried [Last Report Done]-[Next Projected Report] in the query but it gave me
an #error message in the field. So i reversed it and got the same
thing....anyone know what im doing wrong? Do you have suggestions on an
easier way? Thanks in advance
 
R

Rick B

To see who is due in 90 days or less, you would simply use your "next report
due" field. You would then put criteria that says if that date is less that
today + 90 days and greater than today...


Between Date() and Date()+90
 
A

Alex White MCDBA MCSE

The way I would solve this is get your [Next Projected Report]

and do the following

dim myDate as date

myDate = dateadd("d",90,Now)

this will give a date variable 90 days in front of today.

this is then the date that is used for your query



"Select * from TblTest Where [Next Projected Report]<#" &
Format(myDate,"yy­yy-mm-dd") & "#"

the above would give you everything with the [Next Projected Report] within
the next 90 days

be careful you need a flag in the record saying the the report has been done
so that they are excluded from the report.

Another thing try not ever using spaces within names it makes your coding
slightly harder if you want the spaces then Next_Projected_Report would
work, it means you can get rid of the [ ]

the above code has not been tested, but should do the job.
 
J

John Vinson

I've been racking my brain to come up with this and i'm sure its something
simple i am missing. I have a "Last Report Done" Date and a "Next Projected
Report" Date field. The reports are done annually. I'm trying to make a
query where it tells me who is due for a report within 90 days or less. I
tried [Last Report Done]-[Next Projected Report] in the query but it gave me
an #error message in the field. So i reversed it and got the same
thing....anyone know what im doing wrong? Do you have suggestions on an
easier way? Thanks in advance
Click to expand...

Simply use a criterion of

BETWEEN Date() AND DateAdd("d", 90, Date())

on the [Next Projected Report] datefield.


John W. Vinson[MVP]
 
Top