VBA (SQL) Check for record before INSERT

[vapid2323]

Hey guys I have the code below that I created and it works great for checking if an exact date is in the table, but what I really want is to check if there is a record in the table that matches the year. I am attempting to do this with Datepart and Like.

So I change

If fTableRecordCount("tblSurveillance", "WHERE [fk_SiteID]=" & [fk_SiteID] & " AND [Surv_AuditDue]=" & "#" & DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value) & "#", False) > 0 Then

To

If fTableRecordCount("tblSurveillance", "WHERE [fk_SiteID]=" & [fk_SiteID] & " AND [Surv_AuditDue] Like *" & DatePart("yyyy", DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value)), False) > 0 Then

Code that works fine to match exact dates:

If Me.Qual_Complete.Value = 0 Then End
    If MsgBox("Would you like to populate next years Audit?", vbYesNo, "Next Audit") = vbYes Then
        If Nz(Me.Qual_InspectionDate.Value, 0) = 0 Then
            MsgBox "Sorry it would seem that you dont have an audit date filled in, please update that field", vbCritical, "Missing Data"
            Me.Qual_Complete.Value = 0
            End
        Else
            If fTableRecordCount("tblSurveillance", "WHERE [fk_SiteID]=" & [fk_SiteID] & " AND [Surv_AuditDue]=" & "#" & DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value) & "#", False) > 0 Then
                MsgBox "Record allready added, no new record was needed", vbOKOnly, "No Change Made"
                End
            Else
                DoCmd.RunSQL "INSERT INTO [tblSurveillance] ([fk_SiteID], [Surv_AuditDue]) VALUES (" & Me.fk_SiteID.Value & "," & "#" & DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value) & "#);"
            End If
        End If
    Else
        End
    End If
End Sub

And the code I am trying to use to just check the year (Not Working):

Private Sub Qual_Complete_AfterUpdate()
If Me.Qual_Complete.Value = 0 Then End
    If MsgBox("Would you like to populate next years Audit?", vbYesNo, "Next Audit") = vbYes Then
        If Nz(Me.Qual_InspectionDate.Value, 0) = 0 Then
            MsgBox "Sorry it would seem that you dont have an audit date filled in, please update that field", vbCritical, "Missing Data"
            Me.Qual_Complete.Value = 0
            End
        Else
            If fTableRecordCount("tblSurveillance", "WHERE [fk_SiteID]=" & [fk_SiteID] & " AND [Surv_AuditDue] Like *" & DatePart("yyyy", DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value)), False) > 0 Then
                MsgBox "Record allready added, no new record was needed", vbOKOnly, "No Change Made"
                End
            Else
                DoCmd.RunSQL "INSERT INTO [tblSurveillance] ([fk_SiteID], [Surv_AuditDue]) VALUES (" & Me.fk_SiteID.Value & "," & "#" & DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value) & "#);"
            End If
        End If
    Else
        End
    End If
End Sub

 

[pbaldy]

I would try something along the lines of

WHERE Year(YourField) = Year(TheTestDate)

 

[vapid2323]

I would try something along the lines of

WHERE Year(YourField) = Year(TheTestDate)

Thanks for the tip I updated my If statment to the following:

If fTableRecordCount("tblSurveillance", "WHERE [fk_SiteID]=" & [fk_SiteID] & " AND Year ([Surv_AuditDue]) =" & "Year (" & DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value) & ")", False)

And this is the SQL it spits out but it still cant find a matching record (though I do have a 2012 record in the table)"

SELECT * FROM [tblSurveillance] WHERE [fk_SiteID]=503 AND Year ([Surv_AuditDue]) =Year (4/19/2012)

 

[vapid2323]

Scratch that i forgot the octothorpe

If fTableRecordCount("tblSurveillance", "WHERE [fk_SiteID]=" & [fk_SiteID] & " AND Year ([Surv_AuditDue]) =" & "Year (#" & DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value) & "#)", False) > 0 Then

 

[pbaldy]

I would think that could be simplified a little to:

..." AND Year([Surv_AuditDue]) =" & Year(DateAdd("yyyy", 1, Me.Qual_InspectionDate.Value))",...