Misbehaving DCount

[TexRanger]

Dear all,

I am facing the following problem with dcount()

the following statement works perfectly well

=DCount("Numx","tx_container"," VehicleId = " & [VehicleId] & " AND CMRDate > " & [TempVars]![PR_006_LowerDate])

but when the above statement is modified to

=DCount("Numx","tx_container"," VehicleId = " & [VehicleId] & " AND CMRDate > " & [TempVars]![PR_006_LowerDate] & "CMRDate < " & [TempVars]![PR_006_UpperDate])

the whole thing goes for a toss and stops working

All help would be appreciated.
Many Thanks

 

[pr2-eugin]

You added AND between VehicleID and CMRDate, but you missed an AND between CMRDate and CMRDate ! Why not use?

=DCount("Numx","tx_container","VehicleId = " & [VehicleId] & "  AND CMRDate BETWEEN " & [TempVars]![PR_006_LowerDate] & " AND " & [TempVars]![PR_006_UpperDate])

 

[TexRanger]

Dear Paul
the missing "AND" was a typo, my apologies, converted my statement to your reply, the DCount Still does not work. and the whole thing goes for a toss. I am trying to count the containers moved by a truck between two dates. Would converting the whole thing to a count query help, and if yes, then maybe i should try it, though the DCount would have been much easier to work with. The current statement reads:

=DCount("Numx","tx_container","VehicleId = " & [VehicleId] & " AND CMRDate BETWEEN " & [TempVars]![PR_006_LowerDate] & " AND " & [TempVars]![PR_006_UpperDate])

all suggestions are welcome and would be appreciated.

 

[namliam]

assuming CMRDate is a date and lowerdate and upperdate are both filled in as US Date formats (MM/DD/YYYY), you still need to wrap the dates in ##

=DCount("Numx","tx_container","     VehicleId = " & [VehicleId] & " " & _ 
                              " AND CMRDate BETWEEN #" & [TempVars]![PR_006_LowerDate] & "# AND #" & [TempVars]![PR_006_UpperDate] & "#")

 

[TexRanger]

many thanks to you paul and mailman, i modified the dcount statement as suggested by you and it works like a charm,

many thanks