Using a combo box as a criteria for a query

[neetkleat]

I have 2 tables:

Table 1 = Position Names
PositionID - Data Type = AutoNumber
Position Name - Data Type = Text

Table 2 = Staff Positions
StaffID - Data Type = AutoNumber
Last Name - Data Type = Text
First Name - Data Type = Text
Position Name - Data Type = Number, Display Control = Combo Box, Row Source Type = Table/Query, Row Source = SELECT [Position Names].PositionID, [Position Names].[Position Name] FROM [Position Names] ORDER BY [Position Name];

I am trying to create a query that will show the Last Name and First Name of all staff who hold the position "General Staff".

Unfortunately when I try to use the combo box Position Name from the Staff Positions table, it says there is a Data type mismatch, since under criteria, I wrote in "General Staff".

I understand that it is a number since it is a combo box, but how do I get it to query the Staff Positions table?

(Please let me know if you need any more details, I know that wasn't the most clear description)

 

[HiTechCoach]

I have 2 tables:

Table 1 = Position Names
PositionID - Data Type = AutoNumber
Position Name - Data Type = Text

Table 2 = Staff Positions
StaffID - Data Type = AutoNumber
Last Name - Data Type = Text
First Name - Data Type = Text
Position Name - Data Type = Number, Display Control = Combo Box, Row Source Type = Table/Query, Row Source = SELECT [Position Names].PositionID, [Position Names].[Position Name] FROM [Position Names] ORDER BY [Position Name];

I am trying to create a query that will show the Last Name and First Name of all staff who hold the position "General Staff".

Unfortunately when I try to use the combo box Position Name from the Staff Positions table, it says there is a Data type mismatch, since under criteria, I wrote in "General Staff".

I understand that it is a number since it is a combo box, but how do I get it to query the Staff Positions table?

(Please let me know if you need any more details, I know that wasn't the most clear description)

First read:

The Evils of Lookup Fields in Tables

In your staff position table, you should be storing the Posting ID not the position name.

I would urge you to name your fields in the table to reflect this. Something like:

Table 2 = Staff Positions
StaffID - Data Type = AutoNumber
Last Name - Data Type = Text
First Name - Data Type = Text
Position ID - Data Type = Number - Long

In you query to select the staff position for a specific position, I wou;d usew the Position ID, not the Name to filter the records.


If I were doing this, I would use three table liek this:

Table: Positions
PositionID - AutoNumber
PositionName = Text

Table: People
PeopleID - AutoNumber
LastName - Text
FirstName - Text
... other feilds that describe the person


Table: PeoplePositons

PeoplePositonsID - AutoNumber
PeopleID - Data Type = Number - Long
PositionID - Data Type = Number - Long
StartDate - Date/Time
EndDate - Date/Time    - Null => Current position

Note: No s[pace in field names!