J
Juli Intern
I'm using those functions you gave me very extensively, thank you a lot.
Now I have another problem.
I am building form that displays data to compare two years. Some of the
controls are calculated some - are using DLookUp.
One of the controls has to be a cumulative percentage for the year. So I'll
have to refer to the data that is not on the form. I think it's better to do
this calculation in the VB screen but have very limited knowledge on how to
use it.
So, if it were the number for just January 2006 month, the formula in the
DateSource would look like that:
=(DLookUp("[MaxOfSumOfaccount_0_99]","[Query1]","[Date]=[StartDate]"))/(DLookUp("[Stmts_mailed]","[Stmts_mailed]","[Date]=[StartDate]"))
It's actually working and brings me the right value.
To find this value for the month of February I would use
=(DLookUp("[MaxOfSumOfaccount_0_99]","[Query1]","[Date]=LastWorkDay(DateSerial(Year([StartDate]),Month([StartDate])-1,0))"))/(DLookUp("[Stmts_mailed]","[Stmts_mailed]","[Date]=LastWorkDay(DateSerial(Year([StartDate]),Month([StartDate])-1,0))"))
But for some reason it's not working and don’t bring me any results not even
‘error’
And eventually, I don't need to see those numbers for each of the month I
just need to see the summary of those numbers for 12 previous months (going
backwards from [StartDate] which can be chosen through Calendar Active
control).
If some one could help me in this mess I would be happy.
Thanks
Now I have another problem.
I am building form that displays data to compare two years. Some of the
controls are calculated some - are using DLookUp.
One of the controls has to be a cumulative percentage for the year. So I'll
have to refer to the data that is not on the form. I think it's better to do
this calculation in the VB screen but have very limited knowledge on how to
use it.
So, if it were the number for just January 2006 month, the formula in the
DateSource would look like that:
=(DLookUp("[MaxOfSumOfaccount_0_99]","[Query1]","[Date]=[StartDate]"))/(DLookUp("[Stmts_mailed]","[Stmts_mailed]","[Date]=[StartDate]"))
It's actually working and brings me the right value.
To find this value for the month of February I would use
=(DLookUp("[MaxOfSumOfaccount_0_99]","[Query1]","[Date]=LastWorkDay(DateSerial(Year([StartDate]),Month([StartDate])-1,0))"))/(DLookUp("[Stmts_mailed]","[Stmts_mailed]","[Date]=LastWorkDay(DateSerial(Year([StartDate]),Month([StartDate])-1,0))"))
But for some reason it's not working and don’t bring me any results not even
‘error’
And eventually, I don't need to see those numbers for each of the month I
just need to see the summary of those numbers for 12 previous months (going
backwards from [StartDate] which can be chosen through Calendar Active
control).
If some one could help me in this mess I would be happy.
Thanks