dsum with multiple criteria

Topic

New Reply

Hi all,

Not having much luck here. Trying to dsum using:

vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “[badge number] = ‘” & Forms![frm_Report_Criteria]![vEmployee] & “‘ and [Activity Date] = #” & vSun & “#), 0)

getting syntax errors. I tried searching on topic but could not locate example of combining this criteria.

I got this:

vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “[Activity Date] = #” & vSun & “#”), 0)

to work, but cannot complete with additional criteria.

Any help would be appreciated.

Thank You

Reply | Quote

Replies

I think all that was wrong was that you left out the closing ” after the #. When using multiple criteria I prefer to put brackets around each condition.

vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “([badge number] = ‘” & Forms![frm_Report_Criteria]![vEmployee] & “‘) and ([Activity Date] = #” & vSun & “#)”), 0)

I find it useful to introduce a string variable for the criteria. This helps with the debugging.

Dim strwhere as string
strwhere = “([badge number] = ‘” & Forms![frm_Report_Criteria]![vEmployee] & “‘) and ([Activity Date] = #” & vSun & “#)”
Debug.print strwhere
vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”,strwhere), 0)

Reply | Quote

I think all that was wrong was that you left out the closing ” after the #. When using multiple criteria I prefer to put brackets around each condition.

vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “([badge number] = ‘” & Forms![frm_Report_Criteria]![vEmployee] & “‘) and ([Activity Date] = #” & vSun & “#)”), 0)

I find it useful to introduce a string variable for the criteria. This helps with the debugging.

Dim strwhere as string
strwhere = “([badge number] = ‘” & Forms![frm_Report_Criteria]![vEmployee] & “‘) and ([Activity Date] = #” & vSun & “#)”
Debug.print strwhere
vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”,strwhere), 0)

Thank You very much for the input.

I no longer get any syntax errors. However in this case I do not get the right result either. For testing purposes, the table this dsum hits has three records in it. One of them should be found by equation. What seems to be happening is the result is evaluating to Null and therefore converting to zero. When I step through and toggle a breakpoint after the equation, each individual criteria contains the correct result but the the combined criteria is failing. Would you have any thoughts as to why?

Reply | Quote

Could you attach a stripped down and zipped copy of the database that demonstrates the problem?

Reply | Quote

Could you attach a stripped down and zipped copy of the database that demonstrates the problem?

For now hold shift key when opening. The form called “frm_Report_Criteria” opens the parameters for the report “rpt_Op_Log_2”. If you select 11/21/2009, 400, 401, and 503843 in the drop downs respectively. This should find a record meeting that criteria in the “tbl_Inputs_Outputs” table. In the On Print event of that report you will find the dsum calc’s.

Reply | Quote

For vWeekendingDate = 11/21/2009, vSun = 11/15/2009 but there are no records in the table for which Activity Date = 11/15/2009, so DSum returns Null and Nz converts it to 0.

In other words, the code does what you tell it to do. Whether you want this result is something else, but Access can’t help that.

Reply | Quote

Thank You Hans

For now hold shift key when opening. The form called “frm_Report_Criteria” opens the parameters for the report “rpt_Op_Log_2”. If you select 11/21/2009, 400, 401, and 503843 in the drop downs respectively. This should find a record meeting that criteria in the “tbl_Inputs_Outputs” table. In the On Print event of that report you will find the dsum calc’s.

Thank you again,
Kevin

Reply | Quote

Badge Number is a number field, so you don’t need to put single quotes around the values.
[badge number] = ” & 503843 rather than [badge number] = ‘” & 503843 & “‘”

For Dates you need # if the Date is actually a string rather than a Date, so I don’t think you need them either.

I am in australia. I need the # marks because we need to format Dates using format(vSun,”mm/dd/yyyy”) to put the date in the right format.
So I can’t test this without the # marks.

Because there are such a lot of fields, can you point us to a particular value that is wrong, and say what you think it should be.

Reply | Quote

I really appreciate everyone looking at this. The one record that should pull is for the line:

“vInPut430TarsMon = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “[badge number] = ‘” & 503843 & “‘ and ([Activity Date] = #” & vSun & “#)”), 0)”

My apologies for copying the wrong line. The [Badge Number] field is a number field. I tried to take the single quotes out but it still gave me a zero. The right answer should be three.

The other thing that I noticed is usually the “AND” part of the statement is blue and in caps. In this case it is not. Is that because it is part of the overall string?

Thanks
Kevin

Reply | Quote

”vInPut430TarsMon = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “[badge number] = ‘” & 503843 & “‘ and ([Activity Date] = #” & vSun & “#)”), 0)”

Shouldn’t this line use vMon instead of vSun?

Reply | Quote

Shouldn’t this line use vMon instead of vSun?

Hans,

Now I feel really stupid. Sorry to have taken your time on this. I appreciate the help though.

Thanks
Kevin

Reply | Quote

And yes, the word “and” is part of the string here so it is not formatted as a VBA keyword.

Reply | Quote