Query Problem - Numbers not Names

  • Thread starter Thread starter Scuda
  • Start date Start date
S

Scuda

Evening all. I have a Select query (code at bottom). My problem is that when
I attempt enter a name for the query (controller) it shows nothing. I believe
this is because the controller lookup I have in the table is a combo box from
another table. So, if I type in thier ControllerID number, it returns the
query. How can I make this work for the name?

Thanks so much, Steph


SELECT tblSENEIncidentLogCY.MISLE_Number,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.DAY,
tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblSENEIncidentLogCY.CONTROLLER, tblSENEIncidentLogCY.LIVESSAVED,
tblSENEIncidentLogCY.LIVESASSISTED, tblSENEIncidentLogCY.[Case Description],
tblSENEIncidentLogCY.ASST_CONT_BI, tblSENEIncidentLogCY.MONTH
FROM tblSENEIncidentLogCY
WHERE (((tblSENEIncidentLogCY.CONTROLLER) Like "*" & [Enter in Controller
Name (partial works)] & "*"));
 
Sounds as though you might have used the Lookup Wizard when creating your
table.

If that's what you've done, while it may look as though there's a name in
Controller field, there really isn't.
 
Evening all. I have a Select query (code at bottom). My problem is that when
I attempt enter a name for the query (controller) it shows nothing. I believe
this is because the controller lookup I have in the table is a combo box from
another table. So, if I type in thier ControllerID number, it returns the
query. How can I make this work for the name?

Thanks so much, Steph


SELECT tblSENEIncidentLogCY.MISLE_Number,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.DAY,
tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblSENEIncidentLogCY.CONTROLLER, tblSENEIncidentLogCY.LIVESSAVED,
tblSENEIncidentLogCY.LIVESASSISTED, tblSENEIncidentLogCY.[Case Description],
tblSENEIncidentLogCY.ASST_CONT_BI, tblSENEIncidentLogCY.MONTH
FROM tblSENEIncidentLogCY
WHERE (((tblSENEIncidentLogCY.CONTROLLER) Like "*" & [Enter in Controller
Name (partial works)] & "*"));
Click to expand...

As Douglas says, you're another victim of Microsoft's misdesigned, misleading,
infuriating Lookup Wizard:

http://www.mvps.org/access/lookupfields.htm

Your table APPEARS to contain a controller name. It doesn't. It contains a
hidden number field; the controller name is stored in the lookup table.

You can still salvage your query by joining the lookup table to it:

SELECT tblSENEIncidentLogCY.MISLE_Number,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.DAY,
tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblControllers.CONTROLLER, tblSENEIncidentLogCY.LIVESSAVED,
tblSENEIncidentLogCY.LIVESASSISTED, tblSENEIncidentLogCY.[Case Description],
tblSENEIncidentLogCY.ASST_CONT_BI, tblSENEIncidentLogCY.MONTH
FROM tblSENEIncidentLogCY INNER JOIN tblControllers
ON tblControllers.ControllerID = tblSENEIncidentLogCY.CONTROLLER
WHERE (((tblControllers.CONTROLLER) Like "*" & [Enter in Controller
Name (partial works)] & "*"));

You'll need to replace tblControllers with the actual name of your lookup
table - view the Lookup properties of the CONTROLLER field in table design
view.
 
Back
Top