Date Validation in a Report

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)
Robert SchumannFacilities PlannerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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

0
Robert SchumannFacilities PlannerAuthor Commented:
Hi - get an "Invalid syntax"
0
Robert SchumannFacilities PlannerAuthor Commented:
Sorry, The error is " The expression you entered has a function containing the wrong number of arguments.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gustav BrockCIOCommented:
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

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

Open in new window

0
Robert SchumannFacilities PlannerAuthor 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.
0
Gustav BrockCIOCommented:
"[creationdat]>=#10/01/2018#"

Open in new window

will sort anything out before that date.

So look at the other part of the criteria.
0
Robert SchumannFacilities PlannerAuthor 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)
0
Gustav BrockCIOCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert SchumannFacilities PlannerAuthor Commented:
The AND did it!!


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

Thank you both.
0
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.