Sum Product Prices Based on DateofPurchase By Ears

[vdanelia]

I want to calculate Product Prices based on Dateofpurchase by ears...
I'm Using Expression:
=DSum("Price","DATA","Dateofpurchase >=#" & Format([Pdate],"mm/dd/yyyy") & "#")

([Price] -In this field are stored the product prices, [DATA] This is main table, [Dateofpurchase] In this field is stored the date/time of the product, when it was purchased, [Pdate] - This is unbound combo-box in which i have the values)

In Pdata combobox I gave values of years: 1/1/2011, 1/1/2010, 1/1/2009 and AllYears


If Select 1/1/2009 - It Must Show Only the Sum of the products which was bought only in that year (2009)


Please

If Select 1/1/2010 - "The Same" (2010)

If Select 1/1/2011 - "The Same" (2011)

And If Select AllYears It Must Show The Sum of products which was bought from 2009- to current date


To admin or moderator: Please I have a mistake in the title of the thread "Sum Product Prices Based on DateofPurchase By Ears" Change it to: Sum Product Prices Based on DateofPurchase By Years" I misspelled wrote Ears instead of Years


Thank you in Advanced

 

[pbaldy]

One solution that comes to mind is to have 3 columns in your combo; the first is your existing values for display, then hidden would be beginning and ending dates your DSum() could use. For the all years selection, use dates before and after any possible dates in the table.

 

[vdanelia]

Hello, Thanks for your reply...
It's very good idea, but how to realize it...
Could you explain with examples...

Thanks in Advanced

 

[vbaInet]

I would go with Paul's idea. So the three columns will look like this:

PDate     |     Start     |     Finish
================================
2009      |     2009      |     2009
2010      |     2010      |     2010
2011      |     2011      |     2011
AllYears  |     2009      |     2011

You can then use:

=DSum("Price","DATA","Year(Dateofpurchase) BETWEEN " & Year([Pdate].Column(1)) & " AND " & Year([Pdate].Column(2)))

To get the Start and Finish years, you would have to fill the combo box in code.

In any case, here's another way:

=DSum("Price","DATA","Year(Dateofpurchase) BETWEEN " & IIF([PDate] <> 'AllYears', Year([Pdate]), Year(DMin("DateofPurchase", "Data"))) & " AND " & IIF([PDate] <> 'AllYears', Year([Pdate]), Year(DMax("DateofPurchase", "Data"))))

 

[vdanelia]

Greatest Thanks, Worked Perfectly................. Thank You Very Much!

 

[vbaInet]

Glad we could help!