Need to fill-in leading zeros


B

Bonnie

Hello! Using A02 in XP. Have a query that finds all SSN's
missing leading zeros. ShortSSN:=Len([SSN]) with <9 in
the criteria line. Would like to 'fix' them without the
user having to key in the missing zeros. I remember
seeing somewhere how to 'fill in' the leading zeros using
format requirements but couldn't locate it anywhere. Can
someone help?

Thanks in advance for any help or advice!
 
Ad

Advertisements

M

[MVP] S.Clark

Using an update query, you can concatenate zeros to the existing string,
then extract nine characters from the right.

Right$("000000000" & ShortSSN,9)

So, if ShortSSN = "1234", it will result in "000001234".

Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting
 
G

george

Hi Bonnie
Think this was result of recent post by another
I use it a lot and it works great
newbie myself so can't clarify
hope it helps


Dim x As Integer
Dim PadLength As Integer


Public Function Lpad(MyValue$, MyPadCharacter$, MyPaddedLength%)
PadLength = MyPaddedLength - Len(MyValue)
Dim PadString As String
For x = 1 To PadLength
PadString = PadString & MyPadCharacter
Next
Lpad = PadString + MyValue

End Function


Then in the update to query type Lpad([fieldname],"0",9)

good luck
George
 
B

Bonnie

Exactamundo! Steve, thank you VERY much. That's the one.
I really appreciate all you MVP's helping like you do.
It's the only way I have ever found to fill the gap
between 'using' DB's and 'utilizing' DB's. Thanks again.
-----Original Message-----
Using an update query, you can concatenate zeros to the existing string,
then extract nine characters from the right.

Right$("000000000" & ShortSSN,9)

So, if ShortSSN = "1234", it will result in "000001234".

Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting


Hello! Using A02 in XP. Have a query that finds all SSN's
missing leading zeros. ShortSSN:=Len([SSN]) with <9 in
the criteria line. Would like to 'fix' them without the
user having to key in the missing zeros. I remember
seeing somewhere how to 'fill in' the leading zeros using
format requirements but couldn't locate it anywhere. Can
someone help?

Thanks in advance for any help or advice!

.
 
B

Bonnie

Thanks for the info but being a relative newbie myself, I
recognize what you put but am not sure where to put it;
in a module or an event procedure. Also wasn't sure what
you refer to when you say "update to query". Could you
clarify both?

Thanks again and I'll check back to see if you respond.
-----Original Message-----
Hi Bonnie
Think this was result of recent post by another
I use it a lot and it works great
newbie myself so can't clarify
hope it helps


Dim x As Integer
Dim PadLength As Integer


Public Function Lpad(MyValue$, MyPadCharacter$, MyPaddedLength%)
PadLength = MyPaddedLength - Len(MyValue)
Dim PadString As String
For x = 1 To PadLength
PadString = PadString & MyPadCharacter
Next
Lpad = PadString + MyValue

End Function


Then in the update to query type Lpad ([fieldname],"0",9)

good luck
George

Hello! Using A02 in XP. Have a query that finds all SSN's
missing leading zeros. ShortSSN:=Len([SSN]) with <9 in
the criteria line. Would like to 'fix' them without the
user having to key in the missing zeros. I remember
seeing somewhere how to 'fill in' the leading zeros using
format requirements but couldn't locate it anywhere. Can
someone help?

Thanks in advance for any help or advice!

.
 
Ad

Advertisements

G

george

Bonnie
I placed it in a module,
should save you doing a query to find SSN's missing zeros as the update
query will Leftpad all records as needed to the value you enter in the query
criteria
Ex: "update query"
only way i can explain is to run through it
1 New query
2 Design View
3 add table containing the ssn field
4 dbl click ssn field in the table to send it to the pane below
!!Starts as Select Query!!
Field: *ssn*
Table: *your table*
Sort:
Show:
Criteria:
or:
when you have Field: SSN and Table: "your table" showing
go to query type and choose update query
at that point the bottom pane of the query changes to
Field: ssn
Table: "your table"
Update To: *"enter the LPad function"*
Criteria:
or:
hope you try this
George

Bonnie said:
Thanks for the info but being a relative newbie myself, I
recognize what you put but am not sure where to put it;
in a module or an event procedure. Also wasn't sure what
you refer to when you say "update to query". Could you
clarify both?

Thanks again and I'll check back to see if you respond.
-----Original Message-----
Hi Bonnie
Think this was result of recent post by another
I use it a lot and it works great
newbie myself so can't clarify
hope it helps


Dim x As Integer
Dim PadLength As Integer


Public Function Lpad(MyValue$, MyPadCharacter$, MyPaddedLength%)
PadLength = MyPaddedLength - Len(MyValue)
Dim PadString As String
For x = 1 To PadLength
PadString = PadString & MyPadCharacter
Next
Lpad = PadString + MyValue

End Function


Then in the update to query type Lpad ([fieldname],"0",9)

good luck
George

Hello! Using A02 in XP. Have a query that finds all SSN's
missing leading zeros. ShortSSN:=Len([SSN]) with <9 in
the criteria line. Would like to 'fix' them without the
user having to key in the missing zeros. I remember
seeing somewhere how to 'fill in' the leading zeros using
format requirements but couldn't locate it anywhere. Can
someone help?

Thanks in advance for any help or advice!

.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Leading Zeroes 2
Leading Zeroes 1
Leading Zero's 1
Leading zeros 3
leading zeros 1
Leading Zeroes 2
Leading Zeros 2
CODE NEEDED -Remove leading zero 2

Top