Inserting a leading 0

[Pat Briggs]

I imported a spreadsheet that was set-up incorrectly using the '0123456 to
keep the leading 0's in and didn't realize it. Now I have thousands of
records that need the leading 0 put back in. Is there a way to write an
update query to do them or am I stuck adding the 0 in one at a time? I tried
formating the table but it didn't change anything.

 

[Pat Briggs]

Steve, it works for the select query but when I change it to an update query
I get the following "0" & [Empl_ID] is not a valid name. Make sure that is
does not include invalid characters or punctuation and that it is not too
long. Maybe I'm a little confused about the Update To section as well.
Shouldn't that be the field I am trying to update? "Empl_Id"? I tried
putting in the Table as well, but got the same error. I really appreciate
your help as I don't want to have to do these by hand.

This is how my query looks: Expr1: "0" & [Empl_ID]

Any data starting with 0 is text. There are no numbers starting with 0. So
first make sure the data type of your field is text. Now create a query that
contains just that one field. Change to an Update query. Assuming the field
name is MyTextField, put the following expression in where it says Update
To:
"0" & [MyTextField]

Steve
(e-mail address removed)

I imported a spreadsheet that was set-up incorrectly using the '0123456 to
keep the leading 0's in and didn't realize it. Now I have thousands of
records that need the leading 0 put back in. Is there a way to write an
update query to do them or am I stuck adding the 0 in one at a time? I
tried
formating the table but it didn't change anything.

 

[Gina Whipp]

Pat,

In the query grid window...

Field: Field you are trying update
Table: Table where field is located
Update To: "0" & [Empl_ID]
Criteria:
Or:

The table that holds Empl_Id aslo has to be in the query grid window linked
to the table where the field to updated is.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Steve, it works for the select query but when I change it to an update
query
I get the following "0" & [Empl_ID] is not a valid name. Make sure that
is
does not include invalid characters or punctuation and that it is not too
long. Maybe I'm a little confused about the Update To section as well.
Shouldn't that be the field I am trying to update? "Empl_Id"? I tried
putting in the Table as well, but got the same error. I really appreciate
your help as I don't want to have to do these by hand.

This is how my query looks: Expr1: "0" & [Empl_ID]

Any data starting with 0 is text. There are no numbers starting with 0.
So
first make sure the data type of your field is text. Now create a query
that
contains just that one field. Change to an Update query. Assuming the
field
name is MyTextField, put the following expression in where it says Update
To:
"0" & [MyTextField]

Steve
(e-mail address removed)

I imported a spreadsheet that was set-up incorrectly using the '0123456
to
keep the leading 0's in and didn't realize it. Now I have thousands of
records that need the leading 0 put back in. Is there a way to write
an
update query to do them or am I stuck adding the 0 in one at a time? I
tried
formating the table but it didn't change anything.

 

[Douglas J. Steele]

UPDATE MyTable
SET MyField = Right("0000000" & [MyField], 7)
WHERE Len([MyField]) < 7

 

[Pat Briggs]

Gina,

Unfortunately this doesn't work because some of the cells already have a
leading 0 and I don't want to add an additional 0. Originally I was trying
to use an expression, Format(trim(str(format(
[OFF].[Empl_ID],"MMYY"))),"0000"), but I wasn't sure what to change the MMYY
to. I tried 0000000 in both spots, the MMYY and the 0000 and the select
query worked, but not the update query.

Pat,

In the query grid window...

Field: Field you are trying update
Table: Table where field is located
Update To: "0" & [Empl_ID]
Criteria:
Or:

The table that holds Empl_Id aslo has to be in the query grid window linked
to the table where the field to updated is.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Steve, it works for the select query but when I change it to an update
query
I get the following "0" & [Empl_ID] is not a valid name. Make sure that
is
does not include invalid characters or punctuation and that it is not too
long. Maybe I'm a little confused about the Update To section as well.
Shouldn't that be the field I am trying to update? "Empl_Id"? I tried
putting in the Table as well, but got the same error. I really appreciate
your help as I don't want to have to do these by hand.

This is how my query looks: Expr1: "0" & [Empl_ID]

Any data starting with 0 is text. There are no numbers starting with 0.
So
first make sure the data type of your field is text. Now create a query
that
contains just that one field. Change to an Update query. Assuming the
field
name is MyTextField, put the following expression in where it says Update
To:
"0" & [MyTextField]

Steve
(e-mail address removed)



I imported a spreadsheet that was set-up incorrectly using the '0123456
to
keep the leading 0's in and didn't realize it. Now I have thousands of
records that need the leading 0 put back in. Is there a way to write
an
update query to do them or am I stuck adding the 0 in one at a time? I
tried
formating the table but it didn't change anything.

 

[Gina Whipp]

Pat,

Just need to modify the Update query... How many spaces are the ones
without the zero and how many are the ones with the zero?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

Unfortunately this doesn't work because some of the cells already have a
leading 0 and I don't want to add an additional 0. Originally I was
trying
to use an expression, Format(trim(str(format(
[OFF].[Empl_ID],"MMYY"))),"0000"), but I wasn't sure what to change the
MMYY
to. I tried 0000000 in both spots, the MMYY and the 0000 and the select
query worked, but not the update query.

Pat,

In the query grid window...

Field: Field you are trying update
Table: Table where field is located
Update To: "0" & [Empl_ID]
Criteria:
Or:

The table that holds Empl_Id aslo has to be in the query grid window
linked
to the table where the field to updated is.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Steve, it works for the select query but when I change it to an update
query
I get the following "0" & [Empl_ID] is not a valid name. Make sure
that
is
does not include invalid characters or punctuation and that it is not
too
long. Maybe I'm a little confused about the Update To section as
well.
Shouldn't that be the field I am trying to update? "Empl_Id"? I tried
putting in the Table as well, but got the same error. I really
appreciate
your help as I don't want to have to do these by hand.

This is how my query looks: Expr1: "0" & [Empl_ID]



:

Any data starting with 0 is text. There are no numbers starting with
0.
So
first make sure the data type of your field is text. Now create a
query
that
contains just that one field. Change to an Update query. Assuming the
field
name is MyTextField, put the following expression in where it says
Update
To:
"0" & [MyTextField]

Steve
(e-mail address removed)



I imported a spreadsheet that was set-up incorrectly using the
'0123456
to
keep the leading 0's in and didn't realize it. Now I have thousands
of
records that need the leading 0 put back in. Is there a way to
write
an
update query to do them or am I stuck adding the 0 in one at a time?
I
tried
formating the table but it didn't change anything.

 

[John W. Vinson]

Gina,

Unfortunately this doesn't work because some of the cells already have a
leading 0 and I don't want to add an additional 0. Originally I was trying
to use an expression, Format(trim(str(format(
[OFF].[Empl_ID],"MMYY"))),"0000"), but I wasn't sure what to change the MMYY
to. I tried 0000000 in both spots, the MMYY and the 0000 and the select
query worked, but not the update query.

Not sure why tne deeply nested function calls... is EmplID a date!? If not,
the MMYY format is erroneous.

If you want to update a Text datatype field named Empl_ID to have exactly four
digits with leading zeros, use an Update query with an Update To clause of

Right("0000" & [Empl_ID], 4)

Use a criterion of

NOT LIKE "0*"

on the field to prevent wastefully updating fields that already have the
leading zeros.