Dcount Help 2 Criteria (1 Viewer)

Outlet12 · May 20, 2015

Hello,

I would like to use the formula below to help identify duplicates in a subform. In order to do this I created a basic macro using another I found. The problem I am running into is with the criteria. I want to change these <"TheField> and <[Not]>to variables so they update based of the formula but I can't get it to work for some reason. :banghead:

Code:

 Is Expression
fnFormat([TheField],[Not],"tblTest")>0

Code:

Public Function fnFormat(strField As String, str_ID As String, tbl As String) As Integer
Dim TheCount As Integer
fnFormat = 0
TheCount = DCount(""" & strField & """, _
                    "" & tbl & "", _
                    "TheField = '" & strField & "' And [Not] = '" & str_ID & "'")
                    
If TheCount > 1 Then fnFormat = 1
End Function

Thank you for any help you can provide.

vbaInet · May 23, 2015

Welcome to the forum Outlet12!

I'm not sure what some of your parameters actually represent, specifically TheField and Not. Is TheField supposed to be the field name? Is Not supposed to be criteria or is it also a field name?

So I'm going to make some assumptions:

Code:

Public Function fnFormat(strField As String, str_ID As String, tbl As String) As Integer
    fnFormat = Nz(DCount("[" & strField & "]", _
                         "[" & tbl & "]", _
                         "TheField = '" & strField & "' AND [Not] = '" & str_ID & "'"), _
                  0)
    
    If fnFormat > 1 Then fnFormat = 1
End Function

Dcount Help 2 Criteria (1 Viewer)

Outlet12

New member

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread