using query-by-form to callect parms for secondary form

[Guest]

I have a form that has a combo box of valid dates. the user selects a data,
then clicks on a button, which opens another form in add mode. the new form
has a combo box that is populated by selecting data from a table. The date
selected on the first form is used in the "where" clause of the select
statement. The statement (below) has 2 elements in the "where" clause, the
one associated with the field CACRProdLine is working, the one using the
QBF_FY field is not.

"SELECT Project.ProjectName, Project.ProjectWBSID FROM Project WHERE
(((Project.ProjectFY)=forms![qbf-add-CACR-form-f]!QBF_FY) And
((Project.ProjectDirectorate)=forms!AddCACRForm!CACRPRodLine)) Or
(((Project.ProjectDirectorate)=forms!AddCACRForm!CACRPRodLine) And
((forms![qbf-add-CACR-form-f]!QBF_FY) Is Null)) ORDER BY Project.ProjectName;

Any suggestions would be appreciated.
Thax Dan

 

[Allen Browne]

Dan if you want to return all reccords when the date field is left blank,
try something like this:

PARAMETERS [forms]![qbf-add-CACR-form-f]![QBF_FY] DateTime
[forms]![AddCACRForm]![CACRPRodLine] DateTime;
SELECT Project.ProjectName, Project.ProjectWBSID
FROM Project
WHERE ((Project.ProjectFY = [forms]![qbf-add-CACR-form-f]![QBF_FY])
OR ([forms]![qbf-add-CACR-form-f]![QBF_FY] Is Null))
AND ((Project.ProjectDirectorate = [forms]![AddCACRForm]![CACRPRodLine])
OR ([forms]![AddCACRForm]![CACRPRodLine] Is Null))
ORDER BY Project.ProjectName;

The statement handles the AND/OR combination differently than yours, so I
hope that's what you intended.

It also declares the parameters (Parameters on Query menu, in query design),
so that Access understands the intended data type. If they are not both
dates, then change the data type (if it is a Number field), or remove the
parameter declaration (if it is a Text field.)

For an explanation of why you want to declare your parameters, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
For an explanation of why you don't want to declare parameters for Text type
fields, see:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

Please post back if the query is still not giving the result you want.