UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Duplicate Check Syntax Error?, Any Version    
 
   
damian.green
post Feb 16 2020, 05:38 PM
Post#1



Posts: 171
Joined: 24-October 18



I have a check on the subform before update to look for duplicate combinations of the individual, the certification and specific certification level.

For example: I shouldn't have 2 records of Lean Six Sigma > Master Black Belt.

I'm getting a syntax error (missing operator) in query expression '[nameID]=2 and [certification_ID]=4 and [certification_Level_ID]='

Is this because I have a null/blank value in the certification level ID field?

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ClickResult As VbMsgBoxResultEx
    
    If DCount("*", "[t_Contacts_Certification_Relationship]", "[name_ID]= " & Me.[Name_ID] & " And [Certification_ID]= " & Me.[Certification_ID] & " And [Certication_Level_ID]= " & Me.[Certication_Level_ID]) > 0 Then
        ClickResult = Dialog.RichBox("This is a duplicate record. " & "<p/>" & _
                                    "Click OK to remove it.", vbOKOnly + vbCritical, "Duplicate Entry", , , 0, False, False, False)
        If ClickResult = vbOK Then
            Me.Undo
        End If
    End If
End Sub
Go to the top of the page
 
tina t
post Feb 16 2020, 05:47 PM
Post#2



Posts: 6,453
Joined: 11-November 10
From: SoCal, USA


QUOTE
I'm getting a syntax error (missing operator) in query expression '[nameID]=2 and [certification_ID]=4 and [certification_Level_ID]='

Is this because I have a null/blank value in the certification level ID field?

yes. the expression is incomplete without a value "after the equal sign".

QUOTE
I have a check on the subform before update to look for duplicate combinations of the individual, the certification and specific certification level.

so, in your process, why would the certification level be left blank? that's a business process question that you should consider. from a technical standpoint, you can "fill in" a default value so the expression will run, by using the Nz() function. suggest you read up on it in Help so you understand how it works. it's likely that any value you "fill in" will not make a match, which will allow the new record to be added - which brings us back to the business process: in what situation(s) would the certification level for a record be unknown, and how does the business process address those situations?

hth
tina
This post has been edited by tina t: Feb 16 2020, 05:47 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
damian.green
post Feb 16 2020, 05:54 PM
Post#3



Posts: 171
Joined: 24-October 18



Adding in the Nz fixed the error. Thanks.

CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ClickResult As VbMsgBoxResultEx

    If DCount("*", "[t_Contacts_Certification_Relationship]", "[name_ID]= " & Me.[Name_ID] & " And [Certification_ID]= " & Me.[Certification_ID] & " And [Certication_Level_ID]= " & Nz(Me.[Certication_Level_ID], 0)) > 0 Then
        ClickResult = Dialog.RichBox("This is a duplicate record. " & "<p/>" & _
                                    "Click OK to remove it.", vbOKOnly + vbCritical, "Duplicate Entry", , , 0, False, False, False)
        If ClickResult = vbOK Then
            Me.Undo
        End If
    End If
End Sub
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th April 2020 - 05:55 PM