Dsum Syntax Help (1 Viewer)

[Drand]

Hi all

I have a Dsum calculation as follows:

JanCofAct = Nz(DSum("[CostIncGST]", "QryStockOutThisYearForBudget", "[Grp] = 'Coffee' and MonthName = 'Jan'"), 0)

I need to add a year criteria where MyYear (as an integer) is derived from a user input box when the form loads.

So what I am trying to achieve is

JanCofAct = Nz(DSum("[CostIncGST]", "QryStockOutThisYearForBudget", "[Grp] = 'Coffee' and MonthName = 'Jan'"), 0) and MyYear = the inputbox result
but I do not know the correct syntax for this.

Would appreciate your assistance

Many thanks

 

[theDBguy]

Hi. Just a guess, but maybe you could try something like:

JanCofAct = DSum("[CostIncGST]", "QryStockOutThisYearForBudget", "[Grp] = 'Coffee' And MonthName = 'Jan' And MyYear=InputBox(Enter Year)")

Hope that helps...

 

[Pat Hartman]

JanCofAct =

Really? You want to do a dSum() for every expense for every month by year? There are much better ways. Why not tell us what your objective is instead of asking us how to fix your solution which may not even be what you should be doing?

For starters, a cross tab that took Year as an argument would give you ALL expenses as a list with 12 months across the top. Tell us what you want and we'll help you get there

 

[arnelgp]

Dim lngYear As Long
lngYear = Val(InputBox("Enter Year", , Year(Date)))
JanCofAct = DSum("[CostIncGST]", "QryStockOutThisYearForBudget", "[Grp] = 'Coffee' And MonthName = 'Jan' And [Year] = " & lngYear)

 

[Drand]

Thanks arnelgp

Perfect for what I needed!