Results 1 to 7 of 7
  1. #1
    johnashworth is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Location
    Houston, Texas
    Posts
    7

    DCount check for duplicates with Apostrophes and Null Values

    I am needing to check for duplicates where the field values will sometimes have apostrophes or null values. The code below works for apostrophes but I get an "Invalid use of Null" error if one of the fields is Null, but I still need it to check for duplicates because not all fields are required. The only fields that are required are txtSongData and grpChoirEnsemble.


    If DCount("*", "tblSongData", "[SongTitle] = '" & Replace(Me.txtSongTitle, "'", "''") & "'" & " AND [BookTitle] = '" & Replace(Me.BookTitle, "'", "''") & "'" & " AND [Artist] = '" _


    & Replace(Me.cmbArtist, "'", "''") & "'" & " AND [ChoirEnsemble] = " & Me.grpChoirEnsemble) > 0 Then
    Msg = "This song already exist with this book title and artist for the " & strChoirEnsemble & "."
    Style = vbOKOnly + vbExclamation
    Response = MsgBox(Msg, Style, Title)
    Me.Undo
    Cancel = True
    End If

    Please advise.

    Thank you,
    John
    Last edited by johnashworth; 05-28-2016 at 09:05 PM. Reason: Correct title

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    For the fields that might not be there (i.e. might ne Null), use the Nz function to replace that null with some value (even if it is a blank):

    For example: Nz(Me.Booktitle," ") will return a blank if the title is Null, otherwise it returns whatever value is in Me.BookTitle.

    So you would have: If DCount("*", "tblSongData", "[SongTitle] = '" & Replace(Me.txtSongTitle, "'", "''") & "'" & " AND [BookTitle] = '" & Replace(nz(Me.BookTitle," "), "'", "''") & "'" & " AND [Artist] = '" _ & Replace(nz(Me.cmbArtist," "), "'", "''") & "'" & " AND [ChoirEnsemble] = " & Me.grpChoirEnsemble) > 0 Then

    IMO, this way of detecting duplicates could be problematic, since it depends on all the text values entered being exactly the same each time.

  3. #3
    johnashworth is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Location
    Houston, Texas
    Posts
    7
    Thank you very much John_G

    I have one more issue related to the DCount function.

    Of the 4 fields in the DCount function the only two fields that are required to have data are txtSongData (text value) and grpChoirEnsemble (numerical value for a group indicating which option was selected).

    The Problem
    If any of the other two fields are vacant and there is another identical record, it doesn't report back indicating a duplicate. It only reports a duplicate record if all fields are populated.

    Below is the updated code for the DCount function with the recommended modifications and is called from an BeforeUpdate sub:

    Private Function CheckForDuplicates()

    Dim strChoirEnsemble As String

    Select Case Me.ChoirEnsemble.Value
    Case 1
    strChoirEnsemble = "Choir"
    Case 2
    strChoirEnsemble = "Ensemble"
    End Select

    If DCount("*", "tblSongData", "[SongTitle] = '" & Replace(Nz(Me.txtSongTitle, ""), "'", "''") & "'" & " AND [BookTitle] = '" & Replace(Nz(Me.BookTitle, ""), "'", "''") & "'" _
    & " AND [Artist] = '" & Replace(Nz(Me.cmbArtist, ""), "'", "''") & "'" & " AND [ChoirEnsemble] = " & Me.grpChoirEnsemble) > 0 Then
    Msg = "A song already exists with this book title and artist for the " & strChoirEnsemble & "."
    Style = vbOKOnly + vbExclamation
    Response = MsgBox(Msg, Style, Title)
    Me.Undo
    Exit Function
    End If

    End Function

    Please let me know if you require any additional information.

    Thank you,
    John

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The problem is the Nulls. In a comparison expression, if either side is Null, then the value of the expression is Null, not True or False. So, the expression
    Null = Null is not True as one might expect - it is Null.

    Try applying the Nz to every component of the expression where values are not required:

    If DCount("*", "tblSongData", "[SongTitle] = '" & Replace(Me.txtSongTitle, "'", "''") & "'" & " AND nz([BookTitle],"") = '" & Replace(nz(Me.BookTitle," "), "'", "''") & "'" & " AND nz([Artist],"") = '" _ & Replace(nz(Me.cmbArtist," "), "'", "''") & "'" & " AND [ChoirEnsemble] = " & Me.grpChoirEnsemble) > 0 Then

    (I can't be sure I have that syntax right!)

    It might be easier just to check for duplication of the required fields and issue a warning message to check the others manually.

  5. #5
    johnashworth is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Location
    Houston, Texas
    Posts
    7
    Thank you John_G

    I tried your suggestion but I couldn't get it to work the way I needed so I went the recordset route and got it to work.

    Here's the code if you are interested and please let me know if you see any potential problems.

    Private Function CheckForDuplicates(Cancel As Boolean)

    Dim rs As dao.Recordset
    Dim strSongTitle As String
    Dim strBookTitle As String
    Dim strArrangedBy As String
    Dim strChEn As String
    Dim strChoirEnsemble As String
    Dim lngCount As Long

    If Me.grpChoirEnsemble = 0 Then
    Exit Function
    End If

    Select Case Me.ChoirEnsemble.Value
    Case 1
    strChoirEnsemble = "A Choir"
    Case 2
    strChoirEnsemble = "An Ensemble"
    End Select

    Set rs = CurrentDb.OpenRecordset("SELECT SongTitle, BookTitle, ArrangedBy, ChoirEnsemble FROM tblSongData")
    strSongTitle = Me.SongTitle.Value
    strBookTitle = Replace(Nz(Me.BookTitle.Value, ""), "'", "''")
    strArrangedBy = Replace(Nz(Me.ArrangedBy.Value, ""), "'", "''")
    strChEn = Me.ChoirEnsemble.Value
    lngCount = 0
    rs.MoveNext
    Do While Not rs.EOF
    If strSongTitle = rs.Fields("SongTitle").Value Then
    lngCount = lngCount + 1
    End If
    If strBookTitle = Replace(Nz(rs.Fields("BookTitle").Value, ""), "'", "''") Then
    lngCount = lngCount + 1
    End If
    If strArrangedBy = Replace(Nz(rs.Fields("ArrangedBy").Value, ""), "'", "''") Then
    lngCount = lngCount + 1
    End If
    If strChEn = rs.Fields("ChoirEnsemble").Value Then
    lngCount = lngCount + 1
    End If
    If lngCount = 4 Then
    Msg = strChoirEnsemble & " song already exists with this book title and arranged by."
    Style = vbOKOnly + vbExclamation
    Response = MsgBox(Msg, Style, Title)
    Me.Undo
    Cancel = True
    Exit Function
    Else
    lngCount = 0
    rs.MoveNext
    End If
    Loop
    End Function

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I don't see a major issue with the VBA version. One change you might make is to populate the initial recordset with only those records where the title and ensemble match. By doing that, if you get and empty recordset, you know tight away there are no duplicates, and if there are records to check, you are checking far fewer of them, and only testing 2 fields instead of 4.

    The rs.Movenext right before the Do While... loop is a problem - you are probably starting at the second record of your recordset. you can safely remove the rs.Movenext.

  7. #7
    johnashworth is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Location
    Houston, Texas
    Posts
    7
    Thank you John_G

    I did as you suggested and so far so good.

    How do I tag these as solved?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-04-2013, 01:55 PM
  2. sql variable with apostrophes
    By broecher in forum Programming
    Replies: 3
    Last Post: 04-23-2013, 11:00 AM
  3. Replies: 2
    Last Post: 04-17-2013, 04:59 AM
  4. Finding the Max Date and Null Values if Null
    By SpdRacerX in forum Queries
    Replies: 1
    Last Post: 02-03-2012, 06:29 AM
  5. Dlookup() for NULL
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 03-31-2011, 12:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums