Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding Min/Max days between dates - Need help

Status
Not open for further replies.
Mar 9, 2006
93
CA
I am trying to find the MIN and MAX number of days between an open and closing date. Everything seems to work OK except when the MIN or MAX difference between dates is 0. Here is an example:

Item OpenDate CloseDate
1000 01/01/07 01/11/07
1000 05/05/07 05/10/07
1000 10/10/07 10/10/07

In this case I would get Max days of 10 and MIN days of 5. It looks like the MIN or MAX will not include the number of days if the number is equal to 0.

Thanks
Matt
 





Hi,

"when the MIN or MAX difference between dates is 0. Here is an example"

Your example does not seem to support the case that you stated.

Exactly WHAT is not working when, "the MIN or MAX difference between dates is 0"? There is no example of what is not working.



Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
Skip,
Why do your posts nearly always have about 5 blank lines at the top? Is this by design or are you copying and pasting from some other app?

BTW: I agree that Matt hasn't provided enough information for anyone to make more than a WAG.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 




I have a habit, and it's nothing that I wear.

Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
Here I will try and explain this again. This information is in an access database. If I have a table in my database called 'records'. The table has the following information in it:

Item OpenDate CloseDate
1000 01/01/07 01/11/07
1000 05/05/07 05/10/07
1000 10/10/07 10/10/07

Now I am trying to find the minimum number of days between the OpenData and CloseDate. In this case the minimum number of days should be 0 (10/10/2007 - 10/10/2007). The problem is when I use the min sql function it returns the number 5 as the minimum number of days. It seems as if it ignores records where the OpenData and CloseDate are equal. Here is a sample of the SQL I am using.

SELECT Min(DateDiff('d', OpenDate, CloseDate))
FROM records WHERE ItemNumber = '1000'
 
I just created the same table with the same records and the same SQL returned 0. I'm not sure why your query didn't work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
If the fields are dates and the data you provided to us is accurate and the SQL you provided to us is accurate then you will get accurate results.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
I apologize but I was reading results from a few days ago when I my SQL statement was incorrect. The SQL statement is working now. I thank everyone for their help and once again apologize for the trouble.
Matt
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top