Sum of Dates in Report - Advanced Expressions

[kennyrogersjr]

Hi all!



I've been fighting an Access date "math" problem and I hope someone here can assist.

I have two dates: Certification, Recertification. Both are only good for 3 years. If either go a day past 3 years, the document is expired. My report that I have built intends to show the multiple states of expiration and document status, as well as count the amount of documents in a given state. I'm also providing a 30 day warning to show documents that are nearing expiration: Expiring. Both date fields in the primary table can have an empty field. These are not indexed values. The report I am writing the expressions in uses unbound text boxes for the calculations. Forgive my below code if there is any erroneous text, I am writing this from another computer and practically have these expressions memorized.

Expressions I have been attempting to use to solve these questions:

Expiring Certs:   =Sum(IIf(Nz([RecertDate], 0)=0, IIf(DateDiff("d", [CertDate], Now())<1094, IIf(DateDiff("d", [CertDate], Now())>1063, 1, 0), 0), 0))
Expiring Recerts: =Sum(IIf(Nz([RecertDate], 0)<>0, IIf(DateDiff("d", [RecertDate], Now())<1094, IIf(DateDiff("d", [RecertDate], Now())>1063, 1, 0), 0), 0))
Expired Certs:    =Sum(IIf(Nz([RecertDate], 0)=0, IIf(DateDiff("d", [CertDate], Now())>1094, 1, 0), 0))
Expired Recerts:  =Sum(IIf(Nz([RecertDate], 0)<>0, IIf(DateDiff("d", [RecertDate], Now())>1094, 1, 0), 0))

Is there a simpler way to get this report to count these documents than what I'm currently doing? Does anyone have any suggestions for figuring out date value calculations with more than two dates? (Notice that in this instance, I have three dates: Now, Cert, & Recert. You can count a fourth and fifth if you deem the 30 days prior to the Cert and Recert dates.) I'm trying to limit the amount of queries, so running report calculations off a master query allows me to interpret "live" records, not pre-screened records.

Cheers!

Ken

[June7]

What is the issue - error message, wrong results, nothing happens?

Are the expressions returning expected results?

You know some years have 365 days? How did you determine to use 1094 days?

[kennyrogersjr]

What is the issue - error message, wrong results, nothing happens?

Are the expressions returning expected results?

You know some years have 365 days? How did you determine to use 1094 days?

Haha! I figured I'd forgotten to mention something.

Issue: Efficiency and sometimes getting 0 results or all results, even when I'm sure my expression may be right, but it may not be suited for running in a report.

Days:
365 + 365 + 365 + 366 = 1461;
1461 / 4 = 365.25;
365.25 * 3 = 1094.75;
If something is greater than 1094, that means it is 1095 days old or older

What I'm really looking for is a, did you try using this function or functions in this manner, instead? Or maybe a, dude, you need to build a something or other and pull data this way. I'm not a Access reports aficionado, I can't really compare this to anything I've done before.

[June7]

One way to simplify and enhance performance might be to do the DateDiff calcs in query then reference those constructed fields in the Sum(IIf()) conditions.

[kennyrogersjr]

June, thanks for your help and attention!

So basically what I'm trying to avoid is really my only option. Let me throw this at you and see if I can get a good solution going, then.

I have a Master Query. I also have a second query, Active Query, that filters out Decertified documents. I use that Active Query as the record source for my report. This is how I would do it: just copy that Active Query and modify it to get my next result and perform a DCount on the report, do you agree?

Example:
Master Query: 1000 Records
Active Query: 500 Records
Active Filter 1 Query: 10 Records
Active Filter 2 Query: 5 Records
So on and so forth.

I'm not a strong SQL-programmer, so performing calculated values and Total "actions" are not very familiar. But to get the results out of the other queries, I imagine I would need to be able to call them somehow since my record source is using the Active Query, not any of the other queries. Would you do it differently?

[June7]

What exactly are you trying to avoid?

I would probably just use the Master query as report RecordSource and apply filter criteria as defined by user choices on a form and constructed with VBA.

Review http://www.allenbrowne.com/ser-62.html

[kennyrogersjr]

I was attempting to create vital queries only, not "sub"-queries, but I don't see a way around it based on your insight.

I did attempt to create a Search Form and Search Query logically, but I failed. I did use your suggested link a few weeks ago, but since I am rather bad at writing SQL, I never got my search form working. I definitely wanted it to work so I could use that to generate a report and allow the user to print or gain useful info off of it.

I ended up coding some command buttons to filter the data on the report. I also put in unbound text boxes with the aforementioned expressions to show totals for the given criteria. I ended up here trying to see if there was a better way to write the expressions, which I think you're right by suggesting the additional queries for each criteria--I remember doing this in a much larger database and I ended up with over 50 queries. I liked only having 2 queries and was being stubborn about it.

I think I have a way forward. I'm going to create some queries for each criteria and use a DCount expression in the report's unbound fields.

[June7]

The Allen Brown search example doesn't rely heavily on a strong understanding of writing SQL. It is VBA need to really know.

I am not suggesting additional queries for each criteria. I am suggesting one query and using VBA to construct filter criteria.

[ssanfu]

Just an FYI,

I see in your formulas you are using the Now() function. I would use the Date() function.
There is a (slight) difference between Now() and Date() functions.

Now() includes a time component. (example: 01/21/2016 02:24:00 PM)

Date() is just the date (actually, the date with the time component set to 00:00:00 -ie midnight) Example 01/21/2016 00:00:00



There is a slight possibility your calcs could be off by one day. Just be aware..........

[kennyrogersjr]

June,

That's a good suggestion, but I already do that with the command buttons. They perform a filter on the record source (which I did change to the Master Query) and it still works great; I just had to add an additional function to the expression where it skips over the Decertified documents. Now that I've slept on it, I realize that this is a totals issue and I was diving too deep into the weeds. By the looks of it, since my filters work perfect, I'll just throw in a "=Count(*)" expression into an unbound text box and be done.

Steve,

Good point! I think that was why I wasn't getting accurate numbers during verification.

Cheers!

Ken