How To Write Query

[Guest]

FieldX in some records in TblData contains a value while in other records it
is null. How can the query be written which gets the criteria for FieldX from
Forms!FrmAnalyze!XData so that if a value is entered in XData only the
records with that value are returned but if nothing is entered in XData only
the records where FieldX is Null are returned?

Thanks!

 

[Allen Browne]

Switch the query to SQL View (View menu, in Query design), and change the
WHERE clause to something like this:
WHERE (([Forms]![FrmAnalyze]![XData] Is Null) OR
(TblData.FieldX = [Forms]![FrmAnalyze]![XData]))

If the control on the form is null, the first part of that expression is
True for all records, so the query returns everything. If the control is not
null, the expression is true only for the records where the field matches
the value in the control.

BTW, be sure to delcare your paramter, by choosing Parameters on the Query
menu, and entering a row in the dialog to tell JET what tupe of data this
is. For example, if FieldX is a Number field of size Long Integer, you would
put this in the Parameters dialog:
[Forms]![FrmAnalyze]![XData] Long Integer

 

[Guest]

Allen,

Thank you for the quick response!

I do not want to return everything when the control on the form is null. I
just want to return the records where XData is Null. The second part of the
SQL works fine. If the control is not null, the query only returns the
records where the field matches the value in the control.

Switch the query to SQL View (View menu, in Query design), and change the
WHERE clause to something like this:
WHERE (([Forms]![FrmAnalyze]![XData] Is Null) OR
(TblData.FieldX = [Forms]![FrmAnalyze]![XData]))

If the control on the form is null, the first part of that expression is
True for all records, so the query returns everything. If the control is not
null, the expression is true only for the records where the field matches
the value in the control.

BTW, be sure to delcare your paramter, by choosing Parameters on the Query
menu, and entering a row in the dialog to tell JET what tupe of data this
is. For example, if FieldX is a Number field of size Long Integer, you would
put this in the Parameters dialog:
[Forms]![FrmAnalyze]![XData] Long Integer

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

FieldX in some records in TblData contains a value while in other records
it
is null. How can the query be written which gets the criteria for FieldX
from
Forms!FrmAnalyze!XData so that if a value is entered in XData only the
records with that value are returned but if nothing is entered in XData
only
the records where FieldX is Null are returned?

Thanks!

 

[Allen Browne]

Okay, try:

WHERE ((TblData.FieldX Is Null AND [Forms]![FrmAnalyze]![XData] Is Null)
OR (TblData.FieldX = [Forms]![FrmAnalyze]![XData]))

You could even get away with:
WHERE Nz(TblData.FieldX, "") = Nz([Forms]![FrmAnalyze]![XData], "")
but the first one will be much more efficient as it can use any index on
FieldX.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for the quick response!

I do not want to return everything when the control on the form is null. I
just want to return the records where XData is Null. The second part of
the
SQL works fine. If the control is not null, the query only returns the
records where the field matches the value in the control.

Switch the query to SQL View (View menu, in Query design), and change the
WHERE clause to something like this:
WHERE (([Forms]![FrmAnalyze]![XData] Is Null) OR
(TblData.FieldX = [Forms]![FrmAnalyze]![XData]))

If the control on the form is null, the first part of that expression is
True for all records, so the query returns everything. If the control is
not
null, the expression is true only for the records where the field matches
the value in the control.

BTW, be sure to delcare your paramter, by choosing Parameters on the
Query
menu, and entering a row in the dialog to tell JET what tupe of data this
is. For example, if FieldX is a Number field of size Long Integer, you
would
put this in the Parameters dialog:
[Forms]![FrmAnalyze]![XData] Long Integer

FieldX in some records in TblData contains a value while in other
records
it
is null. How can the query be written which gets the criteria for
FieldX
from
Forms!FrmAnalyze!XData so that if a value is entered in XData only the
records with that value are returned but if nothing is entered in XData
only
the records where FieldX is Null are returned?

 

[Guest]

Thanks, Allen!

That did it!!