DSum With Multiple Criteria and Date Value

[Okestra007]

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

    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

 

[vbaInet]

You are getting four lots of data from the same table with same criteria. Why not use a subform?

 

[bwellbor]

I think you've got some syntax problems in your DSum functions. Your Field Names and Table Names should be inside quotation marks. When doing multiple criteria, the AND part should also be inside quotation marks. See below corrections in red.

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 & "#")


See if that helps any.

 

[Okestra007]

I think you've got some syntax problems in your DSum functions. Your Field Names and Table Names should be inside quotation marks. When doing multiple criteria, the AND part should also be inside quotation marks. See below corrections in red.

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 & "#")

See if that helps any.

I think you have list separator errors with your solution.

Thanks

 

[bwellbor]

I think you have list separator errors with your solution.

Thanks

Oops! You're right. I forgot to put the & in when I moved the And inside the quotation marks. Updated:

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 & "#")

Thanks!

Is this working for you?

 

[Okestra007]

Oops! You're right. I forgot to put the & in when I moved the And inside the quotation marks. Updated:

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 & "#")

Thanks!

Is this working for you?

First problem, Access 2010 can't locate the Table with the " ", without it or with [ ] will work though.

Second problem, once I took out the " ", I now get a Run-time error '2428': (You entered an invalid argument in a domain aggregate function)

 

[bwellbor]

If Income_Activity is the name of your table, it needs to be in quotes. And it's ok if it looks like this with the brackets: "[Income_Activity]". If it's a string variable that contains your table name, then it shouldn't be in quotes or brackets.

If Access can't find your table when it's in quotes, make sure you spelled it correctly (and that the table exists).

 

[vbaInet]

Like I mentioned, you're wasting resources calling DSum four times on the same record. Just use a subform, and set the Control Source to point to the controls.

Or call DSum() ONCE, concatenate your values and split it to get the four parts.

 

[Okestra007]

Like I mentioned, you're wasting resources calling DLookup four times on the same record. Just use a subform, and set the Control Source to point to the controls.

I am willing look into Subform, but I have come too far into this ideal not to try to get it to work.

 

[Okestra007]

Or call DSum() ONCE, concatenate your values and split it to get the four parts.

Concatenating DSum might be a problem for me, can you please offer an example?

 

[Okestra007]

If Income_Activity is the name of your table, it needs to be in quotes. And it's ok if it looks like this with the brackets: "[Income_Activity]". If it's a string variable that contains your table name, then it shouldn't be in quotes or brackets.

If Access can't find your table when it's in quotes, make sure you spelled it correctly (and that the table exists).

It doesn't work with Access 2010. I am getting what I need if I am using just the Client_Key criteria, but not when I add the Income_Period criteria.

 

[vbaInet]

Actually I can see that you are eventually adding all the values so in the Control Source of Text37 (aircode):

=DSum("[field1] + [field2] + [field3] + [field4]", "[COLOR=Red]TableName[/COLOR]", "[COLOR=Red]Criteria[/COLOR]")

In the Control Source of Resident_Rent:

=[Text37] * 0.3

What are the data types of Client_Key and Income_Period and I'll tell you what you're doing wrong?

 

[Okestra007]

Problem Solved (Too many mistakes and Errors)

    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

 

[Okestra007]

I am sorry, but you were right about the Table name.
Thanks

If Income_Activity is the name of your table, it needs to be in quotes. And it's ok if it looks like this with the brackets: "[Income_Activity]". If it's a string variable that contains your table name, then it shouldn't be in quotes or brackets.

If Access can't find your table when it's in quotes, make sure you spelled it correctly (and that the table exists).

 

[Okestra007]

Thanks guys!

 

[vbaInet]

Problem Solved (Too many mistakes and Errors)

    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

Did you see the aircode I gave you? Or you're happy with your solution?

 

[Okestra007]

I am happy with what I have, but I will surely give your ideal a play too, as that appears to be much simpler.

Did you see the aircode I gave you? Or you're happy with your solution?

 

[vbaInet]

As long as you're happy