Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, All.

    Here's my problem, which I was hoping you might be able to help me solve.

    Let's say my database is for library records and I manually enter each book's ISBN. I have a way to check to see if the ISBN already exists:

    Code:
     Private Sub Pat_Ref_No_BeforeUpdate(Cancel As Integer)
    Dim vartitle As Variant
    
    If Me.ISBN_No <> "N/A" Then
    vartitle = (DLookup("[ISBN]", "tbl_main", "[ISBN] = '" & Me.ISBN_No& "'"))
    
    If Not IsNull(vartitle) Then
    If Msgbox("This ISBN number already exists.", vbOKOnly) Then Me.Undo
    
    Else
    End If
    End If
    
    End Sub
    This works well. But what if, for example, I enter an ISBN number is as 1-234567890 and it already exists as 1234567890?

    In other words, the record already exists, but the checking method described above will not find it because it contains different characters.

    In my case, I think it would be best to search for all numbers that end with the same 5 digits, display any matching numbers and ask the user if they want to enter this new record anyway. But how on Earth would I do that?

    Perhaps you know how to do this or have a better solution?

    Thanks,

    JoeK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In what ways can/will the ISBN be entered? Is the hyphen the only possible extra character, or can there be others?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    In what ways can/will the ISBN be entered? Is the hyphen the only possible extra character, or can there be others?
    The number could be entered by hand or scanned in from a bar-code.

    There could be hyphens or spaces (actually, more likely spaces).

    In my real datatabase (and not this example of a library) there could also be alphabetical letters appended to the end of the number, which should be ignored.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To get rid of the spaces and hyphens, you could use

    vartitle = DLookup("ISBN","tbl_main","Replace(Replace(ISBN,"" "",""""),""-"","""")='" & Replace(Replace(Me.ISBN_No," ",""),"-","") & "'")

    The last character of the ISBN could be an 'X' so it'll be difficult to simply ignore all alphabetic characters.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, this works very well...mostly.

    If I copy/paste the numbers, this code picks up the duplicate.
    If I type in the numbers, this code picks up the duplicate.

    If I scan in the numbers using a barcode scanner, this code does not pick it up as a duplicate. It will pick it up as a duplicate only if the existing record was also scanned in.

    1) What additional information could a scanner be putting into the DB that I do not see (to my eyes the two numbers are identical)
    2) Is there any way to alter the code above to compensate for whatever difference the scanned record has in it to the existing typed record?

    Thanks for your help.

    JoeK

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    We'd have to see the actual values that the scanner places into the table. Could you post a (zipped) database with a small sample table? (We don't need to see the entire database)

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured out that it enters a carriage return after the number (actually, it was my co-worker who was a librarian for 20 years!)

    Is it possible to alter the code to also ignore carriage returns?

    Thanks,

    JoeK

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    vartitle = DLookup("ISBN","tbl_main","Replace(Replace(Replace (ISBN,"" "",""""),""-"",""""),Chr(13),"""")='" & Replace(Replace(Replace(Me.ISBN_No," ",""),"-",""),Chr(13),"") & "'")

    This version will ignore carriage returns (ASCII character 13). If that doesn't work, try changing both occurrences of Chr(13) to Chr(10)
    ASCII character 10 is the line feed.
    If that doesn't work either, try Chr(13) & Chr(10)
    This is the carriage return/line feed combination.

Posting Permissions

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