Need to fill-in leading zeros

  • Thread starter Thread starter Bonnie
  • Start date Start date
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!
 
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
 
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
 
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!
Click to expand...


.
Click to expand...
 
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!
Click to expand...


.
Click to expand...
 
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!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top