adding '00' to a field

[norm]

I have a database that tracks EZ Pass tags, after several years of use we
have adapted a bar code scanner into it, now the scanner adds 2 zeroes in
front of the ez-pass tag number, and the users all these years have not
entered the zeroes in front of the field.
I need add 00 (2 zeroes) in front of my field,

my data base will show a tag# of 811111111 but the scanner will scan
00811111111, so all I need to do is add 2 zeroes to my existing field, I
understand that I have to make a query, create an expression, but I having
trouble comming up with the experssion values

thank you

 

[Ted]

Hey Norm all you have to do is run an update Query.

Something like this...
UPDATE TableName SET TableName.EZPassTag = "00" & [EZPassTag] WHERE
(((TableName.EZPassTag) Like "8*"))

I put the criteria of like 8* so it would only prepend the the 00 to the
EZPass Tags that did not already have the 00s in the beginning.

You can fix the table name and field name and copy and paste the code into
your SQL view of your query and it should run ok.

Good Luck!
Ted

 

[Ron2006]

If you have other than 8* records that need this done then you could
say

WHERE
((len(TableName.EZPassTag) = 9))


Ron

 

[John W. Vinson]

I have a database that tracks EZ Pass tags, after several years of use we
have adapted a bar code scanner into it, now the scanner adds 2 zeroes in
front of the ez-pass tag number, and the users all these years have not
entered the zeroes in front of the field.
I need add 00 (2 zeroes) in front of my field,

my data base will show a tag# of 811111111 but the scanner will scan
00811111111, so all I need to do is add 2 zeroes to my existing field, I
understand that I have to make a query, create an expression, but I having
trouble comming up with the experssion values

thank you

What's the datatype of tyour Tag# field? It should be Text - if it is, the
expression Ted suggests (with Ron's caveat) should work. If it's a Number
field, you should really convert it to text; Long Integers are limited to two
billion odd and don't store leading zeros.

 

[norm]

yes they are stored as text and all the EZ Pass tags I have (about 24,000
entries) strat with the number 8

thanks to everone for the answer!