Results 1 to 4 of 4
  1. #1
    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. #2
    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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pjawynn View Post
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    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
  •