Add A Leading Zero

[Guest]

I have an Access 2003 field named EIN, data type text, and field size 8. I
have manually added a leading zero to some of the EIN's. The remaining EIN's
data have 7 digits. I want to add a leading zero only to the EIN's that have
7 digits.

If I use the following expression to update the EIN field will it ignore the
EIN's that have 8 digits or will it delete one digit so that it can add the
leading zero? Hence an EIN with 2 leading zeros. If my expression will not
work, how do I do this?

=RIGHT("00000000" & [EIN], 8)

Thanks for the help,

 

[fredg]

I have an Access 2003 field named EIN, data type text, and field size 8. I
have manually added a leading zero to some of the EIN's. The remaining EIN's
data have 7 digits. I want to add a leading zero only to the EIN's that have
7 digits.

If I use the following expression to update the EIN field will it ignore the
EIN's that have 8 digits or will it delete one digit so that it can add the
leading zero? Hence an EIN with 2 leading zeros. If my expression will not
work, how do I do this?

=RIGHT("00000000" & [EIN], 8)

Thanks for the help,

Create an Update query:

Update YourTable Set YourTable.EIN = "0" & [EIN]
Where Len([EIN]) = 7

 

[John W. Vinson]

If I use the following expression to update the EIN field will it ignore the
EIN's that have 8 digits or will it delete one digit so that it can add the
leading zero? Hence an EIN with 2 leading zeros. If my expression will not
work, how do I do this?

=RIGHT("00000000" & [EIN], 8)

It's safe.

For an 8-digit EIN, 12345678 let's say, the concatenation in the first
argument will return a 16-byte string 00000000012345678. The Right() function
will then extract the rightmost 8 characters, yielding 12345678 (what you
started with).

If EIN contained (say) just "123", you would get an 11-character string
00000000123; extracting the rightmost 8 bytes would give you 00000123.

John W. Vinson [MVP]

 

[Guest]

Fred/John:

Thanks for the responses. I used: =RIGHT("00000000" & [EIN], 8) in the
update query. It worked perfectly.

When I entered =RIGHT("00000000" & [EIN], 8) in the update query, the "="
sign was droped by Access. Why did the update query not need the "=" sign?

Thanks again.
--
Bob

If I use the following expression to update the EIN field will it ignore the
EIN's that have 8 digits or will it delete one digit so that it can add the
leading zero? Hence an EIN with 2 leading zeros. If my expression will not
work, how do I do this?

=RIGHT("00000000" & [EIN], 8)

It's safe.

For an 8-digit EIN, 12345678 let's say, the concatenation in the first
argument will return a 16-byte string 00000000012345678. The Right() function
will then extract the rightmost 8 characters, yielding 12345678 (what you
started with).

If EIN contained (say) just "123", you would get an 11-character string
00000000123; extracting the rightmost 8 bytes would give you 00000123.

John W. Vinson [MVP]

 

[John W. Vinson]

Fred/John:

Thanks for the responses. I used: =RIGHT("00000000" & [EIN], 8) in the
update query. It worked perfectly.

When I entered =RIGHT("00000000" & [EIN], 8) in the update query, the "="
sign was droped by Access. Why did the update query not need the "=" sign?

It's just the syntax. The = is needed in the control source for a textbox, to
distinguish a fieldname (no =) from an expression/calculation (=).

In the Update query the Update To box should just contain whatever it is that
you want the field updated to. If you put in "xyz" it would update every
record that meets the criteria to "xyz" - if you put an expression, it will
update to that expression's value.

John W. Vinson [MVP]