Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Question Unanswered: Input mask vs. display format

    Working with a membership database and I have 2 questions/annoying problems....

    1) we're entering the credit card number (ccnum) as text. If I use the display format to clean up the display, such as xxxx-xxxx-xxxx-xxxx then if we try to find/search (control F) on that field, we have to enter the dashs as well as the number, even tho the number is stored without dashs. Is there a way I can do both? Display formatted AND search without the dashs?


    2) I'm still learning my way around VBA, but is there a way to control the input mast/format as above, but based on another field? What I'm trying to do is format the CCNUM display based on the cctype field which only allows AMEX, Visa, MC or Discover. The problem is that each credit card company formats their numbers slightly different and American express is 1 number less then Visa.

    Any one have some suggestions or weblinks to share?

    thanks!

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Input mask vs. display format

    Originally posted by skipb4
    Working with a membership database and I have 2 questions/annoying problems....

    1) we're entering the credit card number (ccnum) as text. If I use the display format to clean up the display, such as xxxx-xxxx-xxxx-xxxx then if we try to find/search (control F) on that field, we have to enter the dashs as well as the number, even tho the number is stored without dashs. Is there a way I can do both? Display formatted AND search without the dashs?


    2) I'm still learning my way around VBA, but is there a way to control the input mast/format as above, but based on another field? What I'm trying to do is format the CCNUM display based on the cctype field which only allows AMEX, Visa, MC or Discover. The problem is that each credit card company formats their numbers slightly different and American express is 1 number less then Visa.

    Any one have some suggestions or weblinks to share?

    thanks!
    1) 0000-0000-0000-0999;;_ no dashes
    0000.0000.0000.0999;0;_ with dashes

    It may need to be changed in the table or in the form.


    2) This is what you want. It checks for all valid credit card numbers.

    Function ccCheck()
    Dim sum&, digits&(16), SubDig&(8), CC$, prefix$, i%, DigNum%, SubDigNum%, temp$, CCType$, LenOK%
    Set fde = Forms!DataEntry
    CC = LTrim(fde!CCacct)
    prefix = Mid(CC, 1, 4)
    'Validation prefix of credit card
    LenOK = True
    If Mid(prefix, 1, 1) = "4" Then 'VISA
    CCType = "VISA"
    If Len(CC) <> 16 And Len(CC) <> 13 Then LenOK = False 'Verify length
    ElseIf prefix = "6011" Then 'Discover
    CCType = "Discover"
    If Len(CC$) <> 16 Then LenOK% = False 'Verify length
    ElseIf Mid(prefix, 1, 2) = "34" Or Mid(prefix$, 1, 2) = "37" Then 'American Express
    CCType$ = "American Express"
    If Len(CC$) <> 15 Then LenOK% = False 'Verify length
    ElseIf Mid(prefix, 1, 2) = "51" Or Mid(prefix, 1, 2) = "52" Or Mid(prefix, 1, 2) = "53" Or Mid(prefix, 1, 2) = "54" Or Mid(prefix, 1, 2) = "55" Then 'Master Card
    CCType$ = "Master Card"
    If Len(CC$) <> 16 Then LenOK% = False 'Verify length
    Else
    MsgBox "The number entered is not an acceptable credit card number. "
    GoTo abrt
    End If
    If Not LenOK% Then
    MsgBox "The " + CCType$ + " number is the wrong length. "
    GoTo abrt
    End If
    'Validation of remaining credit card number
    'First, put digits of number into an array
    DigNum% = 0
    For i% = Len(CC$) To 1 Step -1
    digits&(DigNum%) = Val(Mid(CC$, i%, 1))
    DigNum% = DigNum% + 1
    Next i%

    'Put Odd numbers into another array
    SubDigNum% = 0
    For i% = 0 To DigNum% - 1
    If i% Mod 2 = 1 Then 'Get only even digits
    SubDig&(SubDigNum%) = digits&(i%)
    SubDigNum% = SubDigNum% + 1
    End If
    Next i%
    'Double values in array
    For i% = 0 To SubDigNum% - 1
    SubDig&(i%) = SubDig&(i%) * 2
    Next i%
    'Sum odd digits
    sum& = 0
    For i% = 0 To DigNum% - 1
    If i% Mod 2 = 0 Then
    sum& = sum& + digits&(i%)
    End If
    Next i%
    'Break up sub-digits and add them together if needed, then add that number to the sum
    For i% = 0 To SubDigNum% - 1
    If SubDig&(i%) > 9 Then
    temp$ = Trim$(Str$(SubDig&(i%)))
    sum& = sum& + Mid(temp$, 1, 1)
    sum& = sum& + Mid(temp$, 2, 1)
    Else
    sum& = sum& + SubDig&(i%)
    End If
    Next i%
    'Final check for credit card number validity
    If sum& Mod 10 <> 0 Then
    MsgBox "The " + CCType$ + " number is NOT valid."
    GoTo abrt
    Else
    ' optional (checks if credit card has been used before)
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("payment", dbOpenDynaset)
    rs.FindFirst "account = " + Chr(39) + fde!CCacct + Chr(39)
    If Not rs.NoMatch Then
    MsgBox "Customer has ordered before" & vbCr & "Have them call Customer Service" & vbCr & "for any questions."
    Else
    MsgBox "Appears to be a valid Credit Card"
    End If
    rs.close
    db.close
    set rs = nothing
    set db = nothing
    'end optional
    End If
    Exit Function
    abrt:

    End Function

    Let me know if this does work. I cut and pasted it with out much review.

  3. #3
    Join Date
    Apr 2003
    Posts
    3

    Re: Input mask vs. display format

    thanks!!!

    Some of those code lines are past my current skill sets so it might take a little while to digist this and try it out.


    and thanks for the prompt reply!!!!!



    Skip

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •