Open Report adding number and date criteria

[Garret]

Hello,

Here is my problem:
This line opens up my report:

DoCmd.OpenReport "rptCalibrationDue", acViewPreview, , _
"tblGages.LastCalib <= #" & dteCalibDue & "#"

dteCalibDue is a date entered by the user through an inputbox.
tblGages.LastCalib is a field that holds a date.

So all records with a LastCalib date that occurred before the date
entered show up. However, there is a field called "Interval" on the
same table. This interval represents a number of months. Gages are
Calibrated every (Interval #) of months. The interval is different for
each Gage.
What I want to do is add that # of months to the field, LastCalib, so
the report will show the records that have a LastCalib + Interval
months that are <= (before or on) the user input date.

I hope this makes sense. I'll try to give an example:

ABC Gage was LastCalibrated 4/1/2006, DEF Gage was LastCalibrated
2/1/2006.
ABC Gage has an Interval of 3 (3 months), DEF has an Interval of 10
months.
The user enters a date in the inbox of 8/1/2006.
ABC WILL be displayed on the report because the total would be
7/1/2006.
DEF WILL NOT be displayed on the report because the total would be
12/1/2006.

I can answer any questions anyone may have. I really appreciate
anyone's attempt to understand this .

 

[Guest]

Garret,

I'm not sure if it will work, but try this:
DoCmd.OpenReport "rptCalibrationDue", acViewPreview, , _
"DateAdd(""m"",Interval,tblGages.LastCalib) <= #" & dteCalibDue & "#"

This way, rather than comparing the LastCalib field to dteCalibDue, you are
using the DateAdd function to add the number of months specified by the
Interval field to LastCalib, and comparing that value to dteCalibDue.

The only thing I'm not sure about is the double quotes around the m. If
that doesn't work, try something like this instead:
DoCmd.OpenReport "rptCalibrationDue", acViewPreview, , _
"DateAdd(" & chr(34) & "m" & chr(34) & ",Interval,tblGages.LastCalib) <= #"
& dteCalibDue & "#"

-Michael

 

[Garret]

Hello Michael, and sorry for the long delay, I was on vacation .

I tried both of these expressions and both times I get the error:
"Datatype mismatch in criteria expression"

I really can't tell you more than this. I don't think the problem has
to do with the "m" and its quotation marks - I think the problem lies
in the fact that I am writing this statement in the Where condition of
the line, and this is a mathematical function going on.

I would store the value in a variable beforehand but then there would
have to be a variable for each record since each record has different
Intervals and LastCalib dates, and so that doesn't make any sense.

Is there any way to do this, or another way to this, maybe involving a
query first?
Thanks for any replys.

 

[Guest]

Garrett,

Sure. Try following these steps:

1. Create a new query containing all the fields you need for your report
(especially make sure the "LastCalib" and "Interval" fields are present).

2. In the next blank field in the query, add the following to the specified
rows (copy & paste my text to avoid errors):
Field row: CalcCalib: DateAdd("m",[Interval],[LastCalib])
Criteria row: <=[EnterDate]

3. Click the "Query" menu, then click "Parameters".
In the first row in the "Parameter" column, type: [EnterDate]
In the first row in the "Data Type" colum, select Date/Time
This will ensure that only a date can be entered as a parameter (an InputBox
does not do this without extra code for error checking)

4. Save the query, and run it to ensure that it works the way you think it
should. Here's how I thought it should work:
For each record, the CalcCalib field should be a certain number of months
ahead of the LastCalib field.
That number of months should be equal to the Interval field.
Every CalcCalib date should be on or before the date entered at the
EnterDate prompt.

5. In your "rptCalibrationDue" report, change the Record Source property to
the name of your newly created query, and save the report.

6. Eliminate your InputBox code, as it is no longer needed (unless it serves
an additional purpose).

7. Since the query is now limited to only the records you want displayed,
open your report without a WhereCondition:
DoCmd.OpenReport "rptCalibrationDue", acViewPreview


If you would like the report to display the "dteCalibDue" date, simply add a
TextBox with the ControlSource set to: =[EnterDate]

If you would like to make the query display all records when no date is
entered at the EnterDate prompt:
In the "CalcCalib" field, in the first "Or" row under the "Criteria" row,
enter this: [EnterDate] Is Null


-Michael

 

[Garret]

Worked great. I had been researching and using the Calculated field of
a query for a little bit and discovered that it was definitely what I
wanted. Calculated fields are so helpful!
Thanks for your help, Mike.

Garrett,

Sure. Try following these steps:

1. Create a new query containing all the fields you need for your report
(especially make sure the "LastCalib" and "Interval" fields are present).

2. In the next blank field in the query, add the following to the specified
rows (copy & paste my text to avoid errors):
Field row: CalcCalib: DateAdd("m",[Interval],[LastCalib])
Criteria row: <=[EnterDate]

3. Click the "Query" menu, then click "Parameters".
In the first row in the "Parameter" column, type: [EnterDate]
In the first row in the "Data Type" colum, select Date/Time
This will ensure that only a date can be entered as a parameter (an InputBox
does not do this without extra code for error checking)

4. Save the query, and run it to ensure that it works the way you think it
should. Here's how I thought it should work:
For each record, the CalcCalib field should be a certain number of months
ahead of the LastCalib field.
That number of months should be equal to the Interval field.
Every CalcCalib date should be on or before the date entered at the
EnterDate prompt.

5. In your "rptCalibrationDue" report, change the Record Source property to
the name of your newly created query, and save the report.

6. Eliminate your InputBox code, as it is no longer needed (unless it serves
an additional purpose).

7. Since the query is now limited to only the records you want displayed,
open your report without a WhereCondition:
DoCmd.OpenReport "rptCalibrationDue", acViewPreview


If you would like the report to display the "dteCalibDue" date, simply add a
TextBox with the ControlSource set to: =[EnterDate]

If you would like to make the query display all records when no date is
entered at the EnterDate prompt:
In the "CalcCalib" field, in the first "Or" row under the "Criteria" row,
enter this: [EnterDate] Is Null


-Michael

Hello Michael, and sorry for the long delay, I was on vacation .

I tried both of these expressions and both times I get the error:
"Datatype mismatch in criteria expression"

I really can't tell you more than this. I don't think the problem has
to do with the "m" and its quotation marks - I think the problem lies
in the fact that I am writing this statement in the Where condition of
the line, and this is a mathematical function going on.

I would store the value in a variable beforehand but then there would
have to be a variable for each record since each record has different
Intervals and LastCalib dates, and so that doesn't make any sense.

Is there any way to do this, or another way to this, maybe involving a
query first?
Thanks for any replys.