DSUM with multiple criteria (1 Viewer)

gladiater · Mar 23, 2013

I am trying to get deposit amount for the current month but results are in accurate i am using this

DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))

plz help

pr2-eugin · Mar 23, 2013

Hello gladiater, Welcome to AWF..

DSum or any Domain function can take in multiple criteria, however they needs to be properly concatnated. Also based on their type, proper concatenation operators are to be used.. But based on the Code you have, I think it is a bit more trickier to get what you want.. So IMO, create a Query that will get the information and just use a DSum on the Query..

Code:

SELECT Amount FROM income
WHERE Month(dep_date) = Month(Date()) AND Year(dep_date) = Year(Date()) AND trans_type = 'Deposit';

Name the Query, sumDepositQry
Then use the DSum as..

Code:

DSum("[Amount]","[sumDepositQry]")

gladiater · Mar 24, 2013

Thanks for ur help dear it worked

Solo712 · Mar 24, 2013

gladiater said:
I am trying to get deposit amount for the current month but results are in accurate i am using this

DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))

plz help

To get a better handle on it I would break it down first:

Code:

Dim MDate as Date, thisMonth as string
'
' define the date period 
MDate = DateSerial(Year(Date), Month(Date), 1)
thisMonth = "[dep_date] BETWEEN #" & Format(MDate, "mm/dd/yyyy") & _
       "# AND #" & Format(DateAdd("m", 1, MDate) - 1, "mm/dd/yyyy") & "#"
 
[I]' [/I]then use it in the aggregate function
[I]..DSum("[Amount]","[income]","[trans_type] = 'deposit' AND " & thisMonth[/I])

Best,
Jiri

DSUM with multiple criteria (1 Viewer)

gladiater

New member

pr2-eugin

Super Moderator

gladiater

New member

Solo712

Registered User.

Similar threads

Users who are viewing this thread