VB question on Access form

[mpmason14]

You may be familiar with this problem as I posted it before, so here we go...

I have a module that provides error checking for the length of a SSN and I need a
little help in one area...

The code will check to see if the SSN is 9 digits exactly, and if not it will produce an error message, otherwise it will cipher the SSN. The error message is part of the VBA (the 'msg' in the code below).

My question is how can I get the code to work so it will still check for 9 digits (for use in a form) but also accept an empty string (when opening a report) and NOT produce the same error message?

Thanks

My code is here:

Public Function CipherSS(SSN As String)
   Dim Mask As String, Build As String, x As Integer
   Dim Msg As String, Style As Integer, Title As String, DL As String
 
   DL = vbNewLine & vbNewLine
 
   If Len(SSN) <> 9 Then
      Msg = "SSN Must Be Exactly 9 Digits!" & DL & _
            "Check SSN '" & SSN & "' and try again . . ."
      Style = vbInformation + vbOKOnly
      Title = "SSN Not 9 Digits Error! . . ."
      MsgBox Msg, Style, Title
      CipherSS = Null
   Else
      Do
         x = x + 1
 
         If Asc(Left(SSN, 1)) < 58 Then
            If (x Mod 2) = 0 Then
               Mask = 64
            Else
               Mask = 32
            End If
         Else
            If (x Mod 2) = 0 Then
               Mask = -64
            Else
               Mask = -32
            End If
         End If
 
         Build = Build & Chr(Asc(Mid(SSN, x, 1)) + Mask)
      Loop Until x = 9
 
      CipherSS = Build
 
   End If
 
End Function

[Scott Price]

Instead of:

If Len(SSN) <> 9 Then

try:

If Not IsNull(SSN) AND Len(SSN) <> 9 Then

Regards,
Scott

[mpmason14]

that didn't seem to work.

I still get the "Must be 9 digits error" coming up when I try to open the report with a "0" or empty string in the SSN field. still trying to figure this one out.

thanks

[Scott Price]

that didn't seem to work.

I still get the "Must be 9 digits error" coming up when I try to open the report with a "0" or empty string in the SSN field. still trying to figure this one out.

thanks

You must have your default value set to not accept nulls...

Try:

If SSN<>"0" AND SSN<>"" AND Len(SSN) <> 9 Then

Regards,
Scott

[missinglinq]

I'm confused! The technical aspect of getting the report to accept an invalid SSN aside, if you have your form set up so as to require that a valid SSN be entered, why would you need your report to accept a non-valid SSN? How is a non-valid SSN going to be entered into your table?

Linq ;0)>

[mpmason14]

I'm confused! The technical aspect of getting the report to accept an invalid SSN aside, if you have your form set up so as to require that a valid SSN be entered, why would you need your report to accept a non-valid SSN? How is a non-valid SSN going to be entered into your table?

Linq ;0)>

heres how it works:
1. OPTIONAL TO USER: ssn entered on form
2. ssn saved in table when record saved from form
3. on opening the report, the reports control source is a query that uses the module code previously posted to verify if the ssn is not null, but will still open the report even if there are blank (empty string) ssn's.
4. because the report is based off a query, and that query has "built-in" error checking using the module, it will still produce the error message contained in the code ("SSN Must be Exactly 9 Digits")

i want to get it so i can still open the report and NOT receive the error message for an empty string...thats all

hope that helps
thanks

[Scott Price]

My question is how can I get the code to work so it will still check for 9 digits (for use in a form) but also accept an empty string (when opening a report) and NOT produce the same error message?

I guess I read the question backward, and assumed the error was being generated whenever opening a form to enter a new SSN...


Regards,
Scott

[mpmason14]

I guess I read the question backward, and assumed the error was being generated whenever opening a form to enter a new SSN...


Regards,
Scott

no problem, but does anyone have a solution? i'm continuing to work on it but to no avail.

[Scott Price]

no problem, but does anyone have a solution? i'm continuing to work on it but to no avail.

Did you try the code I posted in post #4?

You don't have this same msgbox being called somewhere else, do you? Like in the On Open event of the report, etc...

Regards,
Scott