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
|
|||
|
|||
?find rows where penultimate digit in a 9 number code in a column
As part of a research project I am looking at a database of approximately 33
000 people in microsoft access 2003. One of the columns is a 9 digit code (all numbers). I need to select all people (rows) where the penultimate digit in the code is the number 3. I have tried a query with "#######3#" but this didn't work. I would be very grateful for any suggestions. |
#2
|
|||
|
|||
?find rows where penultimate digit in a 9 number code in a column
In the criteria row for your 9 digit column field enter
Like "*3?" "AK" wrote: As part of a research project I am looking at a database of approximately 33 000 people in microsoft access 2003. One of the columns is a 9 digit code (all numbers). I need to select all people (rows) where the penultimate digit in the code is the number 3. I have tried a query with "#######3#" but this didn't work. I would be very grateful for any suggestions. |
#3
|
|||
|
|||
?find rows where penultimate digit in a 9 number code in a column
That will return rows with 3s anywhere in the first eight digits.
You need to use Like "???????3?" unless you're using ADO, in which case you'd use Like "_______3_" (that's 7 underscore characters in a row before the 3, and one underscore character after the 3) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "RonaldoOneNil" wrote in message ... In the criteria row for your 9 digit column field enter Like "*3?" "AK" wrote: As part of a research project I am looking at a database of approximately 33 000 people in microsoft access 2003. One of the columns is a 9 digit code (all numbers). I need to select all people (rows) where the penultimate digit in the code is the number 3. I have tried a query with "#######3#" but this didn't work. I would be very grateful for any suggestions. |
#4
|
|||
|
|||
?find rows where penultimate digit in a 9 number code in a column
Another option would be to put something like below in a field of a query:
The3: Mid([TheFieldName],8,1) Then make the criteria = 3. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "AK" wrote: As part of a research project I am looking at a database of approximately 33 000 people in microsoft access 2003. One of the columns is a 9 digit code (all numbers). I need to select all people (rows) where the penultimate digit in the code is the number 3. I have tried a query with "#######3#" but this didn't work. I would be very grateful for any suggestions. |
#5
|
|||
|
|||
?find rows where penultimate digit in a 9 number code in a col
It does n't. I tested it and 123456789 is not returned and that has a 3 in it.
"Douglas J. Steele" wrote: That will return rows with 3s anywhere in the first eight digits. You need to use Like "???????3?" unless you're using ADO, in which case you'd use Like "_______3_" (that's 7 underscore characters in a row before the 3, and one underscore character after the 3) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "RonaldoOneNil" wrote in message ... In the criteria row for your 9 digit column field enter Like "*3?" "AK" wrote: As part of a research project I am looking at a database of approximately 33 000 people in microsoft access 2003. One of the columns is a 9 digit code (all numbers). I need to select all people (rows) where the penultimate digit in the code is the number 3. I have tried a query with "#######3#" but this didn't work. I would be very grateful for any suggestions. . |
#6
|
|||
|
|||
?find rows where penultimate digit in a 9 number code in a column
Hi -
If the 9-digit number is a character string, and the "3" can be anywhere it it, then instr(numbercode,"3") 0 will work. If your code is numeric, then use instr(format(numbercode),"3") 0 HTH John AK wrote: As part of a research project I am looking at a database of approximately 33 000 people in microsoft access 2003. One of the columns is a 9 digit code (all numbers). I need to select all people (rows) where the penultimate digit in the code is the number 3. I have tried a query with "#######3#" but this didn't work. I would be very grateful for any suggestions. -- John Goddard Ottawa, ON Canada jrgoddard at cyberus dot ca Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200910/1 |
#7
|
|||
|
|||
?find rows where penultimate digit in a 9 number code in a col
My mistake, you're right. The question mark at the end insures that the 3
has to be the second last digit. Sorry about that. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "RonaldoOneNil" wrote in message ... It does n't. I tested it and 123456789 is not returned and that has a 3 in it. "Douglas J. Steele" wrote: That will return rows with 3s anywhere in the first eight digits. You need to use Like "???????3?" unless you're using ADO, in which case you'd use Like "_______3_" (that's 7 underscore characters in a row before the 3, and one underscore character after the 3) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "RonaldoOneNil" wrote in message ... In the criteria row for your 9 digit column field enter Like "*3?" "AK" wrote: As part of a research project I am looking at a database of approximately 33 000 people in microsoft access 2003. One of the columns is a 9 digit code (all numbers). I need to select all people (rows) where the penultimate digit in the code is the number 3. I have tried a query with "#######3#" but this didn't work. I would be very grateful for any suggestions. . |
Thread Tools | |
Display Modes | |
|
|