Query to Report Duration an item is OPEN (1 Viewer)

[manix]

Hi All,

As a relative novice this probably sounds noobish, but I am not that hot on code at the moment.

I have a table that contains data relating to quality concerns. Now one field in this table is the date the concern is closed and another is date open. I want to report the duration concerns are open for.

So basically, I need to know how to query the table to show a duration, in weeks, that the concern was OR has been open for. I can relate a date to NOW but this would only work if the field has a date and not all concerns are going to be closed when I run the query. So effectively:

If the concern has a date closed entered, then report the duration in weeks between date open and date closed.

BUT

If the date closed does not have a value entered, count the duration in weeks as date open to NOW.

If anyone can translate that into something I can use in a query then it would be most appreciated. If in VB, please explain how I get that into a usable report format.

Thanks in advance.

 

[pbaldy]

Check out the DateDiff function, along with the Nz function to handle the Null date.

 

[jzwp22]

You will need to create a calculated field using an expression in the query. That expression will need to use several functions & those functions will have to be nested

IIF() function: a conditional function that checks a value or the result of an expression and does something if true or something else if false

IsNull() function: checks to see if the expression or field is null and returns true if it is null or false if is not null

datediff(): calculates the difference between two date/time values. There are options to express the difference in minutes, days, weeks, years etc.

date(): returns the current date (without the time portion)

You can find more on each of these functions in the help section.


So to do this in a query

SELECT field1, field2, etc., IIF(IsNull(dateclosedfield),datediff("ww",dateopenfield, date()), datediff("ww",dateopenfield,dateclosedfield)) as DurationInWeeks

 

[manix]

You will need to create a calculated field using an expression in the query. That expression will need to use several functions & those functions will have to be nested

IIF() function: a conditional function that checks a value or the result of an expression and does something if true or something else if false

IsNull() function: checks to see if the expression or field is null and returns true if it is null or false if is not null

datediff(): calculates the difference between two date/time values. There are options to express the difference in minutes, days, weeks, years etc.

date(): returns the current date (without the time portion)

You can find more on each of these functions in the help section.


So to do this in a query

SELECT field1, field2, etc., IIF(IsNull(dateclosedfield),datediff("ww",dateopenfield, date()), datediff("ww",dateopenfield,dateclosedfield)) as DurationInWeeks

That works brilliantly, thank you!

 

[jzwp22]

You're welcome

 

[pbaldy]

I would have done simply:

DateDiff("ww", dateopenfield, nz(dateclosedfield,Date()))

 

[jzwp22]

Paul, that is a much cleaner solution than mine. Thanks