Using 3 combo boxes to search a database

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a database of people around the world that have
taken various classes. I have a query called
SearchDBQuery with fields that include Name, City, State,
Country and ClassName. The state field is not restricted
to US states. For instance, in the United Kingdom, I
have state values as well. However, most state fields for
foreign people are blank but not all.

I have a form called SearchDatabase with three combo
boxes called comboState, comboCountry and comboClass and
a button that runs a report. I want to allow the user to
be able to select one, two or all three values in these
combo boxes which would then bring back the results in
the report. I placed the following in the criteria of my
state field:

Like IIf(IsNull([Forms]![SearchDatabase]!
[ComboState]),"*",[Forms]![SearchDatabase]![ComboState])

When a user selects Georgia it brings back all of the
people who live in Georgia. I also created the following
in the criteria of my country field:

Like IIf(IsNull([Forms]![SearchDatabase]![ComboCountry]),
"*",[Forms]![SearchDatabase]![ComboCountry])

The problem is that when I select a country (ie the
United Kingdom) and leave the state field blank by not
selecting anything with my comboState box I only get
people who do not have a value for the state field in the
UK. What I want is to get everyone in the UK regardless
of if they have a value there or not.

What change do I need to make to my query to make this
happen?

Thanks,

Chuck
..
 
LIKE * won't return rows where the field is Null. Since you're presumably
not storing anything in the State for people in the UK, that's likely the
situation in your case.

Try putting the following in your Criteria box instead:

Like [Forms]![SearchDatabase]![ComboState] Or
IsNull([Forms]![SearchDatabase]![ComboState])
 
Douglas,

Thanks for your help. I seem to still be having problems.
I actually have five combo boxes: State, Country,
UnitID, Class and Preferred Language. I modified your
suggestion by placing your code into the criteria of all
five fields. I want to make it so that a user can select
a value if he wants or not from any of the five. If
there is not value selected then it treats is as if there
was not criteria at all. I tried just selecting people
in Georgia with the SQL below and came up with no results
which I know is not correct. I must be doing something
wrong with the way I have my SQL.

Can you help?

Thanks,

Chuck


SELECT DISTINCT Attendees.First_Name,
Attendees.Last_Name, Attendees.State, Attendees.Country,
Attendees.Unit_ID, Attendees.Preferred_Language
FROM ClassHistory INNER JOIN (Attendees INNER JOIN
Rosters ON Attendees.ID=Rosters.ID) ON
ClassHistory.ClassHistoryID=Rosters.ClassHistoryID
WHERE (((Attendees.State) Like Forms!SearchDatabase1!
ComboState Or (Attendees.State)=IsNull(Forms!
SearchDatabase1!ComboState)) And ((Attendees.Country)
Like Forms!SearchDatabase1!ComboCountry Or
(Attendees.Country)=IsNull(Forms!SearchDatabase1!
ComboCountry)) And ((Attendees.Unit_ID) Like Forms!
SearchDatabase1!ComboUnit Or (Attendees.Unit_ID)=IsNull
(Forms!SearchDatabase1!ComboUnit)) And
((Attendees.Preferred_Language) Like Forms!
SearchDatabase1!ComboLanguage Or
(Attendees.Preferred_Language)=IsNull(Forms!
SearchDatabase1!ComboLanguage)));

-----Original Message-----
LIKE * won't return rows where the field is Null. Since you're presumably
not storing anything in the State for people in the UK, that's likely the
situation in your case.

Try putting the following in your Criteria box instead:

Like [Forms]![SearchDatabase]![ComboState] Or
IsNull([Forms]![SearchDatabase]![ComboState])



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi,

I have a database of people around the world that have
taken various classes. I have a query called
SearchDBQuery with fields that include Name, City, State,
Country and ClassName. The state field is not restricted
to US states. For instance, in the United Kingdom, I
have state values as well. However, most state fields for
foreign people are blank but not all.

I have a form called SearchDatabase with three combo
boxes called comboState, comboCountry and comboClass and
a button that runs a report. I want to allow the user to
be able to select one, two or all three values in these
combo boxes which would then bring back the results in
the report. I placed the following in the criteria of my
state field:

Like IIf(IsNull([Forms]![SearchDatabase]!
[ComboState]),"*",[Forms]![SearchDatabase]! [ComboState])

When a user selects Georgia it brings back all of the
people who live in Georgia. I also created the following
in the criteria of my country field:

