help with query for report

  • Thread starter Thread starter tw
  • Start date Start date
T

tw

I have a query that I run with criteria from fields in an unbound form.
When I run the query with the form closed, it asked for the data that would
come from the fields in the form, I enter the data, and the query works
fine. When I run the query with the form open with the same data entered
into the fields, the query does not ask for the data, but it also does not
find any records. The data in the form is a from and to date, the format of
the field is short date. What is wrong with the query?

thanks for any help

copied from sql view below

SELECT Clients.[CL-PK Client ID], Clients.[CL Last SV Visit], [CC PCS
Authorized Units].[AU-FK Proc ID], Frequency.Frequency, Frequency.[Interval
in Months], DateAdd("m",[Interval in Months],[CL Last SV Visit]) AS
NextVisit, [CC PCS Authorized Units].[AU From Date], [CC PCS Authorized
Units].[AU To Date]
FROM ([CC PCS Authorized Units] INNER JOIN Clients ON [CC PCS Authorized
Units].[AU-FK Client ID] = Clients.[CL-PK Client ID]) INNER JOIN Frequency
ON [CC PCS Authorized Units].[AU-FK Frequency Code] = Frequency.[FREQ-PK
Frequency Code]
WHERE ((([CC PCS Authorized Units].[AU-FK Proc ID])="T1001") AND
((DateAdd("m",[Interval in Months],[CL Last SV Visit]))<=[au to date]) AND
(([CC PCS Authorized Units].[AU From Date]) Between
[forms]![frmqryparametersfromto]![txtFromDate] And
[forms]![frmqryparametersfromto]![txToDate])) OR ((([CC PCS Authorized
Units].[AU-FK Proc ID])="T1001") AND ((DateAdd("m",[Interval in Months],[CL
Last SV Visit]))<=[au to date]) AND (([CC PCS Authorized Units].[AU To
Date]) Between [forms]![frmqryparametersfromto]![txtFromDate] And
[forms]![frmqryparametersfromto]![txToDate])) OR ((([CC PCS Authorized
Units].[AU-FK Proc ID])="G9001") AND ((DateAdd("m",[Interval in Months],[CL
Last SV Visit]))<=[au to date]) AND (([CC PCS Authorized Units].[AU From
Date]) Between [forms]![frmqryparametersfromto]![txtFromDate] And
[forms]![frmqryparametersfromto]![txToDate])) OR ((([CC PCS Authorized
Units].[AU-FK Proc ID])="G9001") AND ((DateAdd("m",[Interval in Months],[CL
Last SV Visit]))<=[au to date]) AND (([CC PCS Authorized Units].[AU To
Date]) Between [forms]![frmqryparametersfromto]![txtFromDate] And
[forms]![frmqryparametersfromto]![txToDate]))
ORDER BY DateAdd("m",[Interval in Months],[CL Last SV Visit]);
 
Tw,

The first thing to check is that the txtFromDate and txtToDate textboxes
on the form have their Format property set to a date format. It doesn't
matter which format, as long as it's a date, and the format of the field
in the table is also irrelevant, as this only affects the appearance of
the display of the data, not its value.
 
Back
Top