Zero-fill existing data

[Guest]

I'm transfering data from an old database that didn't use input masks. The
proper form for one field would be 00-00-0000. I've brought most data up to
00-00-???? but the last four characters have been input as 0 , 00 , 000 , or
0000. I need to zero fill the last four for search capability.
Please help.

 

[John Vinson]

I'm transfering data from an old database that didn't use input masks. The
proper form for one field would be 00-00-0000. I've brought most data up to
00-00-???? but the last four characters have been input as 0 , 00 , 000 , or
0000. I need to zero fill the last four for search capability.
Please help.

An Update query updating the appropriate records (I don't know your
data so I don't know what criteria would be used!) to

Left([textfield] & "0000", 10)

will tack on enough zeros to bring the length of the field up to 10
bytes. Adapt as appropriate...

John W. Vinson[MVP]

 

[Guest]

I'm not being clear about the problem. The data is

01-05-12
01-05-123
01-05-1
01-05-1234

where 01- is the first month of the year,
05- is the year
and the final four the id number.

I want to enter a zero fill to allow a text search of the field.


01-05-0012
01-05-0123
01-05-0001
01-05-1234
I have 30.000 records to change.

Thanks, Jim

I'm transfering data from an old database that didn't use input masks. The
proper form for one field would be 00-00-0000. I've brought most data up to
00-00-???? but the last four characters have been input as 0 , 00 , 000 , or
0000. I need to zero fill the last four for search capability.
Please help.

An Update query updating the appropriate records (I don't know your
data so I don't know what criteria would be used!) to

Left([textfield] & "0000", 10)

will tack on enough zeros to bring the length of the field up to 10
bytes. Adapt as appropriate...

John W. Vinson[MVP]

 

[John Vinson]

I'm not being clear about the problem. The data is

01-05-12
01-05-123
01-05-1
01-05-1234

where 01- is the first month of the year,
05- is the year
and the final four the id number.

I want to enter a zero fill to allow a text search of the field.


01-05-0012
01-05-0123
01-05-0001
01-05-1234
I have 30.000 records to change.

It's text, and the hyphens are actually stored in the field?

If so, and if you have AccessXP or later, update to

Left([fieldname], 6) & Format(Val(Mid([fieldname],
InStrRev([fieldname], "-") + 1)), "0000")

BACK UP YOUR DATABASE first of course...!

John W. Vinson[MVP]

 

[Guest]

The solution works great.

Thanks. Jim

I'm not being clear about the problem. The data is

01-05-12
01-05-123
01-05-1
01-05-1234

where 01- is the first month of the year,
05- is the year
and the final four the id number.

I want to enter a zero fill to allow a text search of the field.


01-05-0012
01-05-0123
01-05-0001
01-05-1234
I have 30.000 records to change.

It's text, and the hyphens are actually stored in the field?

If so, and if you have AccessXP or later, update to

Left([fieldname], 6) & Format(Val(Mid([fieldname],
InStrRev([fieldname], "-") + 1)), "0000")

BACK UP YOUR DATABASE first of course...!

John W. Vinson[MVP]