Build an Aggregate Query and use that in your DCount.

This can be done with one single SQL statement:

    SELECT Count(*) AS CountOfUniqueTrips
    FROM (SELECT DISTINCT TripID FROM TripT)

Run it, and the first row will return the number of unique trips.

This type of query can't be designed in the graphical query designer.  You must type the SQL statement manually.

Thank you for your responses. If I do the Aggregate Query and use it with a DCount that will just return a total of all my trips, right? Even if I have criteria "Like [Enter PayrollPeriod]. As for the SQL statement. I'm pretty new to that and don't really understand how to add that to an existing query. I put the statement in a new query, but once again I just got the total of all my trips. When I tried to add fields to attempt a criteria to limit it to just what I wanted to see I got error messages. So I am not sure how to go about doing that I guess.

Hi Matthew, Kevin's method and mine only give you a count of unique trips, that's it.  It doesn't do anything else, and won't help you to do the kind of thing shown in your last picture.  To see info of a particular trip, remove that box on the left, then add a condition to show only the specified trip, or trips. See my picture below.  My example will show only info for trip 756.  To show info for trips 756 and 757, type "756 or 757" in the criteria box.  (That's right: "and" means "or" in this instance, long story.)  Alternatively, you can type "756" and "757" on different lines in that column.

It sounds like I may have to restructure my tables or something. In the pic of the form I oringally posted it shows a count of 23, but that is how many Stops there were on the 10 Trips that I did in Nov. I use Like [Enter Payroll Period] to limit the results (i.e. Nov22*). I want it to count each Trip Number (or TripID) only once (so total would be 10) instead of counting each one for every Stop on a given Trip (23 Stops), but since each Stop is it's own record it counts each stop. The only way I've found to get around this problem is to make fields for each possible stop, but out of 760+ Trips only about 70 have had even 3 stops, let alone the oddball 4 to 7 stops which only account for maybe 5 Trips. Each stop with its own field for Pickup or Delivery Date, location, etc. would be a ridiculous amount of fields in each row and 90% of the time I'm only gonna be using the first 2 sets of fields leaving a ton of blank space.

I should note that MileageT is the table where I have the TripID (primary key) and TripNumber fields (my Db has undergone a lot of changes as I've attempted to learn my way thru) and the final Stop # on all trips is 90 regardless of how many stops a trip had. It's how my company denotes which stop is the last one.

I believe I now have a solution to this. I used =Count(IIF([StopNumber]= Like '1',1)) in a new text box in the form footer. There can only be one first stop per trip so this accomplishes counting the trips instead of the stops.

Without the = after StopNumber