Text Strings

[Tim Syrell]

I have a field called "Client_ID" which is essentially a social security
number (in text format). The data for this field is imported from a
downloaded database (which I cannot modify), and the database truncates any
zeros from the beginning (left side) of the number, i.e., a social of
"001-23-4567" becomes "1234567", "000-12-3456" becomes "123456", etc.,
creating a host of different-length ss numbers. At this point I am not
concerned about whether the hyphens are present or not, as that can be dealt
with via formatting later. The table (which I created) into which I need to
place this truncated data contains a field called "SS" for social security
number (again in text format). My problem is this: how do I code or query
to identify and flag each such imported field that has a length of less than
9 digits (the total length of a correct SS number, net of hyphens), and then
insert the appropriate number of zero's at the left end of the truncated
number, in order to achiece the ultimate 9-digit number I need?

 

[Chutney via AccessMonster.com]

I have exactly the same issue. I resolved it by importing the data from the
mainframe into a staging (temporary) table that has the same structure as my
master table. I then run an append query to move the data from the staging
table to the master table with this in the SSN field:
Exp1: fnSSNZeros([SSN])

The function is:

Function fnSSNZeros(ByVal strSSN As String) As String
'Appends leading zeros to SSN

Select Case Len(strSSN)
Case Is < 7 'no o rinvalid SSN
fnSSNZeros = "0"
Case Is = 7 'SSN is 9-99-9999 or less
fnSSNZeros = "00" & strSSN
Case Is = 8 'SSN is 99-99-9999 or less
fnSSNZeros = "0" & strSSN
Case Else
fnSSNZeros = strSSN
End Select

End Function

If you want hyphens use:

Function fnSSNHyphens(ByVal strSSN As String) As String
'Appends leading zeros to and embeds hyphens in SSN

Select Case Len(strSSN)
Case Is < 7 'no or invalid SSN
fnSSNHyphens = "0"
Case Is = 7 'SSN is 9-99-9999 or less
fnSSNHyphens = "00" & Left(strSSN, 1) & "-" & Mid(strSSN, 2, 2) &
"-" & Right(strSSN, 4)
Case Is = 8 'SSN is 99-99-9999 or less
fnSSNHyphens = "0" & Left(strSSN, 2) & "-" & Mid(strSSN, 3, 2) &
"-" & Right(strSSN, 4)
Case Else
fnSSNHyphens = Left(strSSN, 3) & "-" & Mid(strSSN, 4, 2) & "-" &
Right(strSSN, 4)
End Select

End Function

One question I have is how do I set the function to Null for Case Is < 7?
In general, I do not allow empty strings ("") in my tables so I don't want to
set this case = "" (that is why I use "0"). I would prefer, however, to enter
a Null but have not found a way to do this. Does anyone have a suggestion?

 

[KARL DEWEY]

Try an update query - put this in the Update To field of your grid as shown
in design view.
Right("0000000" & [YourSSNField],9)

 

[Chutney via AccessMonster.com]

Karl's method is the easiest if having a string of zeros for no SSN is
acceptable. However, if you want to have a Null value for no SSN I have
answered my own question by checking the posts on this forum. I changed the
function to a Variant type:

Function fnSSNZeros(ByVal strSSN As String) As Variant
'Appends leading zeros to SSN

Select Case Len(strSSN)
Case Is < 7 'no or invalid SSN
fnSSNZeros = Null
etc.

 

[Tom Ellison]

Dear Tim:

I believe this problem can be solved with a simple, two-step approach.
First, in order to be viewing all the values the same way, you need to
remove the hyphens from any values that have them. Using the REPLACE()
funciton, substitute the empty string for any hyphen.

Having only the digits of the number remaining, there should be only 9
characters remaining. Append to the front of this string a constant string
of 9 zeros. Then take the rightmost 9 digits of what remains.

RIGHT("000000000" & REPLACE(YourColumnName, "-", ""), 9)

I believe this approach should do it.

Please let me know if this helped, and if I can be of any other service.

Tom Ellison

 

[John Spencer]

One more answer, use the format function to display or even to update your data

Format([SomeField],"000000000") will return Null for nulls and 000123456 for 123456

You can even insert your dashes using the format function,

Format([SomeField],"000-00-0000")

 

[Tom Ellison]

Hello John!

These are viable alternatives, of course. You probably must remove the
hyphens from the data in any case.

Tom

One more answer, use the format function to display or even to update your data

Format([SomeField],"000000000") will return Null for nulls and 000123456 for 123456

You can even insert your dashes using the format function,

Format([SomeField],"000-00-0000")

Dear Tim:

I believe this problem can be solved with a simple, two-step approach.
First, in order to be viewing all the values the same way, you need to
remove the hyphens from any values that have them. Using the REPLACE()
funciton, substitute the empty string for any hyphen.

Having only the digits of the number remaining, there should be only 9
characters remaining. Append to the front of this string a constant string
of 9 zeros. Then take the rightmost 9 digits of what remains.

RIGHT("000000000" & REPLACE(YourColumnName, "-", ""), 9)

I believe this approach should do it.

Please let me know if this helped, and if I can be of any other service.

Tom Ellison

truncates
any need
to less
than and
then