Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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!
0
Dustin Stanley
Asked:
Dustin Stanley
  • 10
  • 8
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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