Right Function in a Report

[yoly1972]

I need to format a report to show only the last four digits
of a social security number. The field is "SocialSecurityNumber" and the
format is
xxx-xx-. I run invoice reports at my job and for specific companies i need to
only show last four numbers of ssn. I tried the following code in Private Sub
Detail_Format.

if me.billingid = "10016" then
Me.SocialSecurityNumber = "xxx-xx-" + Right(Me.SocialSecurityNumber, 4)
end if

It gives me an error . I'm new to VB Access. Any help would be nice. thanks.

 

[Lord Kelvan]

why dont you do it in the query the report is based on

regardless dont use + use &

"xxx-xx-" & Right(Me.SocialSecurityNumber, 4)

i am not saying that will work i dont normally do stuff like that from
withing the report i build the query with the display data in whatever
format then output it

the expression in the query would be

formatedssn: iif(billingid = "10016", "xxx-xx-" +
Right(SocialSecurityNumber, 4),SocialSecurityNumber)

hope this helps

Regards
Kelvan

 

[John W. Vinson]

I need to format a report to show only the last four digits
of a social security number. The field is "SocialSecurityNumber" and the
format is
xxx-xx-. I run invoice reports at my job and for specific companies i need to
only show last four numbers of ssn. I tried the following code in Private Sub
Detail_Format.

if me.billingid = "10016" then
Me.SocialSecurityNumber = "xxx-xx-" + Right(Me.SocialSecurityNumber, 4)
end if

It gives me an error . I'm new to VB Access. Any help would be nice. thanks.

Put a textbox on the report named txtSocialSecurityNumber (actually name it
anything but SocialSecurityNumber, to avoid circular references). Set its
control source to

=IIF([BillingID] = "10016", "xxx-xx-" & Right([SocialSecurityNumber], 4),
[SocialSecurityNumber])

No code needed. This assumes that BillingID and SocialSecurityNumber are Text
fields - lose the quotes around "10016" if it's numeric.

 

[Marshall Barton]

I need to format a report to show only the last four digits
of a social security number. The field is "SocialSecurityNumber" and the
format is
xxx-xx-. I run invoice reports at my job and for specific companies i need to
only show last four numbers of ssn. I tried the following code in Private Sub
Detail_Format.

if me.billingid = "10016" then
Me.SocialSecurityNumber = "xxx-xx-" + Right(Me.SocialSecurityNumber, 4)
end if

Actually, the + might be appropriate, depending on what you
want to do with Null values, as in Kelvan's query expression
vs Allen Browne's text box expression.

Regardless of that, please don't multi-post a question, if
you feel you must post to more than one newsgroup, cross
post instead. For setails and additional guidelines for
effective posting, see the Netiquette page at
http://www.mvps.org/access/

 

[yoly1972 via AccessMonster.com]

Thanks, this worked out great. thanks Everyone for your suggestions.

Yoly1972

I need to format a report to show only the last four digits
of a social security number. The field is "SocialSecurityNumber" and the

[quoted text clipped - 8 lines]

It gives me an error . I'm new to VB Access. Any help would be nice. thanks.

Put a textbox on the report named txtSocialSecurityNumber (actually name it
anything but SocialSecurityNumber, to avoid circular references). Set its
control source to

=IIF([BillingID] = "10016", "xxx-xx-" & Right([SocialSecurityNumber], 4),
[SocialSecurityNumber])

No code needed. This assumes that BillingID and SocialSecurityNumber are Text
fields - lose the quotes around "10016" if it's numeric.