No Results Running Parameter Query on Field Using Lookup Table

[handyman1600]

I have 2 tables:

TestTable:
ID – Autonumber
First Name – Text
Last Name – Text
Added 3 names to Table1

TestTable2:
FullName – Uses Lookup to Table1
SQL View:
SELECT TestTable.ID, TestTable.[First Name] & " " & TestTable.[Last
Name] AS Expr1
FROM TestTable
ORDER BY TestTable.[Last Name];

Query1:

Setup a parameter in query name “Enter name to find” (text)

PARAMETERS [Enter name to find] Text ( 255 );
SELECT TestTable2.[Full Name]
FROM TestTable2
WHERE (((TestTable2.[Full Name]) Like "*" & "Enter name to find" &
"*"));

I get nothing when I run query, either with a valid name or blank.

Is there a problem referencing a Lookup Table in a parameter query?
Is there a workaround? VBA maybe?

Any help will be appreciated.

 

[Lord Kelvan]

try brackets around your Enter name to find it is trying to see that
as text rather than a parameter

like Like "*" & [Enter name to find] & "*"

 

[handyman1600]

try brackets around your Enter name to find it is trying to see that
as text rather than a parameter

like Like "*" & [Enter name to find] & "*"

I entered the brackets and now if I leave the dialog box blank I get
all names but still nothing if I type a valid name.

 

[Lord Kelvan]

try brackets around your Enter name to find it is trying to see that
as text rather than a parameter

like Like "*" & [Enter name to find] & "*"

I entered the brackets and now if I leave the dialog box blank I get
all names but still nothing if I type a valid name.

you didnt enter

like Like "*" & [Enter name to find] & "*"

that was a typo

it shoudl be

Like "*" & [Enter name to find] & "*"

 

[handyman1600]

try brackets around your Enter name to find it is trying to see that
as text rather than a parameter
like Like "*" & [Enter name to find] & "*"

I entered the brackets and now if I leave the dialog box blank I get
all names but still nothing if I type a valid name.

you didnt enter

like Like "*" & [Enter name to find] & "*"

that was a typo

it shoudl be

Like "*" & [Enter name to find] & "*"

Yes I entered the following on the Criteria line of the Parameter
Query:

Like "*" & [Enter name to find] & "*"

Same problem, all if blank and nothing if name entered.

 

[Lord Kelvan]

interesting

this is the code i used to test it

PARAMETERS [Enter name to find] Text ( 255 );
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));

you could try to remove the parameters line and just have

SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));


could you repaste your code cause there may be something small missing

 

[handyman1600]

interesting

this is the code i used to test it

PARAMETERS [Enter name to find] Text ( 255 );
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));

you could try to remove the parameters line and just have

SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));

could you repaste your code cause there may be something small missing

Removed PARAMETER line.

SELECT TestTable2.[Full Name]
FROM TestTable2
WHERE (((TestTable2.[Full Name]) Like "*" & [Enter name to find] &
"*"));

I get the same result as before.

By the way if I reference a table that does not use a Lookup Table it
works OK with this query.

 

[Lord Kelvan]

interesting

this is the code i used to test it

PARAMETERS [Enter name to find] Text ( 255 );
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));

you could try to remove the parameters line and just have

SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));

could you repaste your code cause there may be something small missing

Removed PARAMETER line.

SELECT TestTable2.[Full Name]
FROM TestTable2
WHERE (((TestTable2.[Full Name]) Like "*" & [Enter name to find] &
"*"));

I get the same result as before.

By the way if I reference a table that does not use a Lookup Table it
works OK with this query.- Hide quoted text -

- Show quoted text -

hold up what lookup table and how it is connected

 

[handyman1600]

interesting

this is the code i used to test it

PARAMETERS [Enter name to find] Text ( 255 );
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));

you could try to remove the parameters line and just have

SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));

could you repaste your code cause there may be something small missing

This is another test query and it works.

PARAMETERS [Enter name to find:] Text ( 255 );
SELECT TestTable.[First Name], TestTable.[Last Name], [TestTable]!
[First Name] & " " & [TestTable]![Last Name] AS Expr1
FROM TestTable
WHERE ((([TestTable]![First Name] & " " & [TestTable]![Last Name])
Like "*" & [Enter name to find:] & "*"));

I showing you a simplified version of this problem but the results are
the same if I reference a Lookup Table reference.

 

[handyman1600]

interesting
this is the code i used to test it
PARAMETERS [Enter name to find] Text ( 255 );
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));
you could try to remove the parameters line and just have
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));
could you repaste your code cause there may be something small missing

Removed PARAMETER line.

SELECT TestTable2.[Full Name]
FROM TestTable2
WHERE (((TestTable2.[Full Name]) Like "*" & [Enter name to find] &
"*"));

I get the same result as before.

By the way if I reference a table that does not use a Lookup Table it
works OK with this query.- Hide quoted text -

- Show quoted text -

hold up what lookup table and how it is connected- Hide quoted text -

- Show quoted text -

This is the SQL code in TestTable2 Lookup tab:

SELECT TestTable.ID, TestTable.[First Name] & " " & TestTable.[Last
Name] AS Expr1
FROM TestTable
ORDER BY TestTable.[Last Name];

 

[Lord Kelvan]

can you tell me what the lookup table is and how it is used so i can
test it on my system ebcause i am gettign to problems i added a value
into the table i am testing on as a lookup value to another table but
i am still getting no problem in all intensive purposes it should work

 

[handyman1600]

On Aug 7, 11:08 am, (e-mail address removed) wrote:

interesting
this is the code i used to test it
PARAMETERS [Enter name to find] Text ( 255 );
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));
you could try to remove the parameters line and just have
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));
could you repaste your code cause there may be something small missing
Removed PARAMETER line.
SELECT TestTable2.[Full Name]
FROM TestTable2
WHERE (((TestTable2.[Full Name]) Like "*" & [Enter name to find] &
"*"));
I get the same result as before.
By the way if I reference a table that does not use a Lookup Table it
works OK with this query.- Hide quoted text -
- Show quoted text -

hold up what lookup table and how it is connected- Hide quoted text -

- Show quoted text -

This is the SQL code in TestTable2 Lookup tab:

SELECT TestTable.ID, TestTable.[First Name] & " " & TestTable.[Last
Name] AS Expr1
FROM TestTable
ORDER BY TestTable.[Last Name];- Hide quoted text -

- Show quoted text -

Field Properties for TestTable2

Display Control - Combo Box
Bound Column - 1
Column Count - 2
Column Heads - No
Column Widths - 0";1"
List Rows - 15
List Width - 1"
Limit to List - Yes

 

[handyman1600]

On Aug 7, 11:08 am, (e-mail address removed) wrote:
interesting
this is the code i used to test it
PARAMETERS [Enter name to find] Text ( 255 );
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));
you could try to remove the parameters line and just have
SELECT STUDENTS.firstname
FROM STUDENTS
WHERE (((STUDENTS.firstname) Like "*" & [Enter name to find] & "*"));
could you repaste your code cause there may be something small missing
Removed PARAMETER line.
SELECT TestTable2.[Full Name]
FROM TestTable2
WHERE (((TestTable2.[Full Name]) Like "*" & [Enter name to find] &
"*"));
I get the same result as before.
By the way if I reference a table that does not use a Lookup Table it
works OK with this query.- Hide quoted text -
- Show quoted text -
hold up what lookup table and how it is connected- Hide quoted text -
- Show quoted text -

This is the SQL code in TestTable2 Lookup tab:

SELECT TestTable.ID, TestTable.[First Name] & " " & TestTable.[Last
Name] AS Expr1
FROM TestTable
ORDER BY TestTable.[Last Name];- Hide quoted text -

- Show quoted text -

Field Properties for TestTable2

Display Control - Combo Box
Bound Column - 1
Column Count - 2
Column Heads - No
Column Widths - 0";1"
List Rows - 15
List Width - 1"
Limit to List - Yes- Hide quoted text -

- Show quoted text -

You should Lookup a combination of two fields like I am doing (First
Name + Last Name), not one. It may work for me if I only do a single
field Lookup. I'll try it.

 

[Lord Kelvan]

i am sorry i didnt understand this was in a form if you run the query
outside the form does it work

 

[handyman1600]

i am sorry i didnt understand this was in a form if you run the query
outside the form does it work

The query was created in the TestTable2 Lookup properties tab.

Also I tried referencing just one field and I get the same problem
referenceing the Lookup.

I think there is a problem with Lookup tables in Access 2002.

 

[Lord Kelvan]

well i just tested it and it seemed to work and i am using access 2002


display control: Combo Box
Row Source Type: Table/Query
Row Source : SELECT students.firstname, students.lastname, students!
FirstName & " " & students!LastName AS Expr1 FROM students WHERE
(((students!FirstName & " " & students!LastName) Like "*" & [Enter
name to find:] & "*"));
bound coloumn: 1
column count: 3
column head: No
column widths: 1cm;1cm;1cm
listrows: 8
list width: auto
limit to list: yes

that is what i used your problem may be deaper but since i havent used
this functionality before as i dont use lookup lists i use forms to do
it for me i cannot help you it may be a setting you have enablem or
disabled preventing this but i am not sure someoen else may be able to
help you

sorry that i wasnt more help

Regards
Kelvan

 

[handyman1600]

well i just tested it and it seemed to work and i am using access 2002

display control: Combo Box
Row Source Type: Table/Query
Row Source : SELECT students.firstname, students.lastname, students!
FirstName & " " & students!LastName AS Expr1 FROM students WHERE
(((students!FirstName & " " & students!LastName) Like "*" & [Enter
name to find:] & "*"));
bound coloumn: 1
column count: 3
column head: No
column widths: 1cm;1cm;1cm
listrows: 8
list width: auto
limit to list: yes

that is what i used your problem may be deaper but since i havent used
this functionality before as i dont use lookup lists i use forms to do
it for me i cannot help you it may be a setting you have enablem or
disabled preventing this but i am not sure someoen else may be able to
help you

sorry that i wasnt more help

Regards
Kelvan

Thanks anyway. I'll get plugging away and see it I can crack this
one.

Ron

 

[John Spencer]

One possibility is that you are using the ANSI-Compliant SQL setting.
Try using "%" in place of "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[handyman1600]

One possibility is that you are using the ANSI-Compliant SQL setting.
   Try using "%" in place of "*"

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================





- Show quoted text -

I tried the "%" but no luck exactly the same problem. By the way once
I get this working I want to add text boxes to an existing form that
links to the parameter query. I have two fields that work fine
already but these fields don't have a lookup table to reference but I
got to get the basic parameter query to work first.

 

[handyman1600]

I tried the "%" but no luck exactly the same problem.  By the way once
I get this working I want to add text boxes to an existing form that
links to the parameter query.  I have two fields that work fine
already but these fields don't have a lookup table to reference but I
got to get the basic parameter query to work first.- Hide quoted text -

- Show quoted text -

BINGO!!!! WE HAVE A WINNER!

Rather than writing the Table queries "inside" the Lookup Properties
window of the table, I wrote stand alone queries and simply entered
the query name on the Lookup Row Source screen and now everything
works! Why it works I'm not sure but it works. The only difference
being that the queries are external from the table Lookup. Strange
but true.