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?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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.