Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Data Type Question

Discussion in 'Information Technology' started by programmingrookie, Nov 18, 2005.

  1. programmingrookie

    programmingrookie
    Expand Collapse
    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
     
  2. Loading...


  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    programmingrookie wrote:
    > 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.

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     
  4. Larry Daugherty

    Larry Daugherty
    Expand Collapse
    Guest

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

    HTH
    --
    -Larry-
    --

    "programmingrookie" <programmingrookie@discussions.microsoft.com>
    wrote in message
    news:1A305EC2-BB22-440D-B53E-D75703619400@microsoft.com...
    > 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
     

Share This Page