Limit data in field to within 90 days of another field...

  • Thread starter Thread starter Serendipity
  • Start date Start date
S

Serendipity

I am trying to figure out how to limit the information on a query. I am have
a table (tblTask1401) that lists all the work orders issued to do a
particular task that I should receive a report on. I have a qry(qryInspDates)
that shows all the inspections that I have received a report on. In my table
I have a field scheduled date and in the query I have an Inspection date.
When I run the query the Inspection date lists against all the scheduled
dates. I want the query to limit the inspection date being listed to only
when it is within 90 days from the scheduled date and / or 30 days before.
When the inspection is done the WO is not included on the inspection form so
I can't match up that way. Any ideas on how I can do this?

Qry I have right now:
TblTask1401 with field= Address ID
TblTask1401 with field= WO#
TblTask1401 with field= Equip#
TblTask1401 with field= Scheduled Date Limited to like"*/*/2009"
QryInspDates with field= Inspection Date Limited to like"*/*/2009"
QryInspDates with field= Inspected By

An example of what I get vs what I need:
Scheduled date 05-04-09 Inspection Date 04-15-2009
Scheduled date 11-2-09 Inspection Date 04-15-2009

What I want it to return:
Scheduled date 05-04-09 Inspection Date 04-15-2009
Scheduled date 11-2-09 Inspection Date Not Received

Thanks for your help!

PS... I have a hard time understanding "expert" language. I am a step by
step person when learning!

Thanks,
 
Take a look in Access HELP at the DateDiff() function. I think you could
use that in a query to find the records you're after.

Also look at using "Between ... And ..." in the selection criterion in your
query.

Regards

Jeff Boyce
Microsoft Access MVP
 
Here is what I tried:
LagTime: DateDiff("d", [Scheduled Date], [InspectionDate])

But I get the error message:
The expression you entered has an invalid .(dot) or ! operator or invalid
parentheses.

I used this reference:
You can use these expressions in a calculated field in a query.

Expression Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays in the LagTime
field the number of days between the values of the OrderDate and ShippedDate
fields.
 
Got it to work with:

Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])

Not sure why changing the field name helped?

? How can I now limit this to between -30 and 60 days?


Serendipity said:
Here is what I tried:
LagTime: DateDiff("d", [Scheduled Date], [InspectionDate])

But I get the error message:
The expression you entered has an invalid .(dot) or ! operator or invalid
parentheses.

I used this reference:
You can use these expressions in a calculated field in a query.

Expression Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays in the LagTime
field the number of days between the values of the OrderDate and ShippedDate
fields.

Jeff Boyce said:
Take a look in Access HELP at the DateDiff() function. I think you could
use that in a query to find the records you're after.

Also look at using "Between ... And ..." in the selection criterion in your
query.

Regards

Jeff Boyce
Microsoft Access MVP
Click to expand...
Click to expand...
 
I don't see anything wrong. Perhaps Access has some subtle corruption in
this query. Have you tried starting from scratch on this one?

Regards

Jeff Boyce
Microsoft Access MVP


Serendipity said:
Here is what I tried:
LagTime: DateDiff("d", [Scheduled Date], [InspectionDate])

But I get the error message:
The expression you entered has an invalid .(dot) or ! operator or invalid
parentheses.

I used this reference:
You can use these expressions in a calculated field in a query.

Expression Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays in the LagTime
field the number of days between the values of the OrderDate and
ShippedDate
fields.

Jeff Boyce said:
Take a look in Access HELP at the DateDiff() function. I think you could
use that in a query to find the records you're after.

Also look at using "Between ... And ..." in the selection criterion in
your
query.

Regards

Jeff Boyce
Microsoft Access MVP
Click to expand...
Click to expand...
 
Back
Top