Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Using Dcount in an IF statement

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?
0
Karen Schaefer
Asked:
Karen Schaefer
2 Solutions
 
Rey Obrero (Capricorn1)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
0
 
Dale FyeCommented:
I'm guessing that your criteria is wrong. I assune that gContractID is probably numeric and should not be wrapped in singe qoutes.
0
 
Karen SchaeferAuthor Commented:
No gcontracid is alpha numeric.

I decided to revert back to using a recordset.  Thanks for the input.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rey Obrero (Capricorn1)Commented:
:-0 ?
0
 
Gustav BrockCIOCommented:
Your syntax is wrong, and Rey is right, no NZ here.
Thus:

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

/gustav
0
 
Karen SchaeferAuthor Commented:
I decided to revert back to using a recordset.  Thanks for the input.  Points awarded for input.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now