Query working with number, not name

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

Scuda

Hi All, I hope I can describe this correct.

I have a Select Query, which I am trying to query by Controller. The
Controller field in my table is a lookup from another table.(tblDutyRoster)

The query does not work when I enter a name, but I just found out it works
when I put that persons ControllerID number in.

What am I doing wrong?? Here is my sql for the query:
SELECT tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.IncidentID,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.MONTH,
tblSENEIncidentLogCY.DAY, tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[Case
Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblSENEIncidentLogCY.CONTROLLER
FROM tblSENEIncidentLogCY
WHERE (((tblSENEIncidentLogCY.CONTROLLER) Like "*" & [enter Controller Name
or partial] & "*"));


Thanks in advance!

Steph
 
Scuda said:
I have a Select Query, which I am trying to query by Controller. The
Controller field in my table is a lookup from another table.(tblDutyRoster)

The query does not work when I enter a name, but I just found out it works
when I put that persons ControllerID number in.

What am I doing wrong?? Here is my sql for the query:
SELECT tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.IncidentID,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.MONTH,
tblSENEIncidentLogCY.DAY, tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[Case
Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblSENEIncidentLogCY.CONTROLLER
FROM tblSENEIncidentLogCY
WHERE (((tblSENEIncidentLogCY.CONTROLLER) Like "*" & [enter Controller Name
or partial] & "*"));
Click to expand...


The core of this problem is that you are using a lookup
field. If you change it from a combo box back to a text
box, you would be able to see what's going on. Take a look
at the link in the second commandant at
http://www.mvps.org/access/tencommandments.htm
for a thorough explanation.

Essentially, you need to join the table with the name to the
incedent table so the query has access to the controller
field:

SELECT tblSENEIncidentLogCY.MISLE_Number, . . . ,
C.[Controller name field]
FROM tblSENEIncidentLogCY
INNER JOIN [controllers table] As C
ON tblSENEIncidentLogCY.CONTROLLER = C.PKfield
WHERE C.[Controller name field] Like "*" & [enter Controller
Name or partial] & "*"
 
Thanks so much for the reply Marshall, I will adhere to the guidelines you
provided in the future, no doubt. What is the best option then, if I want to
have a combo box with a list of controllers for example? Would it be to just
configure the combo box on that specific form?

Thanks again.

Marshall Barton said:
Scuda said:
I have a Select Query, which I am trying to query by Controller. The
Controller field in my table is a lookup from another table.(tblDutyRoster)

The query does not work when I enter a name, but I just found out it works
when I put that persons ControllerID number in.

What am I doing wrong?? Here is my sql for the query:
SELECT tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.IncidentID,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.MONTH,
tblSENEIncidentLogCY.DAY, tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[Case
Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblSENEIncidentLogCY.CONTROLLER
FROM tblSENEIncidentLogCY
WHERE (((tblSENEIncidentLogCY.CONTROLLER) Like "*" & [enter Controller Name
or partial] & "*"));
Click to expand...


The core of this problem is that you are using a lookup
field. If you change it from a combo box back to a text
box, you would be able to see what's going on. Take a look
at the link in the second commandant at
http://www.mvps.org/access/tencommandments.htm
for a thorough explanation.

Essentially, you need to join the table with the name to the
incedent table so the query has access to the controller
field:

SELECT tblSENEIncidentLogCY.MISLE_Number, . . . ,
C.[Controller name field]
FROM tblSENEIncidentLogCY
INNER JOIN [controllers table] As C
ON tblSENEIncidentLogCY.CONTROLLER = C.PKfield
WHERE C.[Controller name field] Like "*" & [enter Controller
Name or partial] & "*"
Click to expand...
 
Yes. The form combo box would have the same RowSource,
ColumnCount, etc that you used before. Typically a simple
combo box would have a RowSource like:

SELECT ControllerID, Controller
FROM tblDutyRoster
ORDER BY ControllerID

with ColumnCount=2, BoundColumn=1 and ColumnWidths=0;

Then just bind the combo box's ControlSource to the
ControllerID field in table tblSENEIncidentLogCY (that I
presume is the form's RecordSource.
--
Marsh
MVP [MS Access]

Thanks so much for the reply Marshall, I will adhere to the guidelines you
provided in the future, no doubt. What is the best option then, if I want to
have a combo box with a list of controllers for example? Would it be to just
configure the combo box on that specific form?


Marshall Barton said:
Scuda said:
I have a Select Query, which I am trying to query by Controller. The
Controller field in my table is a lookup from another table.(tblDutyRoster)

The query does not work when I enter a name, but I just found out it works
when I put that persons ControllerID number in.

What am I doing wrong?? Here is my sql for the query:
SELECT tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.IncidentID,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.MONTH,
tblSENEIncidentLogCY.DAY, tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[Case
Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblSENEIncidentLogCY.CONTROLLER
FROM tblSENEIncidentLogCY
WHERE (((tblSENEIncidentLogCY.CONTROLLER) Like "*" & [enter Controller Name
or partial] & "*"));
Click to expand...


The core of this problem is that you are using a lookup
field. If you change it from a combo box back to a text
box, you would be able to see what's going on. Take a look
at the link in the second commandant at
http://www.mvps.org/access/tencommandments.htm
for a thorough explanation.

Essentially, you need to join the table with the name to the
incedent table so the query has access to the controller
field:

SELECT tblSENEIncidentLogCY.MISLE_Number, . . . ,
C.[Controller name field]
FROM tblSENEIncidentLogCY
INNER JOIN [controllers table] As C
ON tblSENEIncidentLogCY.CONTROLLER = C.PKfield
WHERE C.[Controller name field] Like "*" & [enter Controller
Name or partial] & "*"
Click to expand...
Click to expand...
 
Great info, thanks again Marshall.

Marshall Barton said:
Yes. The form combo box would have the same RowSource,
ColumnCount, etc that you used before. Typically a simple
combo box would have a RowSource like:

SELECT ControllerID, Controller
FROM tblDutyRoster
ORDER BY ControllerID

with ColumnCount=2, BoundColumn=1 and ColumnWidths=0;

Then just bind the combo box's ControlSource to the
ControllerID field in table tblSENEIncidentLogCY (that I
presume is the form's RecordSource.
--
Marsh
MVP [MS Access]

Thanks so much for the reply Marshall, I will adhere to the guidelines you
provided in the future, no doubt. What is the best option then, if I want to
have a combo box with a list of controllers for example? Would it be to just
configure the combo box on that specific form?


Marshall Barton said:
Scuda wrote:
I have a Select Query, which I am trying to query by Controller. The
Controller field in my table is a lookup from another table.(tblDutyRoster)

The query does not work when I enter a name, but I just found out it works
when I put that persons ControllerID number in.

What am I doing wrong?? Here is my sql for the query:
SELECT tblSENEIncidentLogCY.MISLE_Number, tblSENEIncidentLogCY.IncidentID,
tblSENEIncidentLogCY.[SENE_CASE_#], tblSENEIncidentLogCY.MONTH,
tblSENEIncidentLogCY.DAY, tblSENEIncidentLogCY.FY, tblSENEIncidentLogCY.[Case
Description], tblSENEIncidentLogCY.[NATURE OF DISTRESS],
tblSENEIncidentLogCY.CONTROLLER
FROM tblSENEIncidentLogCY
WHERE (((tblSENEIncidentLogCY.CONTROLLER) Like "*" & [enter Controller Name
or partial] & "*"));


The core of this problem is that you are using a lookup
field. If you change it from a combo box back to a text
box, you would be able to see what's going on. Take a look
at the link in the second commandant at
http://www.mvps.org/access/tencommandments.htm
for a thorough explanation.

Essentially, you need to join the table with the name to the
incedent table so the query has access to the controller
field:

SELECT tblSENEIncidentLogCY.MISLE_Number, . . . ,
C.[Controller name field]
FROM tblSENEIncidentLogCY
INNER JOIN [controllers table] As C
ON tblSENEIncidentLogCY.CONTROLLER = C.PKfield
WHERE C.[Controller name field] Like "*" & [enter Controller
Name or partial] & "*"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top