Need formula for a querie!

  • Thread starter Thread starter Cesar Urquidi
  • Start date Start date
C

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???
 
SELECT ID, Description, [Serial Number], [Next Calibration Schedule]
FROM [Equipment Calibration]
WHERE dateadd("d", -30, [Next Calibration Schedule]) < Date;
 
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


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


Cesar Urquidi said:
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???
Click to expand...
Click to expand...
 
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


Cesar Urquidi said:
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


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


Cesar Urquidi said:
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???
Click to expand...
Click to expand...
Click to expand...
 
Do I need to enter all this into the criteria???

Please explain step by step.

--
Thank you,
Cesar Urquidi


Klatuu said:
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


Cesar Urquidi said:
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


Klatuu said:
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???
Click to expand...
Click to expand...
Click to expand...
 
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


Cesar Urquidi said:
Do I need to enter all this into the criteria???

Please explain step by step.

--
Thank you,
Cesar Urquidi


Klatuu said:
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


Cesar Urquidi said:
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???
Click to expand...
Click to expand...
Click to expand...
 
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
 
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!
 
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
 
Mr. Spencer... It worked!!!
Thank you very much!!!

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