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

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.

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.

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.

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.

.

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

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.

.