Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: DSum With Multiple Criteria and Date Value

    Can someone please tell me why I keep getting type mismatch error here?

    Code:
        Dim DisabilityChecks As Currency
        Dim UnemployementChecks As Currency
        Dim WorkersCompChecks As Currency
        Dim PayChecks As Currency
        
        DisabilityChecks = DSum([SS_Disability], Income_Activity, "CLIENT_KEY=" & CLIENT_KEY And "[Income_Period] = #" & [Income_Period] & "#")
        UnemployementChecks = DSum([Unemployment], Income_Activity, "CLIENT_KEY=" & CLIENT_KEY And "[Income_Period] = #" & [Income_Period] & "#")
        WorkersCompChecks = DSum([Workers_Comp], Income_Activity, "CLIENT_KEY=" & CLIENT_KEY And "Income_Period = #" & Income_Period & "#")
        PayChecks = DSum([Gross_Paycheck], Income_Activity, "CLIENT_KEY=" & CLIENT_KEY And "Income_Period = #" & Income_Period & "#")
        
        Me.Text37.Value = DisabilityChecks + UnemployementChecks + WorkersCompChecks + PayChecks
        Resident_Rent = (Me.Text37.Value) * 0.3

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it may help if you identify the line that has the problem rather than expect people to fidn the line for you.
    have ytou tried putting a watch / breakpoint on the code to make certain it has the values you expect

    bear in mind if the client_key is text / string then it should be encapsulated in either ' or "
    the other possible source of problems could be the date.. you may need to either express it as a date literal, and therefore use an appropriate format mask with the hash symbols or ignoire that if the value is a variable or control that is assigned to a date datatype.

    Id want to check that the [incomeperiod] is correct and has a meaningfull value in it

    I suspect you need to revisit the dsum and look at the way you build the last term
    I think it should read
    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & [Income_Period] & "#")
    or

    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = " & [Income_Period]
    or

    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & format([Income_Period],"yyyy/mm/dd") & "#")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    My mistake, the problem is at the very first DSum.

    Here's the data type for the fields
    Client_Key = Number
    Income_Period = Date/Time
    Unemployment= Currency
    SS_Disability = Currency
    Workers_Comp = Currency
    Gross_Paycheck = Currency

    Quote Originally Posted by healdem View Post
    it may help if you identify the line that has the problem rather than expect people to fidn the line for you.
    have ytou tried putting a watch / breakpoint on the code to make certain it has the values you expect

    bear in mind if the client_key is text / string then it should be encapsulated in either ' or "
    the other possible source of problems could be the date.. you may need to either express it as a date literal, and therefore use an appropriate format mask with the hash symbols or ignoire that if the value is a variable or control that is assigned to a date datatype.

    Id want to check that the [incomeperiod] is correct and has a meaningfull value in it

    I suspect you need to revisit the dsum and look at the way you build the last term
    I think it should read
    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & [Income_Period] & "#")
    or

    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = " & [Income_Period]
    or

    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & format([Income_Period],"yyyy/mm/dd") & "#")

  4. #4
    Join Date
    Sep 2011
    Posts
    5
    I tried this, and I am now getting a Run-time error 2428 (You entered an invalid argument in a domain aggregate function)

    Code:
        DisabilityChecks = DSum([SS_Disability], Income_Activity, "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & Format([Income_Period], "mm/dd/yyyy") & "#")
    Quote Originally Posted by healdem View Post
    it may help if you identify the line that has the problem rather than expect people to fidn the line for you.
    have ytou tried putting a watch / breakpoint on the code to make certain it has the values you expect

    bear in mind if the client_key is text / string then it should be encapsulated in either ' or "
    the other possible source of problems could be the date.. you may need to either express it as a date literal, and therefore use an appropriate format mask with the hash symbols or ignoire that if the value is a variable or control that is assigned to a date datatype.

    Id want to check that the [incomeperiod] is correct and has a meaningfull value in it

    I suspect you need to revisit the dsum and look at the way you build the last term
    I think it should read
    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & [Income_Period] & "#")
    or

    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = " & [Income_Period]
    or

    Code:
    "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & format([Income_Period],"yyyy/mm/dd") & "#")

  5. #5
    Join Date
    Sep 2011
    Posts
    5

    Problem Solved (Too many mistakes and Errors)

    Problem Solved (Too many mistakes and Errors)

    Code:
        Dim DisabilityChecks As Currency
        Dim UnemployementChecks As Currency
        Dim WorkersCompChecks As Currency
        Dim PayChecks As Currency
        
        DisabilityChecks = DSum("SS_Disability", "Income Activity", "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & Format([Income_Period], "mm/dd/yyyy") & "#")
        UnemployementChecks = DSum("Unemployment", "Income Activity", "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & Format([Income_Period], "mm/dd/yyyy") & "#")
        WorkersCompChecks = DSum("Workers_Comp", "Income Activity", "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & Format([Income_Period], "mm/dd/yyyy") & "#")
        PayChecks = DSum("Gross_Paycheck", "Income Activity", "CLIENT_KEY=" & CLIENT_KEY & " And [Income_Period] = #" & Format([Income_Period], "mm/dd/yyyy") & "#")
        
        Me.Text37.Value = DisabilityChecks + UnemployementChecks + WorkersCompChecks + PayChecks
        Resident_Rent = (Me.Text37.Value) * 0.3

  6. #6
    Join Date
    Sep 2011
    Posts
    5
    Thanks guys!

Posting Permissions

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