Date Validation in a Report

How about if you format it like this :

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

Select allOpen in new window

Hi - get an "Invalid syntax"

Sorry, The error is " The expression you entered has a function containing the wrong number of arguments.

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) 

Select allOpen in new window

i forgot a parenthesis

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

Select allOpen in new window

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.

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

Select allOpen in new window

will sort anything out before that date.

So look at the other part of the criteria.

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)

The AND did it!!


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

Thank you both.

You are welcome!