Using Dcount in an IF statement

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
Getting typemismatch when I am using

"             
        If Nz(DCount("RecordLock", "tblInvoice", "ContractNumber ='" & _
            gContractID & "'" And "RecordLock =" - 1)) > 0 Then
           
"

If gUser = "User" Then
                
        If Nz(DCount("RecordLock", "tblInvoice", "ContractNumber ='" & _
            gContractID & "'" And "RecordLock =" - 1)) > 0 Then
            
            Select Case MsgBox("Not Authorized to Print this report at this time, " _
                    & vbCrLf & "please see Manager or Systems Administrator." _
                    , vbOK Or vbCritical Or vbDefaultButton1, "Currently Not Available")
            
            Case vbOK
                Set rst = Me.RecordsetClone
                strCriteria = rs.Fields("RecordLock") = False
                rst.FindFirst strCriteria
                Me.Bookmark = rst.Bookmark
                Forms![frmCodingSlip]![LblPrintNote].Visible = True
            Case vbCancel
                DoCmd.RunCommand acCmdUndo
                Exit Sub
            End Select
        
        ElseIf Nz(DCount("RecordLock", "tblInvoice", "ContractNumber ='" & gContractID & "'" And "RecordLock = 0")) > 0 Then
   

Open in new window


What am I doing wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
you don't need to use  Nz() in Dcount, dcount returns a number or 0

is ContractNumber DataType TEXT?

ElseIf DCount("RecordLock", "tblInvoice", "ContractNumber ='" & gContractID & "' And RecordLock = 0") > 0 Then

if NOT

ElseIf DCount("RecordLock", "tblInvoice", "ContractNumber =" & gContractID & " And RecordLock = 0") > 0 Then
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I'm guessing that your criteria is wrong. I assune that gContractID is probably numeric and should not be wrapped in singe qoutes.
Karen SchaeferBI ANALYST

Author

Commented:
No gcontracid is alpha numeric.

I decided to revert back to using a recordset.  Thanks for the input.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
:-0 ?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Your syntax is wrong, and Rey is right, no NZ here.
Thus:

If DCount("RecordLock", "tblInvoice", "ContractNumber ='" & _
            gContractID & "' And RecordLock = -1") > 0 Then

/gustav
Karen SchaeferBI ANALYST

Author

Commented:
I decided to revert back to using a recordset.  Thanks for the input.  Points awarded for input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial