"like" and "*" problem

G

ghost

Greeting,
I have a query that has there fields, idnumber, fristname and lastname. In
this query , I put the following criteria by using "like" and "*" operators
under firstname field:
Like [Forms]![Search Form]![Text5] & "*"
The problem is when I run the query and put e.g. "a", the query gives me
just one record which is the first letter of name, while there is more than
one record has the same letter, also the problem with searching for numbers
e.g. "1", one record appears only.
Can any body help me please?
 
R

Rick Brandt

ghost said:
Greeting,
I have a query that has there fields, idnumber, fristname and
lastname. In this query , I put the following criteria by using
"like" and "*" operators under firstname field:
Like [Forms]![Search Form]![Text5] & "*"
The problem is when I run the query and put e.g. "a", the query gives
me just one record which is the first letter of name, while there is
more than one record has the same letter, also the problem with
searching for numbers e.g. "1", one record appears only.
Can any body help me please?
Click to expand...

Post the SQL of your query.
 
G

ghost

The SQL for letters:
Workers.Idnumber, Workers.fristname, Workers.fathername,
Workers.gfathername, Workers.[last name], Workers.[wprker number]
FROM Workers
WHERE (((Workers.fristname) Like [Forms]![Search Form]![txt2] & "*" & "*?"));

And for Numbers:
SELECT Workers.Idnumber, Workers.fristname, Workers.fathername,
Workers.gfathername, Workers.[last name], Workers.[wprker number]
FROM Workers
WHERE (((Workers.[wprker number]) Like [Forms]![Search Form]![Text5] & "*"));



Rick Brandt said:
ghost said:
Greeting,
I have a query that has there fields, idnumber, fristname and
lastname. In this query , I put the following criteria by using
"like" and "*" operators under firstname field:
Like [Forms]![Search Form]![Text5] & "*"
The problem is when I run the query and put e.g. "a", the query gives
me just one record which is the first letter of name, while there is
more than one record has the same letter, also the problem with
searching for numbers e.g. "1", one record appears only.
Can any body help me please?
Click to expand...

Post the SQL of your query.
Click to expand...
 
R

Rick Brandt

ghost said:
The SQL for letters:
Workers.Idnumber, Workers.fristname, Workers.fathername,
Workers.gfathername, Workers.[last name], Workers.[wprker number]
FROM Workers
WHERE (((Workers.fristname) Like [Forms]![Search Form]![txt2] & "*" &
"*?"));

And for Numbers:
SELECT Workers.Idnumber, Workers.fristname, Workers.fathername,
Workers.gfathername, Workers.[last name], Workers.[wprker number]
FROM Workers
WHERE (((Workers.[wprker number]) Like [Forms]![Search Form]![Text5]
& "*"));
Click to expand...

Forgiving the missing "SELECT" in your first example, what is with the second
asterisk and question mark? They are not necessary and might very well be your
problem.

As for the second one you should not look for number patterns based on their
string representation unless that is the only way to do it. Like and wildcards
are for strings only. When you use them for dates or numbers Access will try to
convert the type on the fly, but the results of that coersion might not be what
you are expecting it to be.

If searching in this manner will be common then I would store your worker number
in a text field rather than a numeric field.
 
G

ghost

Thank you Rick .

Could you please tell me what is the appropriate operator for numbers?


Rick Brandt said:
ghost said:
The SQL for letters:
Workers.Idnumber, Workers.fristname, Workers.fathername,
Workers.gfathername, Workers.[last name], Workers.[wprker number]
FROM Workers
WHERE (((Workers.fristname) Like [Forms]![Search Form]![txt2] & "*" &
"*?"));

And for Numbers:
SELECT Workers.Idnumber, Workers.fristname, Workers.fathername,
Workers.gfathername, Workers.[last name], Workers.[wprker number]
FROM Workers
WHERE (((Workers.[wprker number]) Like [Forms]![Search Form]![Text5]
& "*"));
Click to expand...

Forgiving the missing "SELECT" in your first example, what is with the second
asterisk and question mark? They are not necessary and might very well be your
problem.

As for the second one you should not look for number patterns based on their
string representation unless that is the only way to do it. Like and wildcards
are for strings only. When you use them for dates or numbers Access will try to
convert the type on the fly, but the results of that coersion might not be what
you are expecting it to be.

If searching in this manner will be common then I would store your worker number
in a text field rather than a numeric field.
Click to expand...
 
A

Amy Blankenship

ghost said:
Thank you Rick .

Could you please tell me what is the appropriate operator for numbers?
=
<
=
Click to expand...
<=
<>

Numbers aren't "like" other numbers. They are higher, lower, or the same.
 
K

Ken Snell \(MVP\)

Amy Blankenship said:
<=
<>

Numbers aren't "like" other numbers. They are higher, lower, or the same.
Click to expand...

Although, you can use Like to match to number values in a query. This query
will return all records with a numeric value in the field that contains the
digits 1 and 2 in that order:

SELECT *
FROM Tablename
WHERE NumberField Like "*12*";
 
Top