Parameter Query not working

[cphelps48]

I maintain a Plant & Equipment Register in Access 2003. This week I have found out that our Fleet department is printing out copious amounts of paper via a Report and then manually sifting out what they do and don't need.



The report in question has a query as its data source. In turn the query gets its data from two tables that are linked via a common field.

One of the columns on the query is a calculated field that has the following formula:

Due Date: ([Date Last Tested]+([Test Frequency])*7)

The logic behind the formula is that a piece of equipment (e.g. a first aid kit in a vehicle) is checked or tested. The date of this is recorded against the vehicle. The item to be checked also has a test frequency assigned to it as a number of weeks.

So the formula calculates the due date by mulitplying the frequency by seven (7) to get the number of days and then add that the Date Last Tested.

In the underlying tables the Date Last Tested is defined as a Date (Short date) with the Test Frequency as a Number (Integer).

I am trying to get the query to prompt the user to enter a date fon the Due Date field. This is intended so that they can run the report and get a print-out for items whose due date falls before the entered in date.

I have placed the following formula into the Criteria settings for the calculated Due Date field.

<=[Enter Date]

Unfortunately it is producing results that do not comply with the requirements. An example of the query being run against First Aid boxes there are a total of 196 in existence. If I enter 31/12/10 as the date I get 195 results of which some of these have due dates that are clearly after the entered date.

With the information provided can anybody give me some guidance on what we might be doing wrong or what is happening here?

[ssanfu]

The report in question has a query as its data source. In turn the query gets its data from two tables that are linked via a common field.

How are the two tables related? 1 to many? Would you post the SQL of the query? And maybe a few examples of [Date Last Tested] & [Test Frequency]?

One of the columns on the query is a calculated field that has the following formula:

Due Date: ([Date Last Tested]+([Test Frequency])*7)

Since the frequency is in weeks, I would use the DateAdd() function. The date due column would be:

DueDate: DateAdd("ww",[Test Frequency],[Date Last Tested])

(Note that using spaces in object names causes headaches.) See
http://access.mvps.org/access/tencommandments.htm ......... Rule #3

In the underlying tables the Date Last Tested is defined as a Date (Short date) with the Test Frequency as a Number (Integer).

Just so we are on the same page, the field is defined as a Date type. The format (how it is displayed) of the field is set to Short date.

I have placed the following formula into the Criteria settings for the calculated Due Date field.

<=[Enter Date]

Unfortunately it is producing results that do not comply with the requirements. An example of the query being run against First Aid boxes there are a total of 196 in existence. If I enter 31/12/10 as the date I get 195 results of which some of these have due dates that are clearly after the entered date.

I created a query with the parameter and it ran without errors when I tested it. But I am using dates in American format (m/d/yyyy).

Have you tried using a control on a form to enter an ending date?

[cphelps48]

Thank you for replying. To address your questions or points........

The two tables are related in a one-to-one relationship. The first table in the query is a Vehicles table that records all of our plant equipment. The second table is the "master" Equipment & Service table that records all transactions relating to the servicing and checks on these vehicles and other equipment. The relationship is set up between the Asset# field in the Vehicle table and the Location field in the Equipment & Service table. This Location field has the vehicle asset# entered into it to define where the item that has been checked is located.

The SQL for this query is as follows:

SELECT tblEquipmentAndService.[Equip ID], tblEquipmentAndService.Location, tblEquipmentAndService.[Asset No], tblEquipmentAndService.[Equip Description], tblEquipmentAndService.[Date Last Tested], tblEquipmentAndService.[Test Frequency], ([Date Last Tested]+([Test Frequency])*7) AS [Due Date], tblEquipmentAndService.[Service Centre], tblEquipmentAndService.Department, tblVehicles.Site, tblVehicles.Operator, tblEquipmentAndService.Decommissioned, tblEquipmentAndService.[Decommissioned date]
FROM tblVehicles INNER JOIN tblEquipmentAndService ON tblVehicles.[Asset No] = tblEquipmentAndService.Location
WHERE (((tblEquipmentAndService.Location) Like [Enter Location]) AND ((tblEquipmentAndService.[Equip Description]) Like "*first aid*") AND ((([Date Last Tested]+([Test Frequency])*7))<=[enter date]) AND ((tblEquipmentAndService.[Service Centre]) Like [Enter Service Centre]) AND ((tblVehicles.Site) Like [Enter Site]) AND ((tblEquipmentAndService.Decommissioned)=No) AND ((tblEquipmentAndService.[Decommissioned date]) Is Null))
ORDER BY tblEquipmentAndService.Location;

The following are some examples of the fields involved in this query.

qryFirstAidKitContentsChecklistEquip DescriptionDate Last TestedTest FrequencyDue DateFirst Aid Kit - B Size29/05/098407/01/11First Aid Kit - B Size19/05/111211/08/11First Aid Kit - B Size27/10/091219/01/10First Aid Kit - B Size29/06/091221/09/09First Aid Kit - B Size01/06/111224/08/11First Aid Kit - B Size01/06/111224/08/11First Aid Kit - B Size01/06/111224/08/11

I too would use the DateAdd function and actually tried modifying the query to use it. However that resulted in my receiving an error message of "This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplying the expression by assigning parts of the expression to variables."

When I remove the <=[Enter Date] entry from the Criteria option on that column this error message goes away.

I also agree about spaces. However this is a database that I have inherited and am looking to take to another platform (eventually). Too much work to do right now unfortunately.

My dates are in Australian format but I would not have thought that would be an issue as long as the dates in the table and the format entered as part of the query were the same.

No, I have not tried using a control on a form for entering the date.

[cphelps48]

This is a mystery to me why this would act differently but I protoyped calendar control on a form that I called "frmSelectDate" to allow them to visually select the required date. I then put a button on the form to run the report. I also changed the query that acts as the Data Source for the report to have a criteria for the calculated Due Date field of <=[forms]![frmSelectDate]![Calendar3].

This worked perfectly!!! Thank you for you help.