dsum with multiple criteria

[Sketchin]

Hello,

I have a dsum statement with multiple criteria that I cant get to work. Basically it returns no records, when it should return records that have a CategoryID = 1 and a State/Province = 14.

TotalSMECount = Nz(DSum("WorkingDays", "qryTotalUsageForDashboardNew", "CategoryID = 1" And "State/Province = " & Me.cboProvinceFilter.Column(2) & "")) + Nz(DSum("UsageDays", "qryEquipmentDaysRapidProtoCumulative", "CategoryID = 1"))

I am guessing that I just have the criteria portion written incorrectly. Is there anything obviously wrong? For the record, when I debug.print Me.cboProvinceFilter.Column(2) it returns the value '14', which is correct for my test data.

 

[plog]

Your 'AND' isn't part of your criteria. It needs to be inside the quotes:

"CategoryID = 1" And "State/Provin...

Also the end part:

& "")


accomplishes nothing. Lastly, if Me.cboProvinceFilter.Column(2) returns text, you need single quotes to apear around it inside the double quotes.

 

[Sketchin]

I actually just noticed that for a numerical value, it needs to be like this:

TotalSMECount = Nz(DSum("WorkingDays", "qryTotalUsageForDashboardNew", "[CategoryID] = 1 " And "[State/Province]= " & Me.cboProvinceFilter.Column(2))) + Nz(DSum("UsageDays", "qryEquipmentDaysRapidProtoCumulative", "CategoryID = 1 " And "[State/Province]= " & Me.cboProvinceFilter.Column(2)))

Still doesn't return any records though.

 

[bob fitz]

I actually just noticed that for a numerical value, it needs to be like this:

TotalSMECount = Nz(DSum("WorkingDays", "qryTotalUsageForDashboardNew", "[CategoryID] = 1 " And "[State/Province]= " & Me.cboProvinceFilter.Column(2))) + Nz(DSum("UsageDays", "qryEquipmentDaysRapidProtoCumulative", "CategoryID = 1 " And "[State/Province]= " & Me.cboProvinceFilter.Column(2)))

Still doesn't return any records though.

Read Plog's post again.
ulative", "CategoryID = 1 " And "[State/Provi
should be:
ulative", "CategoryID = 1 And [State/Provi
All the other AND's need to be changed aswell