Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    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
    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)
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by johnhutchison View Post
    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?
    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you attach a stripped down and zipped copy of the database that demonstrates the problem?


  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    Kevin

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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.
    Attached Files Attached Files
    Kevin

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.


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



  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    Kevin

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by mcneilkm View Post
    "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?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    And yes, the word "and" is part of the string here so it is not formatted as a VBA keyword.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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
    Kevin

Posting Permissions

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