Dsum Multiple Criteria - Visitor Forum


	Back to Visitor Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Dsum Multiple Criteria

Melanie Le Roux

2 years ago

Hi I have table (tblLocationPatients) with fields YearId, Location, SchoolTrips, ClinicTrips and TotalTrips

I want to DSum TotalSchoolTrips and TotalClinicTrips by Location and Year. (Sometimes schools and clinics are in the same location - I need to know how many trips we make to the same location in a year.

My form is [Forms]![FrmLocationPtsDataEntry] and I have the Location and YearID on the form that I want to match the DSum to

Me.TotalLocationTrips = DSum("[TotalSchoolTrips] And [TotalClinicTrips]", "LocationTrips", "[YearID] = " & [Forms]![FrmLocationPtsDataEntry]![YearId] & " And [Location] = " & [Forms]![FrmLocationPtsDataEntry]![Location])

I have tried various diff syntaxes, but it keeps throwing errors.

Someone please help me off the ledge here. Thank you.

	Kevin Robertson @Reply 2 years ago
Sum up TotalSchoolTrips and TotalClinicTrips seperately. You can store each value in Variables and then add the two values together. Is Location a String value? If so, you will need to use Double Double Quotes.

	Kevin Yip @Reply 2 years ago
Change "And" to "+". "And" is a boolean operator, not a math operator: DSum("[TotalSchoolTrips] + [TotalClinicTrips]", "TableName", "Criteria" ) Both fields need to be numeric and not null for this to work. If this is done on a form, both fields also need to be actual field names from the form's record source.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Visitor Forum.

Next Unseen

New Feature: Comment Live View