Search Queries

  • Thread starter Thread starter Phil Hood
  • Start date Start date
P

Phil Hood

Hello,

I have a form that contains three text box controls for:
Dept
Team
EmpeeID

I wish to construct a query that will pick up values from
these controls and select matching records from a table.

However, I want to set it up so that the query selects the
records whether 1, 2 or 3 of the controls are populated
i.e. select on dept only, team only, empeeID only, dept &
team, dept & empeeID etc......)

I can only make a query work where all three controls are
populated.

Any help would be greatly appreciated..

Thanks

Phil.
 
Phil

If you are working only in a query, and are not considering using a SQL
statement you build dynamically from the fields on the form, with only three
criteria, you should still be able to do it in a query. However, I'd
recommend that you give some thought to using code to dynamically build the
SQL...

How many different combinations of your three fields do you need to deal
with? By my count, you could have three separate "one-field" situations,
plus three separate "two-field" situations, plus one unique "three-field"
combination. One way to do this in a query design grid is to use seven
different criteria rows, one for each valid combination.

To refer to a form's control in a query, use a statement like:

Forms!YourFormName!YourControlName

in the criterion for the field (actual syntax may vary).
 
One way is to build the SQL statement on the fly using VBA.

Another method assumes that your Dept, Team, and EmpeeID fields always have
values and are all text fields.

SELECT Dept, Team, EmpeeID
FROM YourTable
WHERE Dept Like NZ([Forms!FormName!TxtDeptControl,"*")
AND Team Like NZ([Forms!FormName!TxtTeamControl,"*")
AND EmpeeID Like NZ([Forms!FormName!TxtEmpeeIDControl,"*")


Yet another method is outlined below. This will be reorganized by Access when
you save it and will look like a mess. Access reorganizes this to handle all
the different combinations you could have.

1 - All values null
1 - All values filled
3 - Only one value filled
3 - Any two values filled

So you end up with 8 sets of criteria

SELECT Dept, Team, EmpeeID
FROM YourTable
WHERE (Dept = [Forms!FormName!TxtDeptControl
OR [Forms!FormName!TxtDeptControl is Null)
AND (Team = [Forms!FormName!TxtTeamControl
OR [Forms!FormName!TxtTeamControl is Null)
AND (EmpeeID = [Forms!FormName!TxtEmpeeIDControl
OR [Forms!FormName!TxtEmpeeIDControl is Null)
 
Hi,

Thanks very much for your help. I've used your second
option and it works fine (you're right, it does look a
mess when saved!)

I've since added a text control to the form that contains
a date and when I add this to the SQL statement, it
produces odd results when left blank i.e. it extracts all
the records in the underlying table.

Is this because a control with a date cannot be null?

If I put a date in (i.e. don't leave it blank) the right
number of records are extracted.

Any ideas?

Thanks

Phil.
-----Original Message-----
One way is to build the SQL statement on the fly using VBA.

Another method assumes that your Dept, Team, and EmpeeID fields always have
values and are all text fields.

SELECT Dept, Team, EmpeeID
FROM YourTable
WHERE Dept Like NZ([Forms!FormName!TxtDeptControl,"*")
AND Team Like NZ([Forms!FormName!TxtTeamControl,"*")
AND EmpeeID Like NZ([Forms!FormName! TxtEmpeeIDControl,"*")


Yet another method is outlined below. This will be reorganized by Access when
you save it and will look like a mess. Access reorganizes this to handle all
the different combinations you could have.

1 - All values null
1 - All values filled
3 - Only one value filled
3 - Any two values filled

So you end up with 8 sets of criteria

SELECT Dept, Team, EmpeeID
FROM YourTable
WHERE (Dept = [Forms!FormName!TxtDeptControl
OR [Forms!FormName!TxtDeptControl is Null)
AND (Team = [Forms!FormName!TxtTeamControl
OR [Forms!FormName!TxtTeamControl is Null)
AND (EmpeeID = [Forms!FormName!TxtEmpeeIDControl
OR [Forms!FormName!TxtEmpeeIDControl is Null)


Phil said:
Hello,

I have a form that contains three text box controls for:
Dept
Team
EmpeeID

I wish to construct a query that will pick up values from
these controls and select matching records from a table.

However, I want to set it up so that the query selects the
records whether 1, 2 or 3 of the controls are populated
i.e. select on dept only, team only, empeeID only, dept &
team, dept & empeeID etc......)

I can only make a query work where all three controls are
populated.

Any help would be greatly appreciated..

Thanks

Phil.
Click to expand...
.
Click to expand...
 
Well, you now have a much more complex query and to get it to work correctly (in
Access) you are going to have to rebuild the entire query criteria (that is
start over with a COPY of your current query).

You now have the following possibilities
1 - All Null
1 - All filled
4 - One filled
6 - Two filled
3 - Three filled

For a total of fifteen possible sets of criteria; which could get you a "Query
TOO Complex" error from Access.

SELECT Dept, Team, EmpeeID
FROM YourTable
WHERE (Dept = [Forms!FormName!TxtDeptControl
OR [Forms!FormName!TxtDeptControl is Null)
AND (Team = [Forms!FormName!TxtTeamControl
OR [Forms!FormName!TxtTeamControl is Null)
AND (EmpeeID = [Forms!FormName!TxtEmpeeIDControl
OR [Forms!FormName!TxtEmpeeIDControl is Null)
AND (YourDate = [Forms!FormName!TxtYourDateControl
OR [Forms!FormName!TxtYourDateControl is Null)

At this point, you may want to consider building the SQL using VBA.


Phil said:
Hi,

Thanks very much for your help. I've used your second
option and it works fine (you're right, it does look a
mess when saved!)

I've since added a text control to the form that contains
a date and when I add this to the SQL statement, it
produces odd results when left blank i.e. it extracts all
the records in the underlying table.

Is this because a control with a date cannot be null?

If I put a date in (i.e. don't leave it blank) the right
number of records are extracted.

Any ideas?

Thanks

Phil.
-----Original Message-----
One way is to build the SQL statement on the fly using VBA.

Another method assumes that your Dept, Team, and EmpeeID fields always have
values and are all text fields.

SELECT Dept, Team, EmpeeID
FROM YourTable
WHERE Dept Like NZ([Forms!FormName!TxtDeptControl,"*")
AND Team Like NZ([Forms!FormName!TxtTeamControl,"*")
AND EmpeeID Like NZ([Forms!FormName! TxtEmpeeIDControl,"*")


Yet another method is outlined below. This will be reorganized by Access when
you save it and will look like a mess. Access reorganizes this to handle all
the different combinations you could have.

1 - All values null
1 - All values filled
3 - Only one value filled
3 - Any two values filled

So you end up with 8 sets of criteria

SELECT Dept, Team, EmpeeID
FROM YourTable
WHERE (Dept = [Forms!FormName!TxtDeptControl
OR [Forms!FormName!TxtDeptControl is Null)
AND (Team = [Forms!FormName!TxtTeamControl
OR [Forms!FormName!TxtTeamControl is Null)
AND (EmpeeID = [Forms!FormName!TxtEmpeeIDControl
OR [Forms!FormName!TxtEmpeeIDControl is Null)


Phil said:
Hello,

I have a form that contains three text box controls for:
Dept
Team
EmpeeID

I wish to construct a query that will pick up values from
these controls and select matching records from a table.

However, I want to set it up so that the query selects the
records whether 1, 2 or 3 of the controls are populated
i.e. select on dept only, team only, empeeID only, dept &
team, dept & empeeID etc......)

I can only make a query work where all three controls are
populated.

Any help would be greatly appreciated..

Thanks

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