adding prefix - text field

[KarenY]

I have a text field with 33438 records in a table, and I want to add a prefix
of letters "II" infront of about 33300 records.

The name for the field: USERID
Among those 33438 IDs, some records are numbers prefixed with ., J, R, S, C
and NL.
These 33300 records are only numbers without letter prefix yet in the same
text field.

I have tried the IIF statement and UPDATE QRY, yet I couldn't get it worked.

I think it may be an easy one, yet I check the questions and answers in the
Discussions Group and tried myself, I just couldn't get it work,
Appreciate if anybody can help please.
karen

 

[Douglas J. Steele]

UPDATE MyTable SET MyField = "II" & MyField
WHERE Left([MyField], 1) BETWEEN "0" And "1"

 

[KarenY]

Wonderful ! Thanks for your prompt response and brilliant answer ! It works !

UPDATE MyTable SET MyField = "II" & MyField
WHERE Left([MyField], 1) BETWEEN "0" And "1"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a text field with 33438 records in a table, and I want to add a
prefix
of letters "II" infront of about 33300 records.

The name for the field: USERID
Among those 33438 IDs, some records are numbers prefixed with ., J, R, S,
C
and NL.
These 33300 records are only numbers without letter prefix yet in the same
text field.

I have tried the IIF statement and UPDATE QRY, yet I couldn't get it
worked.

I think it may be an easy one, yet I check the questions and answers in
the
Discussions Group and tried myself, I just couldn't get it work,
Appreciate if anybody can help please.
karen

 

[Ken Sheridan]

Karen:

I think Doug probably meant:

WHERE Left([MyField], 1) BETWEEN "0" And "9"

Alternatives would be:

WHERE IsNumeric(Left([MyField], 1))

or:

WHERE Val([Myfield]) > 0

The Val function operates on the first character(s) so if the first is not
numeric it returns zero. If any could be Null add:

AND [Myfield] IS NOT NULL

to the expression.

Ken Sheridan
Stafford, England

 

[John Spencer]

Shouldn't that be
Between "0" and "9"


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

Karen:

That presumably means the ones beginning with a number all started with a 0
or 1. How did Doug know this? Is he psychic? Am I missing something?

Ken Sheridan
Stafford, England

Wonderful ! Thanks for your prompt response and brilliant answer ! It works !

UPDATE MyTable SET MyField = "II" & MyField
WHERE Left([MyField], 1) BETWEEN "0" And "1"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a text field with 33438 records in a table, and I want to add a
prefix
of letters "II" infront of about 33300 records.

The name for the field: USERID
Among those 33438 IDs, some records are numbers prefixed with ., J, R, S,
C
and NL.
These 33300 records are only numbers without letter prefix yet in the same
text field.

I have tried the IIF statement and UPDATE QRY, yet I couldn't get it
worked.

I think it may be an easy one, yet I check the questions and answers in
the
Discussions Group and tried myself, I just couldn't get it work,
Appreciate if anybody can help please.
karen

 

[Douglas J. Steele]

Yeah, you're missing that I had a typo and Karen hopefully caught it.

BTW, you passed the test, Ken... <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Karen:

That presumably means the ones beginning with a number all started with a
0
or 1. How did Doug know this? Is he psychic? Am I missing something?

Ken Sheridan
Stafford, England

Wonderful ! Thanks for your prompt response and brilliant answer ! It
works !

UPDATE MyTable SET MyField = "II" & MyField
WHERE Left([MyField], 1) BETWEEN "0" And "1"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a text field with 33438 records in a table, and I want to add a
prefix
of letters "II" infront of about 33300 records.

The name for the field: USERID
Among those 33438 IDs, some records are numbers prefixed with ., J,
R, S,
C
and NL.
These 33300 records are only numbers without letter prefix yet in the
same
text field.

I have tried the IIF statement and UPDATE QRY, yet I couldn't get it
worked.

I think it may be an easy one, yet I check the questions and answers
in
the
Discussions Group and tried myself, I just couldn't get it work,
Appreciate if anybody can help please.
karen

 

[Ken Sheridan]

You disappoint me, Doug. I was convinced you'd added ESP to your many other
talents!

Ken Sheridan
Stafford, England

Yeah, you're missing that I had a typo and Karen hopefully caught it.

BTW, you passed the test, Ken... <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Karen:

That presumably means the ones beginning with a number all started with a
0
or 1. How did Doug know this? Is he psychic? Am I missing something?

Ken Sheridan
Stafford, England

Wonderful ! Thanks for your prompt response and brilliant answer ! It
works !

:

UPDATE MyTable SET MyField = "II" & MyField
WHERE Left([MyField], 1) BETWEEN "0" And "1"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a text field with 33438 records in a table, and I want to add a
prefix
of letters "II" infront of about 33300 records.

The name for the field: USERID
Among those 33438 IDs, some records are numbers prefixed with ., J,
R, S,
C
and NL.
These 33300 records are only numbers without letter prefix yet in the
same
text field.

I have tried the IIF statement and UPDATE QRY, yet I couldn't get it
worked.

I think it may be an easy one, yet I check the questions and answers
in
the
Discussions Group and tried myself, I just couldn't get it work,
Appreciate if anybody can help please.
karen