Another Subtotal Formula in Subform Problem ( (1 Viewer)

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
Another Subtotal Formula in Subform Problem (DateDiff Forumula)

I have another sub-form subtotaling issue.

I am entering work that is task based, time based or other.

The below sub-total formula was working until I came to a record that had no time based work at all. The sub-total control for time for this record is blank and as a result, the grand total in the main form is also blank

Code:
=Sum(IIf(DateDiff("n",[StartTime],[EndTime])/60<1,1*50,DateDiff("n",[StartTime],[EndTime])/60*50))

I tried amending the formula for multiple criteria but I am getting the same results.

Code:
=Sum(IIf(DateDiff("n",[StartTime],[EndTime])/60=0,0*50,IIf(DateDiff("n",[StartTime],[EndTime])/60<1,1*50,DateDiff("n",[StartTime],[EndTime])/60*50)))

Any suggestions?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 15:16
Joined
Jan 14, 2017
Messages
18,186
Try using the Nz function to handle null values. See if this works

Code:
=Sum(IIf(DateDiff("n",(Nz([StartTime],0),Nz([EndTime],0))/60<1,1*50,DateDiff("n",Nz([StartTime],0),Nz([EndTime],0))/60*50))

If not, add another IIf clause specifically for null values
 
Last edited:

June7

AWF VIP
Local time
Today, 07:16
Joined
Mar 9, 2014
Messages
5,423
I tested this expression and where record does not have date/time values Null is returned. The Sum still calculates. Nulls are ignored in aggregate calcs.
 

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
I'm not getting the formula to work, I'm getting invalid dot or operator message. I've gone over the formula a few times now and can't find the problem
 

isladogs

MVP / VIP
Local time
Today, 15:16
Joined
Jan 14, 2017
Messages
18,186
If you are referring to my reply, I've just added a missing comma before the first Nz. Try it again now.
NOTE: Unlike June, I didn't test your expression ...or mine.
 

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
Ok...I'm getting somewhere. The formula is being accepted but the total is incorrect. So I will keep working with the NZ function to see if I can get the end correct end result.

Thank you for your help.
 

isladogs

MVP / VIP
Local time
Today, 15:16
Joined
Jan 14, 2017
Messages
18,186
If you get stuck, post some sample data together with the required results, so one of us can assist further if needed
 

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
What's happening is that the formula is adding 50 to every line in the record that is NOT time based. So the record that had no time based work, but 4 task based work, is returning a result of 200.00 in the time sub-total

I need to find a way of saying if the time controls are blank, to return 0, otherwise, do the calculations
 

isladogs

MVP / VIP
Local time
Today, 15:16
Joined
Jan 14, 2017
Messages
18,186
OK -try what I suggested as an alternative in my first reply

Code:
=IIf(Nz(StartTime,0)=0 Or Nz(EndTime,0)=0,0,Sum(IIf(DateDiff("n",[StartTime],[EndTime])/60<1,1*50,DateDiff("n",[StartTime],[EndTime])/60*50)))

The above is aircode so check for possible errors.

If that still fails, please post sample data and expected results
 
Last edited:

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
Thank you! That last formula worked.

I had just finished typing out a long response to your previous post with examples and everything and then accidentally clicked refresh page and lost everything I type :(

Thanks again...you've helped me out a lot!
 

isladogs

MVP / VIP
Local time
Today, 15:16
Joined
Jan 14, 2017
Messages
18,186
Phew! Glad it worked for you.

BTW Having lost posts by doing that myself too many times, I now write all lengthy answers in Notepad.
 

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
Will do that from now on!

On another note, please let me know if I am posting the formulas correctly - do I wrap code, or quotes around the formulas before posting?
 

isladogs

MVP / VIP
Local time
Today, 15:16
Joined
Jan 14, 2017
Messages
18,186
Just click the code (#) button on the post toolbar then paste code between the code tags.
Code in post #1 was fine
 

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
Hi

Further to this earlier post, I have encountered a slight problem. Due to the number of fields in my subform, datasheet view was too wide and difficult to navigate and simply did not look good.

I changed the subform from datasheet to single form and once I did this, the sub-total on the main form is not showing the total of all the records in the subform, only the total of the focus record.

The Invoice report is working just fine but I would like to see the actual total while I am keying the info as a means of double checking without having to go to report view.

Is there a different formula for subtotaling single form subforms?
 

June7

AWF VIP
Local time
Today, 07:16
Joined
Mar 9, 2014
Messages
5,423
I did a test with simple aggregate (Count(*) and Sum([Rate])) in form header. I get same correct result in Single or Continuous view.

If you want to provide db for analysis, follow instructions at bottom of my post.
 

Tophan

Registered User.
Local time
Today, 11:16
Joined
Mar 27, 2011
Messages
362
Thanks for your reply. The problem I'm having is that there are 3 subform subtotals on the main form...one totaling time based work, one totaling task based work and one totaling other work. When I enter time based work in one record, and task or other work in the next record, the subtotal for the time based doesn't show when the focus is on a new record for task or other work.

When I had the subform as a datasheet the subtotals were working but once I changed to single form view the subtotals don't reflect the actual invoice value.

It's just a little quirk I wouldn't mind fixing but it is not in anyway affecting the final result which is the invoice report.
 

Users who are viewing this thread

Top Bottom