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

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,
 
J

Jeff Boyce

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
 
S

Serendipity

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.
 
S

Serendipity

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
 
J

Jeff Boyce

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top