Date Validation in a Report

Robert Schumann
Robert Schumann used Ask the Experts™
on
Hi, where am I going wrong with this expression.  It's in a report text field.  The table field "creationdat" is formatted for Date/Time.  The expression is not pulling data created after 10/01/2018.  It should pull all data entered after 10/01/2018.  But it's pulling all data.

=Nz(DSum("amount","PurchaseOrders","[creationdat]>=#10/01/2018# Or [id]=39"),0)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
How about if you format it like this :
=Nz(DSum("amount","PurchaseOrders","[creationdat]>=#" & Format("10/01/2018","mm/dd/yyyy" & "# Or [id]=39"),0)

Open in new window

Robert SchumannFacilities Planner

Author

Commented:
Hi - get an "Invalid syntax"
Robert SchumannFacilities Planner

Author

Commented:
Sorry, The error is " The expression you entered has a function containing the wrong number of arguments.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Your expression seems correct, but shouldn't it be for one ID only, thus And:

=Nz(DSum("amount","PurchaseOrders","[creationdat]>=#10/01/2018# And [id]=39"),0) 

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
i forgot a parenthesis
=Nz(DSum("amount","PurchaseOrders","[creationdat]>=#" & Format("10/01/2018","mm/dd/yyyy") & "# Or [id]=39"),0)

Open in new window

Robert SchumannFacilities Planner

Author

Commented:
That's what I  thought, and I added it, but it still returns all the data for "Amount".  Does not filter out all data before 10/01/2018.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
"[creationdat]>=#10/01/2018#"

Open in new window

will sort anything out before that date.

So look at the other part of the criteria.
Robert SchumannFacilities Planner

Author

Commented:
Each record has an ID field.  This expression will return all values, it is not filtering for records with an ID 39.  Other expressions in the report will return the correct value.  It's just not working when adding the date.

=Nz(DSum("amount","PurchaseOrders","[creationdat]>=#" & Format("09/30/2018","mm/dd/yyyy") & "#or [id]=39 "),0)

If I delete the ID reference it will return the correct value, those with a date after 10/01/2018.  But confused.  It should work, but doesn't.

=Nz(DSum("amount","PurchaseOrders","[creationdat]>=#" & Format("09/30/2018","mm/dd/yyyy") & "#"),0)
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Your string dates doesn't make sense. Originally, they were correct, though hard-coded, now you put a string, cast that to a date, and format that back to a string …

It should go like this:

Dim FilterDate As Date

FilterDate = #10/01/2018#   ' or
FilterDate = DateSerial(2018, 10, 1)

=Nz(DSum("amount","PurchaseOrders","[creationdat]>= #" & Format(FilterDate,"yyyy\/mm\/dd") & "# Or [id]=39"),0)

Open in new window

However, this will sum all  amounts for ID = 39 and all  amounts for any other ID with creationdat from primo October and onwards.

I still believe you need an And.
Robert SchumannFacilities Planner

Author

Commented:
The AND did it!!


=Nz(DSum("amount","PurchaseOrders","[creationdat]>=#" & Format("09/30/2018","mm/dd/yyyy") & "#And [id]=39 "),0)

Thank you both.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial