Multiple Criteria in DSum expression (1 Viewer)

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #1

I am currently programming a report that needs to spit out all of the money paid to a specific client. The Dsum function I used was:

=DSum("CashforVisit","Financials","Financials.SSN= " & [SSN])

Which worked excellent. However, I need a way to add another criteria to the DSum function, specifically that the payments happenned in 2011. This report grabs information from table Financials, which documents every payment, the SSN of the recipient, and the date of the payment.

Values to sum: CashforVisit
Table name: Financials
Criteria1:"Financials.SSN= " & [SSN]
Criteria2: DateofVisit >= 1/1/2011
Criteria3: DateofVisit <= 12/31.2011

How would I do this? I have been trying for a while, I would love some help

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #2

=DSum("CashforVisit","Financials","[SSN]= " & [SSN] & " AND [DateofVisit] Between " & DateSerial(Year(Date()), 1, 1) & " And " & DateSerial(Year(Date()), 12, 31))

This way you can use this over again next year and subsequent years without making changes to it.

 

Last edited: Jan 4, 2011

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #3

Thank you so much!

Unfortunately, it is giving me the error "The expression you entered contains a function with the wrong number of arguments". What would be the source of that error?

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #4

Did you try the code before or after I made the edit which added the ending parens for YEAR in both of the DateSerial functions? I had forgotten to include those.

 

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #5

The code now runs, but does not successfully remove the payments that were made in different years. Any ideas? I really appreciate this help.

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #6

Is DateofVisit really a date field?

 

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #7

You nailed that one, I changed [DateofVisit] to be a date field rather than text. I fear my inexperience may be showing. However, After changing this and providing new inputs, it is still not working. The code runs but does not provide any values for an output, likely indicating that it cannot find anything that meets the criteria. Any ideas on what else could be the problem?

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #8

Are you sure you have the right dates? You are asking me for 2011 but there is only 4 days gone of 2010. Perhaps you meant you wanted to look for LAST year (2010)?

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #9

Oh, and the field SHOULD be a date/time field (with format set to Short Date) and NOT text.

 

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #10

I'm sure that the dates are right, the system has not been implemented yet, I only have a few test inputs. Thank you again for your help

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #11

FlyingDisc said:

I'm sure that the dates are right, the system has not been implemented yet, I only have a few test inputs. Thank you again for your help

Click to expand...

What do you have for test inputs (what dates) and are you sure it is for the appropriate SSN?

 

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #12

Test inputs:

First Name , Last Name, SSN, DateofVisit, CashforVisit
This , Works, 123456777, 1/1/2010, 101 (should not be counted)
This , Works, 123456777, 1/4/2011, 100 (should be counted)
Testing, Testing, 123456789, 1/5/2011, 500 (should be counted)
Testing, Testing, 123456789, 1/6/2011, 100 (should be counted)

This is in table financials, which is linked to table Parent Financials, which has SSN as a primary key.

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #13

Is SSN text or numeric in the table's field datatypes?

(also - it is not good using SSN for primary key by the way - for one they are reused by the SS Administration for other people and also I don't know what kind of controls you have in place but the law is getting such that you have to protect those and Access databases can grow legs and walk - actually someone can just copy it to a USB drive and take it with them which is very unsecure).

 

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #14

The SSN is a number data type

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #15

Where is the control which you are trying to put the value? Is it in the report's DETAIL section? Or do you have a group on SSN and it is there?

 

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #16

And in regards to security, I actually did raise personal objections to storing the SSNs in the DB, but the front ends are all password protected and the backend is on secure server. My boss decided that this was better that the stack of paper in an unlocked drawer method we were employing before.

 

F

FlyingDisc

Registered User.

Local time

Yesterday, 23:05

Joined

Jun 28, 2010

Messages

11

• Jan 4, 2011

• #17

It is in the report's detail section

 

boblarson

Smeghead

Local time

Yesterday, 23:05

Joined

Jan 12, 2001

Messages

32,059

• Jan 4, 2011

• #18

So is the control on the report named [SSN]? If so, change the control name to txtSSN and then, if you have name autocorrect (also known as name autocorrupt) enables (which I would suggest disabling) it will automatically change your code to use txtSSN so you'll want to change it back to the red:

=DSum("CashforVisit","Financials","[SSN]= " & [SSN] & " AND [DateofVisit] Between " & DateSerial(Year(Date()), 1, 1) & " And " & DateSerial(Year(Date()), 12, 31))


On reports, if you use something in a control source or calculation it doesn't like it if the name of the field and the name of the control are the same.