Ms Access Dcount Help Please! Saying 0 when it is more than 0

Here is my code. They both are above 0 in count but:

Doesn't work:
If DCount("[LocID]", "[ProdLocations]", "[LocID] =" & Me.ProdLocLocID) > 0 Then

Open in new window



Does Work:
If DCount("[ProductID]", "[ProdLocations]", "[ProductID] =" & Me.ProductID) > 0 Then

Open in new window


Whole Code:
If DCount("[ProductID]", "[ProdLocations]", "[ProductID] =" & Me.ProductID) > 0 Then
 If DCount("[LocID]", "[ProdLocations]", "[LocID] =" & Me.ProdLocLocID) > 0 Then
        MsgBox ("IT EXISTS!")
        Else
        MsgBox ("Nothing!")
End If
End If

Open in new window



Please help! LocID and ProductID are both numeric. Sometimes the record count may only be 1.

Thanks!
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this too

Private Sub Command35_Click()
If DCount("*", "[ProdLocations]", "[ProductID] =" & Me.ProductID & " AND [ProdLocID] =" & Me.ProdLocLocID) > 0 Then
        MsgBox ("IT EXISTS!")
        Else
        MsgBox ("Nothing!")
End If
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this

If DCount("*", "ProdLocations", "[LocID] =" & Me.ProdLocLocID) > 0 Then
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Still says Nothing Rey. It works without the criteria. As in it returns things.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dustin StanleyEntrepreneurAuthor Commented:
YES I figured it out! I thought I had this set up on LocID when it was supposed to be ProdLocID


This Code works:

Private Sub Command35_Click()
'If DCount("[ProductID]", "[ProdLocations]", "[ProductID] =" & Me.ProductID) > 0 Then
 If DCount("[ProdLocID]", "[ProdLocations]", "[ProdLocID] =" & Me.ProdLocLocID) > 0 Then
        MsgBox ("IT EXISTS!")
        Else
        MsgBox ("Nothing!")
End If
'End If
End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
How can I combine these dcounts. I need the record to be >0 for both at the same time.
Exists
Else
 Nothing
0
 
Rey Obrero (Capricorn1)Commented:
you can "AND" the dcount

Private Sub Command35_Click()
If DCount("[ProductID]", "[ProdLocations]", "[ProductID] =" & Me.ProductID) > 0 AND  If DCount("[ProdLocID]", "[ProdLocations]", "[ProdLocID] =" & Me.ProdLocLocID) > 0 Then
        MsgBox ("IT EXISTS!")
        Else
        MsgBox ("Nothing!")
End If
End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
I tried that and there is a expected expression on the second AND  If
0
 
Rey Obrero (Capricorn1)Commented:
oops, remove the second "If"

Private Sub Command35_Click()
If DCount("[ProductID]", "[ProdLocations]", "[ProductID] =" & Me.ProductID) > 0 AND DCount("[ProdLocID]", "[ProdLocations]", "[ProdLocID] =" & Me.ProdLocLocID) > 0 Then
        MsgBox ("IT EXISTS!")
        Else
        MsgBox ("Nothing!")
End If
End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you but now it says everything exists. Is it reading a single record for both criteria? It seems like it is reading from all records in the table.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Sorry I didn't see the second one. But the first one says everything exists and the second one says nothing.
0
 
Rey Obrero (Capricorn1)Commented:
it will read all records that satisfy both criteria.
0
 
Rey Obrero (Capricorn1)Commented:
use the last code I posted.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
So if in my table there is no single record that match both criteria then it says Nothing correct?

The last code says Nothing!
0
 
Rey Obrero (Capricorn1)Commented:
that is correct.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Why and how is the wildcard * used here?
0
 
Rey Obrero (Capricorn1)Commented:
why?  that is the way the function dcount() operates.

if you use the * ,the DCount function calculates the total number of records, including those that contain Null fields.

for more detailed explanation, type dcount in the access help window.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks I was just reading about that.

https://support.office.com/en-us/article/DCount-Function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3

I got it work thanks Rey!
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.