Inserting a leading 0

  • Thread starter Thread starter Pat Briggs
  • Start date Start date
P

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



Steve said:
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)



Pat Briggs said:
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.
Click to expand...
Click to expand...
 
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

Pat Briggs said:
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]



Steve said:
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)



Pat Briggs said:
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.
Click to expand...
Click to expand...
Click to expand...
 
UPDATE MyTable
SET MyField = Right("0000000" & [MyField], 7)
WHERE Len([MyField]) < 7
 
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.



Gina Whipp said:
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

Pat Briggs said:
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]



Steve said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

Pat Briggs said:
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.



Gina Whipp said:
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

Pat Briggs said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...

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.
 
Back
Top