Query Problem - Numbers not Names

[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)] & "*"));

 

[UpRider]

Steph, try adding a third closing parens at the end.

HTH, UpRider

 

[UpRider]

Steph, forget the third parens, that won't work. Sorry I miscounted the
pairs.

UpRider

 

[Douglas J. Steele]

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.

 

[John W. Vinson]

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)] & "*"));

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.