Performing a date calculation

[gary g]

I wasn't sure in which forum to ask this question, but here goes. I need to know the formula for determining the number of days between the "current" date and an earlier "fixed" date. The report shows cases that are still open in our office.

Besides other criteria, I want the report to indicate the number of days each case has been open based on the date filed (DateReceivedinADR in the table) and the date the report is being run (which is ever-changing).

In the Page Header of the report I created a label titled Case Age. Under this, in the report's detail section, I created an unbound text box and inserted this formula as the control source: =[CurrentDate]-[DateReceivedinADR]. I was hoping CurrentDate would pick up the date from the PC and, in comparing it to the other fixed date, it would automatically make the calculation, but it didn't work.

Any suggestions on how I can make this work, or where I went wrong? Thanks.

 

[pbaldy]

The function to get the current date from the PC is Date()

 

[gary g]

Thanks. However, if I enter the following formula: [Date()]-[DateReceivedinADR] into the control source for the unbound text box, then open the report, I get a date parameter dialogue box asking for dates. I just want to capture the number of days that have elapsed between two different dates (current date and DateReceivedinAD).

I actually all of this has been working fine for the last three years, but after converting to Access 2007 from 2003, a few glithches have cropped up. This is one of them. Since its been so long since I I've created the original database and reports I have forgotten what I did the first time around.

Any other suggestions? And thanks for quick response BTW. This has always been a great forum.

 

[pbaldy]

You don't want it bracketed. That makes Access think it's a field name, which it then can't find.

=Date()-[DateReceivedinADR]

 

[gary g]

I realized that I included the initial brackets after I sent the reply and went back and removed them around Date(). I ended up with the same formula as the one you have. However, I still ended up with the same issue. When opening the report I still get the date parameter dialogue based on the [DateReceivedinADR] table field.

I wish I could remember how I set this up years ago. I know it's a simple result based on a simple calculation. Just thinking out loud, I presume there is no need to place the unbound text box that I referred to in my first message anywhere other than on the report, correct? That's where the calculation is being made, not the form or query. Or am I missing an obvious step somewhere? It's been a long day!

I don't mean to tie up your time with this, but I'm on a mission to figure this out all over again. Especially when it worked so well for so long the first time around. The answer is out there

 

[pbaldy]

If you're getting a parameter prompt on that, it must not be in the report's source query. I would probably make that a calculated field in the underlying query, but it should work either way, as long as that field is in the query.

 

[RainLover]

Is "DateReceivedinADR" part of your record source.

To test create a new Text Box and enter DateReceivedinADR. It should show the Date.

 

[gary g]

Sorry for the late response, but got busy at work late yesterday. I actually created a brand new query and report hoping that the date calculation formula would work from scratch. But having the same problem. Which is strange, considering the same exact formula works just fine when I used it in a simple test DB I created. However, when I attempt to use the formula in the report in question it will not work. Either I end up with a date parameter dialogue when opening the report, or the words #Error on the report.

Just to reiterate, the date field in question (DateReceivedInADR) is part of the report's underlying query. The dates themselves appear as they should in the report. However, when I open the report in Design View, open the Property Sheet for this particular text box and enter the following formula after Control Source: =Date()-[DateReceivedInADR] either I get an error message on the report for this field, or the date parameter dialogue box appears.

I am willing to send screenshots of the query and report (in both design and report views) if someone wants to take a look at it and maybe figure out what the issue is. Thanks again.

 

[pbaldy]

Screenshots would help, the db itself would be better. One thing to make sure of is that there isn't a textbox with the same name, which will sometimes confuse Access.

 

[gary g]

I finally got it figured out. I went to the underlying query and placed the expression in the field line of a blank column and saved it that way. Going back to the report's design view I dragged the newly created expression to the page, created a label for it, and all worked out.

Thanks so much for the help, and the timely responses. I appreciate it very much. More importantly, I made my own help file to refer to so when I forget again in three years at least I can go back and check my notes.

 

[pbaldy]

Happy to help. I do that, but then I forget where the notes are...or that I made any notes.

 

[RainLover]

Lesson Learnt.

Do everything you can in the Query not the Form or Report.

Also look at the Function DateDiff. This is what you really should be using.