summing

[nashr1928]

I have posted prior to a calculated field and it works fine but would like to sum the column for the 2011 date. Here is what I have in the text field that sums the whole column. =sum([name])

[nashr1928]

I have tried this but get a minus one. ([Travel Mileage])>("2010"). Is there someone that has a link in this site that shows sum by date?

[ssanfu]

I have tried this but get a minus one. ([Travel Mileage])>("2010"). Is there someone that has a link in this site that shows sum by date?

Minus one in Access means TRUE. You are doing a comparison of a value in a field named "Travel Mileage" with a text string.


Probably the reason that no one has responded is that your first post is vague and contains almost no usable information on your problem. What are you trying to do? What have you tried? What are the table names; field names; field types; relationships; any queries that are close; form or report?
Do you have Screenshots? sample MDB?

Post back with more info.... try to be specific......


Here are three sites I use to find solutions:

"The Access Web" http://mvps.org/access/

"Rogers Access Library" http://www.rogersaccesslibrary.com/

"Allen Browne's site" http://allenbrowne.com/tips.html


BTW, "name" is a reserved word in Access (and shouldn't be used for object names), along with words like "Month", "Time", "Year", 'Day"....... to see a long list of reserved words, goto

http://allenbrowne.com/AppIssueBadWord.html

[nashr1928]

I have a table with a column named [Travel Mileage] I have a calculated text field on a form that I see the total sum =Sum([Travel Mileage]) The problem is that this shows total for the whole column. I want to be able to see the sum of travel mileage but only for 2011 and not see 2010.

[ssanfu]

Option 1 : Limit the recordset to the records for 2011

Option 2 : Use the DSUM() function

[nashr1928]

Thanks for your help. I havent found any examples of this but will keep looking. Eventually I will solve this. Thanks . . . . Rick

[kennejd]

Dsum("[TravelMileage]","TravelMileageTable","Year(TravelDate)=2011" )

Replace with your fields and table names.

[nashr1928]

I have tried this but get a #name as an error in the text filed. This is what I used DSUM("[Travel Mileage]"."assets1"."year(travel mileage)=2011") Where [travel mileage] is the column summed in the table called assets1. This is in a text field on a form.

[kennejd]

The Year(travel mileage) condition won't work....you need the field that stores the travel date.

[nashr1928]

Do you mean the text field on the form that displays the mileage?

[kennejd]

No, I'm guessing you record the travel date in a field in your travel table? That's the field you would use....the field in the table.

[nashr1928]

The travel date is recorded in a column called end date in the table called assets1. so there is a end date for each record.

[kennejd]

If the end date is in a separate table, then you will need to join the tables in a query. Then you can use your dlookup function based on the query....and you can put your year criteria in the query instead of the dlookup.

[nashr1928]

Ok. Here is what I have but still doesnt work. Get #name in the form field. DSUM("[Travel Mileage]"."Billing"."year(End Date)=2011. [Travel Mileage] is the column that stores mileage that is entered with each new record. "Billing" is the table name and (End Date) is the column on the same table that a date is entered for each new record. There is something obvious wrong so I will ponder this over for a while. Is there any spaces or like that? Thanks very much for your help.

[kennejd]

Try this:
DSUM("[Travel Mileage]","Billing","year([End Date])=2011")