Finding Min/Max days between dates - Need help

[matt23lucier]

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

 

[SkipVought]

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]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[dhookom]

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]

 

[SkipVought]

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

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[matt23lucier]

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'

 

[dhookom]

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]

 

[lespaul]

are OpenDate and CloseDate defined as date fields in the table?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[matt23lucier]

They are defined as date/time fields within the access table.

 

[dhookom]

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]

 

[matt23lucier]

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