do a query based on a text box with data or empty text box

[lord]

I am using ms access 2002.

I have a form with 2 ids. First id is departmentID, and the second one is
itemID.

DepartmentID and itemID are completely unique.

At the moment, in order for my subform to show description of the item, I
have to select department, then I will have a list of items in that
department, and once I select that item I get its description. I would like
to make it if departmentID field is empty, I would get the list of all
itemID, so that I could search by itemID only.

 

[lord]

Hi Marshall,

I have added the expression into query, but I get no results if the
parameters were not passed.

Thx.

I am using ms access 2002.

I have a form with 2 ids. First id is departmentID, and the second one is
itemID.

DepartmentID and itemID are completely unique.

At the moment, in order for my subform to show description of the item, I
have to select department, then I will have a list of items in that
department, and once I select that item I get its description. I would like
to make it if departmentID field is empty, I would get the list of all
itemID, so that I could search by itemID only.

Try setting the criteria for the department field to
something like:

=Forms![the form].[department textbox] OR Forms![the
form].[department textbox] Is Null

 

[Marshall Barton]

Please post a CopyPaste of your query's Where clause.
--
Marsh
MVP [MS Access]

I have added the expression into query, but I get no results if the
parameters were not passed.

I am using ms access 2002.

I have a form with 2 ids. First id is departmentID, and the second one is
itemID.

DepartmentID and itemID are completely unique.

At the moment, in order for my subform to show description of the item, I
have to select department, then I will have a list of items in that
department, and once I select that item I get its description. I would like
to make it if departmentID field is empty, I would get the list of all
itemID, so that I could search by itemID only.

Try setting the criteria for the department field to
something like:

=Forms![the form].[department textbox] OR Forms![the
form].[department textbox] Is Null

 

[lord]

Hi, here is the where clause.

WHERE (((dbo_Survey.SurveyID)=[Forms]![AccessForm2]![SurveyID])) OR
((([Forms]![AccessForm2]![SurveyID]) Is Null));

Please post a CopyPaste of your query's Where clause.
--
Marsh
MVP [MS Access]

I have added the expression into query, but I get no results if the
parameters were not passed.

lord wrote:

I am using ms access 2002.

I have a form with 2 ids. First id is departmentID, and the second one is
itemID.

DepartmentID and itemID are completely unique.

At the moment, in order for my subform to show description of the item, I
have to select department, then I will have a list of items in that
department, and once I select that item I get its description. I would like
to make it if departmentID field is empty, I would get the list of all
itemID, so that I could search by itemID only.


Try setting the criteria for the department field to
something like:

=Forms![the form].[department textbox] OR Forms![the
form].[department textbox] Is Null

 

[Marshall Barton]

That's logically consistent so the reason for not getting
any records is because either the SurveyID text box on the
form is not Null when it is "empty" or SurveyID is not the
name of the right text box.

Does it work if the text box has something valid in it?

Why are we talking about SurveyID instead of DepartmentID?
--
Marsh
MVP [MS Access]

WHERE (((dbo_Survey.SurveyID)=[Forms]![AccessForm2]![SurveyID])) OR
((([Forms]![AccessForm2]![SurveyID]) Is Null));

Please post a CopyPaste of your query's Where clause.

I have added the expression into query, but I get no results if the
parameters were not passed.


:
Try setting the criteria for the department field to
something like:

=Forms![the form].[department textbox] OR Forms![the
form].[department textbox] Is Null


lord wrote:
I am using ms access 2002.

I have a form with 2 ids. First id is departmentID, and the second one is
itemID.

DepartmentID and itemID are completely unique.

At the moment, in order for my subform to show description of the item, I
have to select department, then I will have a list of items in that
department, and once I select that item I get its description. I would like
to make it if departmentID field is empty, I would get the list of all
itemID, so that I could search by itemID only.