Like IIf(IsNull([Forms]![SearchDatabase]! [ComboCountry]),
"*",[Forms]![SearchDatabase]![ComboCountry])

The problem is that when I select a country (ie the
United Kingdom) and leave the state field blank by not
selecting anything with my comboState box I only get
people who do not have a value for the state field in the
UK. What I want is to get everyone in the UK regardless
of if they have a value there or not.

What change do I need to make to my query to make this
happen?

Thanks,

Chuck
.
Click to expand...


.
Click to expand...
 
Try adding some parentheses to ensure that the boolean algebra is done
correctly:

Like (Forms!SearchDatabase1!ComboCountry Or
(Attendees.Country)=IsNull(Forms!SearchDatabase1!
ComboCountry))) And (((Attendees.Unit_ID) Like Forms!
SearchDatabase1!ComboUnit Or (Attendees.Unit_ID)=IsNull
(Forms!SearchDatabase1!ComboUnit))) And
(((Attendees.Preferred_Language) Like Forms!
SearchDatabase1!ComboLanguage Or
(Attendees.Preferred_Language)=IsNull(Forms!
SearchDatabase1!ComboLanguage))));

You're going for (A OR B) AND (C OR D) AND (E OR F)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ChuckW said:
Douglas,

Thanks for your help. I seem to still be having problems.
I actually have five combo boxes: State, Country,
UnitID, Class and Preferred Language. I modified your
suggestion by placing your code into the criteria of all
five fields. I want to make it so that a user can select
a value if he wants or not from any of the five. If
there is not value selected then it treats is as if there
was not criteria at all. I tried just selecting people
in Georgia with the SQL below and came up with no results
which I know is not correct. I must be doing something
wrong with the way I have my SQL.

Can you help?

Thanks,

Chuck


SELECT DISTINCT Attendees.First_Name,
Attendees.Last_Name, Attendees.State, Attendees.Country,
Attendees.Unit_ID, Attendees.Preferred_Language
FROM ClassHistory INNER JOIN (Attendees INNER JOIN
Rosters ON Attendees.ID=Rosters.ID) ON
ClassHistory.ClassHistoryID=Rosters.ClassHistoryID
WHERE (((Attendees.State) Like Forms!SearchDatabase1!
ComboState Or (Attendees.State)=IsNull(Forms!
SearchDatabase1!ComboState)) And ((Attendees.Country)
Like Forms!SearchDatabase1!ComboCountry Or
(Attendees.Country)=IsNull(Forms!SearchDatabase1!
ComboCountry)) And ((Attendees.Unit_ID) Like Forms!
SearchDatabase1!ComboUnit Or (Attendees.Unit_ID)=IsNull
(Forms!SearchDatabase1!ComboUnit)) And
((Attendees.Preferred_Language) Like Forms!
SearchDatabase1!ComboLanguage Or
(Attendees.Preferred_Language)=IsNull(Forms!
SearchDatabase1!ComboLanguage)));

-----Original Message-----
LIKE * won't return rows where the field is Null. Since you're presumably
not storing anything in the State for people in the UK, that's likely the
situation in your case.

Try putting the following in your Criteria box instead:

Like [Forms]![SearchDatabase]![ComboState] Or
IsNull([Forms]![SearchDatabase]![ComboState])



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi,

I have a database of people around the world that have
taken various classes. I have a query called
SearchDBQuery with fields that include Name, City, State,
Country and ClassName. The state field is not restricted
to US states. For instance, in the United Kingdom, I
have state values as well. However, most state fields for
foreign people are blank but not all.

I have a form called SearchDatabase with three combo
boxes called comboState, comboCountry and comboClass and
a button that runs a report. I want to allow the user to
be able to select one, two or all three values in these
combo boxes which would then bring back the results in
the report. I placed the following in the criteria of my
state field:

Like IIf(IsNull([Forms]![SearchDatabase]!
[ComboState]),"*",[Forms]![SearchDatabase]! [ComboState])

When a user selects Georgia it brings back all of the
people who live in Georgia. I also created the following
in the criteria of my country field:

Like IIf(IsNull([Forms]![SearchDatabase]! [ComboCountry]),
"*",[Forms]![SearchDatabase]![ComboCountry])

The problem is that when I select a country (ie the
United Kingdom) and leave the state field blank by not
selecting anything with my comboState box I only get
people who do not have a value for the state field in the
UK. What I want is to get everyone in the UK regardless
of if they have a value there or not.

What change do I need to make to my query to make this
happen?

Thanks,

Chuck
.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top