# Thread: Combining Dcount form two different Tables

1. Registered User
Join Date
Feb 2013
Posts
99

## Unanswered: Combining Dcount form two different Tables

Morning

Is there a way to combine the two following Dcounts to check the values in both tables

DCount("*", "ParkingViolationstbl", "[VIN]='" & Me![VIN] & "'") = 3
or
DCount("*", "Infringementtbl", "[VIN]='" & Me![VIN] & "'") = 0

Also how to you perform a docunt between a number range

Thank you

2. Registered User
Join Date
Nov 2011
Posts
413
Are you just wanting the total count of all violations? If so, then just add the two together.
Me.Text0 = DCount("[VIN]", "ParkingViolationstbl")
Me.Text2 = DLookup("VIN", "Infringementtbl")
Me.Dirty = False
Me.Total = [Text0] + [Text2]
Would need more details to help with your actual end result.
Are these tables in a qry, on a form/subform??
Last edited by Burrina; 08-11-13 at 03:56. Reason: Corrections

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by pjawynn
Also how to you perform a dcount between a number range
The 3rd parameter in the dcount is effectively a 'where' clause without using the word where. so you can have multiple elements

Code:
```DCount("*", "Infringementtbl", "[VIN]='" & Me![VIN] & _
"' AND MyNumericColumn BETWEEN " & MyLowerValue & " AND " & MyHigherValue)```
the 3rd parameter will inlcude rows that resolve to a boolean value so you can also use brackets (), OR, AND NOT to identify rows that you want to include

4. Registered User
Join Date
Feb 2013
Posts
99

## Still stuck

Thanks for the replys I still stuck on this system for Parking Warnings.

The officers have to warn vehciles when they have not paided the parking charge if this vehcile then parks again on the campus and does not pay they are then fined. But then it reappears again its a warning and then again after that it would be a fine and the process keeps repeating.

I got around this with a dcount example below

If DCount("*", "ParkingViolationstbl", "[VIN]='" & Me![VIN] & "'") = 1 Then
Me.Filter = "VIN = """ & Me.VIN.Value & """"
Me.FilterOn = True
Me.VIN.BackColor = vbRed
Me.VIN.BackStyle = 1
MsgBox "Please Issue Notice", vbOKOnly, "Exceeded Warning"
Me.VIN.BackColor = vbBlack
Me.VIN.BackStyle = 0
Me.FilterOn = False
DoCmd.GoToRecord acDataForm, "Parking Violations", acLast

If DCount("*", "ParkingViolationstbl", "[VIN]='" & Me![VIN] & "'") = 3 Then
Me.Filter = "VIN = """ & Me.VIN.Value & """"
Me.FilterOn = True
Me.VIN.BackColor = vbRed
Me.VIN.BackStyle = 1
MsgBox "Please Issue Notice", vbOKOnly, "Exceeded Warning"
Me.VIN.BackColor = vbBlack
Me.VIN.BackStyle = 0
Me.FilterOn = False
DoCmd.GoToRecord acDataForm, "Parking Violations", acLast

But then someone mentioned that what happens if when you return to the vehicle and it has left so you can not fine it. The next time you enter it onto the system it will not provide a warning because the dcount will equal for example 2

so I would have to referance the other table for the fines

If DCount("*", "Infringementtbl", "[VIN]='" & Me![VIN] & "'") = 2 Then
Me.Filter = "VIN = """ & Me.VIN.Value & """"
Me.FilterOn = True
Me.VIN.BackColor = vbBlue
Me.VIN.BackStyle = 1
MsgBox "Please Issue Notice Vehicle not Fine on Previous Occurance", vbOKOnly, "Exceeded Warning"
Me.VIN.BackColor = vbBlack
Me.VIN.BackStyle = 0
Me.FilterOn = False
DoCmd.GoToRecord acDataForm, "Parking Violations", acLast

Really not sure on how to work this have been on and off it for about a week now trying to get it working

Could really do with some help

Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•