Multiple criteria in dsum

Topic

New Reply

I am looking for a little guidance using multiple “OR” statements in a Dsum in access 2007. I tried many variations of the below but can’t get passed an invalid use of null error or a compile error. The idea is to sum where PIIN_CODE is = to the current forms PIIN_CODE “AND” where MOD_TYPE_CODE = B “OR” C “OR” J etc…

can someone point me to the error of my ways within this syntax?

Thank You

‘ vCurrent_Cum_Mod_Cost = DSum(“[MOD_Cost]”, “Cams_Supmod_Data1”, “([PIIN_Code] = ‘” & Forms![supmod -> Exe]![PIIN_CODE] And (Cams_Supmod_Data1.MOD_TYPE_CODE = “B” Or Cams_Supmod_Data1.MOD_TYPE_CODE = “C” Or Cams_Supmod_Data1.MOD_TYPE_CODE = “J” Or Cams_Supmod_Data1.MOD_TYPE_CODE = “L” Or Cams_Supmod_Data1.MOD_TYPE_CODE = “Q” Or Cams_Supmod_Data1.MOD_TYPE_CODE = “V” Or Cams_Supmod_Data1.MOD_TYPE_CODE = “P”) & “‘”) ‘ + vBasic_Cost

Reply | Quote

Replies

Try this.

The IN syntax is easier when you have a list like this.

I also prefer to build the criteria in a variable, then put the variable into the DSUM.
It allows you to concentrate on one bit at a time, and check that that bit is right.

Code:

Dim strCriteria as string
strCriteria =  “(([PIIN_Code] = ‘” & Forms![supmod -> Exe]![PIIN_CODE] & “‘) And ”
strCriteria = strCriteria & ” (Cams_Supmod_Data1.MOD_TYPE_CODE in (‘B’, ‘C’, ‘J’, ‘L’,’Q’,’V’,’P’) ))
debug.print strCriteria
vCurrent_Cum_Mod_Cost = DSum(“[MOD_Cost]”, “Cams_Supmod_Data1”, strCriteria) + vBasic_Cost

Reply | Quote