How do I exclude values from my combo box?!?!?! HELP!!!

[Guest]

Okay, this has been killing me now for four days:

I have two tables, one called tblMain and one called tblPilots. The
structure is as follows:
tblMain
• ReqNumber (primary key)
• RequestBy
• <etc, etc.>
• PilotAssign

tblPilot
• BEMSID (primary key)
• LastName
• FirstName
• <etc, etc>

On one of my forms, I’m trying to create a list of pilots available for
flying, selectable with a combo box.
The pilots are only available if their time isn’t already scheduled for
another flight.
I’ve been able to come up with a list of pilots whose time is occupied using
a “between… and†query (qryPilotNotAvail)
Now comes the part that is killing me: I can’t find a good way to exclude
the pilots from qryNotAvail from the combo box. I tried calling a query and
using the criteria Not Like “*[queries]![qryPilotNotAvail]![BEMSID]*†to
filter out the values I didn’t want (the pilots already scheduled for that
time), but it doesn’t seem to work. Does someone know of a better way to
handle this one?

Incidentally, I also tried putting the results into a list box on a form
(planning on making it not visible later), but the only time the values are
excluded is if one of them are selected. I was thinking of trying to code a
filter statement onto the combo box, using a select “*†from
qryPilotNotAvail, but I have a feeling I’m overcomplicating things somehow….

Any ideas before my eyeballs pop out of my head like Michael Ironside in
Scanners?

 

[Ken Snell \(MVP\)]

Use a query that gets the primary key value of the pilots from
qryPilotNotAvail, and use that as the basis for a NOT IN statement in the
RowSource query for the combo box.

SELECT * FROM Tablename
WHERE PilotPrimaryKeyField NOT IN
(SELECT T.PilotPrimaryKeyField
FROM qryPilotNotAvail AS T);

 

[Ken Snell \(MVP\)]

Or use a LEFT JOIN query:

SELECT T.* FROM Tablename AS T
LEFT JOIN qryPilotNotAvail AS Q
ON T.PilotPrimaryKeyField = Q.PilotPrimaryKeyField
WHERE Q.PilotPrimaryKeyField Is Null;

 

[Guest]

Try something like:

SELECT * from tbl_Pilots
WHERE PILOT_ID NOT IN (SELECT Pilot_ID from qryPilotNotAvail)

HTH
Dale

 

[Guest]

Between yours a Dale's comments, this is the panacea for what ills me. I
knew it was something simple. D'oh!

Thanks to you both!!!

Use a query that gets the primary key value of the pilots from
qryPilotNotAvail, and use that as the basis for a NOT IN statement in the
RowSource query for the combo box.

SELECT * FROM Tablename
WHERE PilotPrimaryKeyField NOT IN
(SELECT T.PilotPrimaryKeyField
FROM qryPilotNotAvail AS T);

--

Ken Snell
<MS ACCESS MVP>

Okay, this has been killing me now for four days:

I have two tables, one called tblMain and one called tblPilots. The
structure is as follows:
tblMain
. ReqNumber (primary key)
. RequestBy
. <etc, etc.>
. PilotAssign

tblPilot
. BEMSID (primary key)
. LastName
. FirstName
. <etc, etc>

On one of my forms, I'm trying to create a list of pilots available for
flying, selectable with a combo box.
The pilots are only available if their time isn't already scheduled for
another flight.
I've been able to come up with a list of pilots whose time is occupied
using
a "between. and" query (qryPilotNotAvail)
Now comes the part that is killing me: I can't find a good way to exclude
the pilots from qryNotAvail from the combo box. I tried calling a query
and
using the criteria Not Like "*[queries]![qryPilotNotAvail]![BEMSID]*" to
filter out the values I didn't want (the pilots already scheduled for that
time), but it doesn't seem to work. Does someone know of a better way to
handle this one?

Incidentally, I also tried putting the results into a list box on a form
(planning on making it not visible later), but the only time the values
are
excluded is if one of them are selected. I was thinking of trying to code
a
filter statement onto the combo box, using a select "*" from
qryPilotNotAvail, but I have a feeling I'm overcomplicating things
somehow..

Any ideas before my eyeballs pop out of my head like Michael Ironside in
Scanners?

 

[Ken Snell \(MVP\)]

Just a performance note -- the LEFT JOIN approach will usually run faster
than the NOT IN approach.

--

Ken Snell
<MS ACCESS MVP>

Or use a LEFT JOIN query:

SELECT T.* FROM Tablename AS T
LEFT JOIN qryPilotNotAvail AS Q
ON T.PilotPrimaryKeyField = Q.PilotPrimaryKeyField
WHERE Q.PilotPrimaryKeyField Is Null;

--

Ken Snell
<MS ACCESS MVP>

Okay, this has been killing me now for four days:

I have two tables, one called tblMain and one called tblPilots. The
structure is as follows:
tblMain
. ReqNumber (primary key)
. RequestBy
. <etc, etc.>
. PilotAssign

tblPilot
. BEMSID (primary key)
. LastName
. FirstName
. <etc, etc>

On one of my forms, I'm trying to create a list of pilots available for
flying, selectable with a combo box.
The pilots are only available if their time isn't already scheduled for
another flight.
I've been able to come up with a list of pilots whose time is occupied
using
a "between. and" query (qryPilotNotAvail)
Now comes the part that is killing me: I can't find a good way to
exclude
the pilots from qryNotAvail from the combo box. I tried calling a query
and
using the criteria Not Like "*[queries]![qryPilotNotAvail]![BEMSID]*" to
filter out the values I didn't want (the pilots already scheduled for
that
time), but it doesn't seem to work. Does someone know of a better way to
handle this one?

Incidentally, I also tried putting the results into a list box on a form
(planning on making it not visible later), but the only time the values
are
excluded is if one of them are selected. I was thinking of trying to
code a
filter statement onto the combo box, using a select "*" from
qryPilotNotAvail, but I have a feeling I'm overcomplicating things
somehow..

Any ideas before my eyeballs pop out of my head like Michael Ironside in
Scanners?