[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Using Dcount in an IF statement

Posted on 2014-02-25
6
Medium Priority
?
373 Views
Last Modified: 2014-03-06
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
Comment
Question by:Karen Schaefer
6 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 39887137
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39887154
I'm guessing that your criteria is wrong. I assune that gContractID is probably numeric and should not be wrapped in singe qoutes.
0
 

Author Comment

by:Karen Schaefer
ID: 39887165
No gcontracid is alpha numeric.

I decided to revert back to using a recordset.  Thanks for the input.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39887216
:-0 ?
0
 
LVL 53

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 39888072
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
 

Author Closing Comment

by:Karen Schaefer
ID: 39910068
I decided to revert back to using a recordset.  Thanks for the input.  Points awarded for input.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question