Calculate number of days between two dates in a Report

[Jane@College]

I have been checking out another thread, tried the formula but it does not give me a number of days. I have tried the DateDiff("d",[Date 1],[Date 2]), not sure what the d tag works on and if that is what I need. It returns nothing and also asks for a Parameter Value on the Date 1 at the start of the report running, which usually means it has not been set up correctly.

Would appreciate some help!

Jane

 

[boblarson]

Yes, DateDiff is what you want. You need to give it the right field names (not the control names) and the "d" means days where you can use "m" for months, "q" for quarters, "yyyy" for years, "h" for hours, "n" for minutes, and "s" for seconds.

So the syntax in a control source on a report is

=DateDiff("d", [FieldName1], [FieldName2])

 

[Jane@College]

Yes I have entered all that OK, thanks for telling what the d refers to. However I don't get a calculation returning on the report. I have changed the format to a general number, but nothing is showing up?

Jane

 

[Jane@College]

It does help if you make sure you have typed in the full label of the dates! It works now, thanks for the help!

Jane

 

[Joebates26]

What if [FieldName2]) is open meaning a date has not been put in the field because it is still open. I have Invoice Recieved as a field 1 and Invoice Processed as field 2. Field 2 is more than likely to remain open a lot of times but I still want the DateDiffer to tell me how many days the Invoice has been open from when the Invoice was recieved to when it has been processed even if it is open. Any help would be appreciated.

Thanks,
Joe

 

[boblarson]

What if [FieldName2]) is open meaning a date has not been put in the field because it is still open. I have Invoice Recieved as a field 1 and Invoice Processed as field 2. Field 2 is more than likely to remain open a lot of times but I still want the DateDiffer to tell me how many days the Invoice has been open from when the Invoice was recieved to when it has been processed even if it is open. Any help would be appreciated.

Thanks,
Joe

You can use an IIF statement for that.

=IIf(IsNull([InvoiceProcessed]),DateDiff("d", [InvoiceReceived], Date()), DateDiff("d", [InvoiceReceived], [InvoiceProcessed]))

 

[Joebates26]

Bob,

Thank you for posting the following statement. I was able to get it to work once and I forgot to save it and now I cannot get it to work anymore. I have tried for two hours. I am uploading my Database. Feel free to take a look if you wish. Field 1 is Date Rejected and Field 2 is Date Processed. I would like this Report to only work on Rejects. Again any help would be greatly appreciated.

Thanks,
Joe

 

[boblarson]

Which report? You have three and it isn't obvious which one you are talking about (Locate by Auditor Report, Locate by Invoice Date Report, or Locate by Invoice Number Report).

 

[Joebates26]

Bob,

I apologize for that. Is it not possible to create a brand new query called "Locate by Outstanding Days" and build a report off that (It would be called the same thing)? If so, I am having a hard time building the query for the new report. Is there a simpler way of achieving my goal? Again, thank you very much for all of the help so far.

Joe

 

[REIGNMANIAC]

I am a total newbie to Access, so please keep in mind that any responses should be in terms a 5 years could understand…lol!!

I have a table with ten columns of data, the important ones are:
Name Date Worked Department
Robin 10-01-1901 Science
Mark 01-06-1950 Research
Robin 05-28-1967 Science
Mark 02-28-1988 Research
Robin 08-31-2011 Science


I have a query that runs, pulling the name, first date worked, last date worked, and frequency. The first and lastof dates are pulling from the same column in the the table
Name firstofdate lastofdate count
Robin 10-01-1901 08-31-2011 3
Mark 01-06-1950 02-28-1988 2

I have tried to create an expression that will find the difference between the two dates, using datediff that would look something like this

Name firstofdate lastofdate count Days Passed
Robin 10-01-1901 08-31-2011 3 40146
Mark 01-06-1950 02-28-1988 2 13932

My expressions looks like this =DateDiff("d", [firstofdate], [Lastofdate]), which I pulled from office.microsoft.com, but I get an expression error.

In the query the first and last of dates are being pulled from the same column in the table, does that have something to do with my expression error?