If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"like" and "*" problem
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? |
#2
|
|||
|
|||
"like" and "*" problem
ghost wrote:
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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
"like" and "*" problem
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" wrote: ghost wrote: 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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
"like" and "*" problem
ghost wrote:
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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
"like" and "*" problem
Thank you Rick . Could you please tell me what is the appropriate operator for numbers? "Rick Brandt" wrote: ghost wrote: 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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
"like" and "*" problem
"ghost" wrote in message ... 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. |
#7
|
|||
|
|||
"like" and "*" problem
"Amy Blankenship" wrote in message
... "ghost" wrote in message ... 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. 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*"; -- Ken Snell MS ACCESS MVP |
Thread Tools | |
Display Modes | |
|
|