DSum With Specific Criteria

[vdanelia]

Hello Dear friends, I need your help...
I have a database in which I sum Price, I'm using the VBA

=DSum("UnitPrice","DATA","Dateofpurchase >= #01/01/1998# AND Status Not In ('Removed')")

I works but I need to add some criteria.. This variant sums Unit price from 1998 till now. I added some fields with values in my database and want to sum by them

Field (Account) with values BUDGET1, BUDGET2 and Field (AccType) With Values 013, 065

VBA Must DSUm According Unitprice + Account -Budget1, Acctype - 013 and Dateofpurchase as it is in my old code


I Did something like:

=DSum ("Unitprice", "DATA", "Account = 'Budget1'", "AccType = '065'") But this not working I have something wrong

Please Help

 

[lcook1974]

you should set up a strWhere varible and build your string there.

Dim strWhere as string
strWhere = "[Dateofpurchase] <= # " & 01/01/1998 & "#" & " AND "
strWhere = strWhere & "[Status] NOT IN ('Removed')" & " AND "
strWhere = strWhere & "[Account] IN ( 'value1', 'value2', etc...)

Then do your =DSum ("Unitprice", "DATA", strWhere)

You could make this a little more interactive using a multiple select text box for you account...then it could something like IN (me.lstbox1)

not tested...just a thought.

 

[vdanelia]

Hello lcook1974 Thank you for your reply...
I think that this VBA is complicated variant, I need something easy like:

=DSum("Unitprice","DATA","Account= 'Budget1'") This is working, but i just want to add one Field With Value: AccType = '013'")
I added this, but something is wrong, I want them both to work

 

[lcook1974]

Try this:

=DSum("Unitprice","DATA","Account= 'Budget1' AND AccType = '013'")

 

[vdanelia]

Thanks lcook1974 Worked perfectly, This is what i needed
Greatest Thanks

 

[lcook1974]

Happy to help...

Larry

 

[vdanelia]

Hello Larry, I have one question if you don't mind help

I have this VBA they're working perfectly, now a Question
How i can =DSum everything as One VBA Code

=DSum("Unitprice","DATA","Account= 'Budget1' AND AccType = '013'")
=DSum("Unitprice","DATA","Account= 'Budget1' AND AccType = '064'")

=DSum("Unitprice","DATA","Account= 'Budget2' AND AccType = '013'")
=DSum("Unitprice","DATA","Account= 'Budget2' AND AccType = '064'")

I'm Making the statistics page and need all kind of calculations
Thank you in Advanced

 

[lcook1974]

Try

=DSum("Unitprice","DATA","[Account] IN ('Budget1', 'Budget2') AND [AccType] IN ( '013','064')")

 

[vdanelia]

Greatest Thanks Larry
Works Perfectly