Data Type Question

[Guest]

I have a dialog box that allows someone to search personnel records based on
an SSN. I've been testing with known SSNs and I'm running into an
interesting problem where sometimes the SSN has to have the dashes and at
other times it cannot have them for a successful query. I'm having the
results of this query open a form, based on whether or not a record is found,
and that part of my code is working. Any ideas on the SSN problem? I've
posted my code below:

Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click

Dim stDocName As String

stDocName = "qryRecordSearch"
DoCmd.OpenQuery stDocName, acNormal, acReadOnly

If stDocName <> "" Then
stLinkCriteria = "[SocialSecurity#]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm "frmApplicants", , , stLinkCriteria
Else
DoCmd.OpenForm "frmApplicantsNew", acNormal, acReadOnly
End If

DoCmd.Close acQuery, stDocName
DoCmd.Close acForm, "frmRecordQuery"

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click

End Sub

 

[Rick Brandt]

I have a dialog box that allows someone to search personnel records
based on an SSN. I've been testing with known SSNs and I'm running
into an interesting problem where sometimes the SSN has to have the
dashes and at other times it cannot have them for a successful query.
I'm having the results of this query open a form, based on whether or
not a record is found, and that part of my code is working. Any
ideas on the SSN problem? I've posted my code below:

If you are using an InputMask for the SSN and the InputMask is controlling
the dashes then that can be set up one of two ways. It can be configured so
that the mask characters (the dashes in this case) are only displayed but
not saved as part of the data or you can configure it so that the dashes are
stored as part of the data.

If your InputMask was originally configured one way and then later changed
you will have some SSN records that include the dash as part of the data and
some that don't. The InputMask will make all of these records appear the
same. Looking directly at the data in a datasheet that has no InputMask on
the field should reveal this.

Another scenario would be if your InputMask was configured to NOT store the
dash but then you imported some SSNs from another source where the dashes
were included.

If you find that this is the case you should be able to run some update
queries to make all of the data consistent.

 

[Larry Daugherty]

open your table and search for dashes in the SSN field.

HTH