"like" and "*" problem

[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?

 

[Rick Brandt]

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?

Post the SQL of your query.

 

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

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?

Post the SQL of your query.

 

[Rick Brandt]

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

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.

 

[ghost]

Thank you Rick .

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

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

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.

 

[Amy Blankenship]

Thank you Rick .

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

<=
<>

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

 

[Ken Snell \(MVP\)]

<=
<>

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

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*";