Filtered Sum in a form

  • Thread starter Thread starter halfbodyguy
  • Start date Start date
H

halfbodyguy

OK, we are doing A Delivery Databse -


In the order entry form we need to have a Truck total that is the total
of all the trucks for the DAY, When I use:

sum[trucks] I get all the trucks in the database

when I use "=[trucks]" I get just the ones in the record.

I want to use an expressions to grab all total trucks that are in any
record with the Delivery date the same as the one I am in...

Any Ideas? (Essentially I need an IF statement that works in the
control source expression)

Thanks!
 
Use the DCount()
=DCount("trucks","yourtable", "TableKey = " & TableKey)
replace the variables with your own names.
 
It just says #Error



I did like this:

=DCount("trucks","tblordernumber","[Current Deliv Date] = " & [Current
Deliv Date])

Trucks is the field in the form / tblOrderNumber is the table, Current
Deliv Date is the name on both the form and the table.

What am i doing wrong?
 
Acually I think you misunderstood.

Trucks is going to have a Number in it. 1 or 2.

I need to add the TOTAL for that day.

so I cound have 3 records but 6 trucks.

I want it to SUM all the ones that are only from that day.
 
Dates need to be delimited with # characters, and need to be in mm/dd/yyyy
format. (Okay, the latter isn't absolutely true: they can be in unambiguous
formats such as yyyy-mm-dd or dd mmm yyyy. The point is, if your Regional
Settings have your short date format as dd/mm/yyyy, you'll run into
problems.)

Try:

=DCount("trucks","tblordernumber","[Current Deliv Date] = " & _
Format(Me.[Current Deliv Date], "\#mm\/dd\/yyyy\#"))

If you have problems, you might want to rename the textbox on your form so
that it's different than the name of the field to which it's bound. (I
always rename all of my textboxes to start with txt, as in txtFieldName)
 
i pasted it as it is there, and it says there is a syntax error
(unfortinately when they get to this size or expressions I no longer
make enough sense of them to figure out the problem)

I realize that it is

Dcount <expr>,<table>,<criteria> but what makes them up and the
breakdown of symbols kind of loses me after a while...

In any case it is saying that the syntax is wrong.....

Help :-)
 
OK, Update...

(first, although not terrible important, I am looking for a Dsum, not a
Dcount, cause i am trying to get a total within the fields, not a count
of the records.)

ok, With

=DSum("[trucks]","tblordernumber","[Current Deliv Date]"="[Current
Deliv Date]")

I get the total of EVERYTHING (without date restrcitions)

Or with yours:

=DSum("trucks","tblordernumber","[Current Deliv Date]" = " & _
Format(Me.[Current Deliv Date], "\#mm\/dd\/yyyy\#"))

it said "The Expression entered has an invalid date value" I don't
know if that is a formatting problem on my part or the part of the
syntax given.
 
Is Current Deliv Date a date field, or a text field that happens to contain
a date? If it's just a text field, you don't need to use the Format
function.

What happens if you rename the textbox as I suggested in my original post?
 
The text box is just to display information.

The text box is txtCurrentDeliv

its just a holding place for the source expression.

There is another Text box containing the Current Deliv Date. it is just
looking up directly from the table...

So there IS a tblorderNumber.[Current Deliv Date] Field, as well as a
Text box containing that information.

I want to tell it "Any record with the same delivery date as the date
in the Delivery TEXT BOX, should be Summed and displayed in the 3rd box.
 
=DSum("[trucks]","tblordernumber","[txtcurrentdelivdate]"="tblordernumber.[Current

Deliv Date]")



This is what I had come up with to better explain. This would all be in
the 3rd text box, which is just a location to display the answer to the
expression.

the Expression does not error, but it doesn't show any numbers either.
 
Sorry to post so many without reply. But maybe I have this sort of
wrong....


OK the 3rd peice. The criteria, is simply a TRUE or FALSE return isn't
it? THat may be the problem... I was looking at it as more of a
filter...

I want it to add all the ones that fit the criteria, not to come back
with a TRUE FALSE... would I need to do this different?

I easily managed to get the numbers I need in a report, but I cant put
them in the form becuase it would require multiple queries running the
form at the same time... Unless there IS a way to do that I don't know
about...
 
ALL FIXED. I used a subquery with an uncontrolled form, so it just
looks like another field, but now it works as it is supposed to. Sorry
for the HUGE amounts of worthless jabber, I'm at work and frustrated
with i get to a standstill. People are payin me! haha
 
Back
Top