query problem with unbound form

[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]);

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the PARAMETERS clause. By using the Parameters clause you define
the data type of the parameters, which converts the values into the
correct data type (if the value is a valid expression of that data
type).

PARAMETERS [forms]![frmqryparametersfromto]![txtFromDate] Date,
[forms]![frmqryparametersfromto]![txToDate] Date;

SELECT C.[CL-PK Client ID],
C.[CL Last SV Visit],
AU.[AU-FK Proc ID],
F.Frequency,
F.[Interval in Months],
DateAdd("m",F.[Interval in Months],
C.[CL Last SV Visit]) AS NextVisit,
AU.[AU From Date],
AU.[AU To Date]

FROM ([CC PCS Authorized Units] As AU
INNER JOIN C As Clients C
ON AU.[AU-FK Client ID] = C.[CL-PK Client ID])
INNER JOIN Frequency As F
ON AU.[AU-FK Frequency Code] = F.[FREQ-PK Frequency Code]

WHERE AU.[AU-FK Proc ID] IN ("T1001", "G9001")
AND DateAdd("m", F.[Interval in Months], C.[CL Last SV Visit])
<= AU.[au to date]
AND AU.[AU From Date]
Between [forms]![frmqryparametersfromto]![txtFromDate]
And [forms]![frmqryparametersfromto]![txToDate]

ORDER BY DateAdd("m", F.[Interval in Months], c.[CL Last SV Visit]);

I got rid of the redundant criteria in the WHERE clause, Access QBE
likes to do that (make redundant criteria).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlWSgoechKqOuFEgEQIsewCfY3BkMemwgDhYQB5v+G25iKBZG08An0JY
GS0BKXfbIHms9+A+xB9QsV4v
=2hxR
-----END PGP SIGNATURE-----

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]);

 

[[MVP] S.Clark]

Sometimes... parameters that you want to be a date get misinterpreted by
Access as some other datatype. So, in the query, open the Parameters dialog
box and define the date parameters to be of type date.

Query / Parameters from the Query menu.

 

[tw]

thanks

Sometimes... parameters that you want to be a date get misinterpreted by
Access as some other datatype. So, in the query, open the Parameters
dialog box and define the date parameters to be of type date.

Query / Parameters from the Query menu.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

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]);