Calculating quarterly values from YTD values

[TonyWilliams via AccessMonster.com]

I have a query that calculates a quarterly value from year to date values.
Each quarter the user inputs YTD values and the query calculates the
quarterly value by looking for the previous quarters’ YTD value and
subtracting it from this quarter's YTD value. If it is March then the
quarterly value is taken as the same as the YTD value. The form has a control
with the current quarter which is txtqtr2 (which is selected from a drop down
list by the user) and a control with the previous quarter (calculated as
=DateAdd("m",-3,[txtqtr2]) ) An example of one of the expressions in the
query is this:

IIf(Month([txtmonthlabel])=3,[txtDomidpartytd],IIf([txtMonthLabel]=[Forms]!
[frmMain]![SubForm1]![txtqtr2],[txtDomidpartytd],0)-DLookUp("[txtDomidpartytd]
","[tblmaintabs]","[txtMonthLabel]=[Forms]![frmMain]![SubForm1]![txtqtr3]and
txtCompany='" & [txtCompany] & "'"))

In this instance this expression looks at the YTD values (txtdomidpartytd)
and the populates a field in the table called txtdompartid.

This works fine EXCEPT if at a quarter other than March eg June, there is a
new company added and as there is no previous quarter for the DLookup the
quarterly value can't be calculated and the value of the field is blank.

How can I amend this expression to populate the value of the quarterly value
equal to the YTD value only if there are no previous YTD values?
Thanks
Tony

 

[ghetto_banjo]

You could use Nz functions around the DLookups to return zeros instead
of nulls.

i.e.

Nz(Dlookup(......), 0)


the above will return zero in the event the DLookup part returns
Null. So if a company didn't have a previous quarter's total, you
can still subtract zero from the current quarter's total and get your
result.

 

[TonyWilliams via AccessMonster.com]

Thanks for that tip I'll try that and get back if I have any problems.
Thanks again.
Tony

 

[TonyWilliams via AccessMonster.com]

Hi I've tried your suggestion and although it works where a company doesn't
have a previous quarter's YTD data, where a company does have a previous
quarter's YTD data it populates the quarterly figure with "0" and not the
true quarterly figure.
So nearly there but not quite. Any ideas?
Thanks for your help
Tony

 

[TonyWilliams via AccessMonster.com]

Solved the problem, incorrect field name. So your solution worked, thanks a
lot.
Tony

Hi I've tried your suggestion and although it works where a company doesn't
have a previous quarter's YTD data, where a company does have a previous
quarter's YTD data it populates the quarterly figure with "0" and not the
true quarterly figure.
So nearly there but not quite. Any ideas?
Thanks for your help
Tony

You could use Nz functions around the DLookups to return zeros instead
of nulls.

[quoted text clipped - 7 lines]

can still subtract zero from the current quarter's total and get your
result.

 

[ghetto_banjo]

Great, glad to help.