Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Multiple criteria in dsum

    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
    Kevin

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •