Need formula for a querie!

[Cesar Urquidi]

Hello,
I created an "Equipment Calibration" table, including the following fields:

l_ ID _l_ Description _l_ Serial Number _l_ Next Calibration Schedule
_l

I created a querie for a report that gives me the equipment records that are
past due for calibration, using the following formula in the "Next
Calibration Schedule" field:

<Date( )

Formula means: Any records that are less that today's date, are past due.

Here's the problem:
I want to create a querie for a report that can give me the same records,
but instead of past due, I would like to see the records 30 days before
calibration expires. This is so I can prepare with time ahead.

Question:
What formula do I need to create???

 

[Klatuu]

SELECT ID, Description, [Serial Number], [Next Calibration Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Next Calibration Schedule]) < Date;

 

[Cesar Urquidi]

Hello,
I get an "Enter Parameter Value" window that says "Next Calibration Schedule".
I don't know what to enter.

I'm not sure if I understood your instruction, please explain.

--
Thank you,
Cesar Urquidi

SELECT ID, Description, [Serial Number], [Next Calibration Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Next Calibration Schedule]) < Date;
--
Dave Hargis, Microsoft Access MVP

Hello,
I created an "Equipment Calibration" table, including the following fields:

l_ ID _l_ Description _l_ Serial Number _l_ Next Calibration Schedule
_l

I created a querie for a report that gives me the equipment records that are
past due for calibration, using the following formula in the "Next
Calibration Schedule" field:

<Date( )

Formula means: Any records that are less that today's date, are past due.

Here's the problem:
I want to create a querie for a report that can give me the same records,
but instead of past due, I would like to see the records 30 days before
calibration expires. This is so I can prepare with time ahead.

Question:
What formula do I need to create???

 

[Klatuu]

Try changing it to:
SELECT [Equipment Calibration].ID, Description, [Equipment
Calibration].[Serial Number], [Equipment Calibration].[Next Calibration
Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Equipment Calibration].[Next Calibration Schedule])
< Date;

--
Dave Hargis, Microsoft Access MVP

Hello,
I get an "Enter Parameter Value" window that says "Next Calibration Schedule".
I don't know what to enter.

I'm not sure if I understood your instruction, please explain.

--
Thank you,
Cesar Urquidi

SELECT ID, Description, [Serial Number], [Next Calibration Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Next Calibration Schedule]) < Date;
--
Dave Hargis, Microsoft Access MVP

Hello,
I created an "Equipment Calibration" table, including the following fields:

l_ ID _l_ Description _l_ Serial Number _l_ Next Calibration Schedule
_l

I created a querie for a report that gives me the equipment records that are
past due for calibration, using the following formula in the "Next
Calibration Schedule" field:

<Date( )

Formula means: Any records that are less that today's date, are past due.

Here's the problem:
I want to create a querie for a report that can give me the same records,
but instead of past due, I would like to see the records 30 days before
calibration expires. This is so I can prepare with time ahead.

Question:
What formula do I need to create???

 

[Cesar Urquidi]

Do I need to enter all this into the criteria???

Please explain step by step.

--
Thank you,
Cesar Urquidi

Try changing it to:
SELECT [Equipment Calibration].ID, Description, [Equipment
Calibration].[Serial Number], [Equipment Calibration].[Next Calibration
Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Equipment Calibration].[Next Calibration Schedule])
< Date;

--
Dave Hargis, Microsoft Access MVP

Hello,
I get an "Enter Parameter Value" window that says "Next Calibration Schedule".
I don't know what to enter.

I'm not sure if I understood your instruction, please explain.

--
Thank you,
Cesar Urquidi

SELECT ID, Description, [Serial Number], [Next Calibration Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Next Calibration Schedule]) < Date;
--
Dave Hargis, Microsoft Access MVP


:

Hello,
I created an "Equipment Calibration" table, including the following fields:

l_ ID _l_ Description _l_ Serial Number _l_ Next Calibration Schedule
_l

I created a querie for a report that gives me the equipment records that are
past due for calibration, using the following formula in the "Next
Calibration Schedule" field:

<Date( )

Formula means: Any records that are less that today's date, are past due.

Here's the problem:
I want to create a querie for a report that can give me the same records,
but instead of past due, I would like to see the records 30 days before
calibration expires. This is so I can prepare with time ahead.

Question:
What formula do I need to create???

 

[Klatuu]

What do you mean by entering it all in the criteria. It should be the entire
query.

I don't know what you are asking.
--
Dave Hargis, Microsoft Access MVP

Do I need to enter all this into the criteria???

Please explain step by step.

--
Thank you,
Cesar Urquidi

Try changing it to:
SELECT [Equipment Calibration].ID, Description, [Equipment
Calibration].[Serial Number], [Equipment Calibration].[Next Calibration
Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Equipment Calibration].[Next Calibration Schedule])
< Date;

--
Dave Hargis, Microsoft Access MVP

Hello,
I get an "Enter Parameter Value" window that says "Next Calibration Schedule".
I don't know what to enter.

I'm not sure if I understood your instruction, please explain.

--
Thank you,
Cesar Urquidi


:

SELECT ID, Description, [Serial Number], [Next Calibration Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Next Calibration Schedule]) < Date;
--
Dave Hargis, Microsoft Access MVP


:

Hello,
I created an "Equipment Calibration" table, including the following fields:

l_ ID _l_ Description _l_ Serial Number _l_ Next Calibration Schedule
_l

I created a querie for a report that gives me the equipment records that are
past due for calibration, using the following formula in the "Next
Calibration Schedule" field:

<Date( )

Formula means: Any records that are less that today's date, are past due.

Here's the problem:
I want to create a querie for a report that can give me the same records,
but instead of past due, I would like to see the records 30 days before
calibration expires. This is so I can prepare with time ahead.

Question:
What formula do I need to create???

 

[Lord Kelvan]

funny dave you have to remember not all people know sql nor do they
know how to access the sql viewer

enter

between dateadd("d", -30, [Equipment Calibration].[Next Calibration
Schedule]) and date()

as the criteria for

Next Calibration Schedule

and that should give you all records that have a Next Calibration
Schedule expiring in the next 30 days

hope this helps

Regards
Kelvan

 

[Cesar Urquidi]

Hello Kelvan,
It didn't work. It shows the past due records.
Then, I changed it to "30" instead of "-30", and it worked, but it is also
showing records over the 30 days.
I want it to show only the records expiring in the next 30 days.
Please help!

 

[John Spencer]

The criteria
Between Date() and DateAdd("d",30, Date())
should return all records where the date field is between today and 30 days in
the future.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Cesar Urquidi]

Mr. Spencer... It worked!!!
Thank you very much!!!

Dave & Kelvan... Thank you for your time and help too!