filtered sum

steve111

Registered User.
Local time
Today, 20:53
Joined
Jan 30, 2014
Messages
429
hi

below is 2 formulas that this forum kindly help me with and works great

what I want to do if possible is add another formula
is count the amount of records that are waiting purchasing up to 91 days from present day
I can have a date field with this in it "<=Now()+91" and filter it but still needs a formula after filter but if possible I would like the formula just to look at the dates to give me the results without filtering
but any of the 2 is great



Status: IIf(Nz([Invoice No],0)>0,"Invoiced",IIf(Nz([invoice old],0)>0,"Invoiced",IIf(Nz([pco No] Or [pco purchased old],"")<>"","parts ordered","Waiting Purchasing")))


Waiting purchase: Sum(DCount("*","order status","Status='waiting purchasing'"))

thanks
steve
 
you should get in the habit of using Date() rather than Now() for these situations - if you wanted to know the number of records greater or equal to today the formula orderdate>=now() would not include any for today. Reason? say orderdate=31/01/2015 and now() is 31/01/2015 11:00:00 - you can see that orderdate (value 42035.0) is less than now() (value 42035.458333)

Also, the bit in red won't work - nz should only apply to a single value
Status: IIf(Nz([Invoice No],0)>0,"Invoiced",IIf(Nz([invoice old],0)>0,"Invoiced",IIf(Nz([pco No] Or [pco purchased old],"")<>"","parts ordered","Waiting Purchasing")))

try this instead

Code:
 Status: IIf(Nz([Invoice No],0)>0,"Invoiced",IIf(Nz([invoice old],0)>0,"Invoiced",IIf(Nz([pco No],"")<>"" Or nz([pco purchased old],"")<>"","parts ordered","Waiting Purchasing")))

and for your question
Code:
Waiting purchase: DCount("*","order status","Status='waiting purchasing' AND orderDate>Dateadd('d',91,Date())")
Note you do not need to sum it

If you wanted 3 months instead of 91 days then you would use

Dateadd('m',3,Date())
 
hi ,

thanks I have put that into the query and it is working.
but does that date in the formula look at "<=Now()+91" as with yours I get 114 records but I think I should get 138

steve
 
difficult to say without seeing what you actually used

I did use> rather than >= which may make a difference and if you've assigned a time element to orderdate then this will also make a difference - see the first paragraph of my previous post
 

Users who are viewing this thread

Back
Top Bottom