IIF Query

[jo15765]

how to take the date/time that info was entered and the date time that issue was resolved, and if the time frame was greater than 7 days (168 hours) then I want to be able to run a query to display all information that the turn around time was greater than 7 days (168 hours)

Me.TAT =DateDiff("y", [DateFound],[DateResolved]) which gives me the day.

How do I set up a query to display If TAT is greater than 7 days.

I am working with something like this, but not beingn to succesful


=IIF([TAT]>7,7"")

[ajetrumpet]

first, make a query object. then ref the differences of both form controls in the statement, like:

where tat > datediff(expression) + 7

if tat is the date field, and you want recs that have tat great than 7 days from your current outcmoe of the "datediff" function

[jo15765]

Date Found is the date that the issue was reported, then date resolved was the date the issue was corrected. My TAT (turn around time) is the amount of time it took from the date the issue was found to the date the issue was corrected. So I guess in a sense all 3 are date fields. Will this query object still work with 3 date fields?

Is this the syntax you were saying should work?

Where([Forms]![Form1]![DateFound]-[Forms]![Form1]![DateResolved] < 7)

[ajetrumpet]

i would do it like this, jo:

where abs(datediff("d", formRef1, formRef2)) <7

do NOT use a subtraction sign, unless you know all the consequences of that sign in all aspects of access, which I'm sure you don't.

so do that. ABS() is not necessary, but I always do it, because I can never remember which arg goes where.

[jo15765]

I tried using:

SELECT [tbl1].[Date found on Recon], [tbl1].[Resolved_Date]
FROM tbl1
WHERE abs(datediff("d",DateFound,Date_Time_Resolved))<1;


And it is asking me to input the DateFound and the Date_Time_Resolved. I want it to pull this information from my table that already has the data on there. Oh, and I changed it to one, because now it needs to be one day instead of 7.

[ajetrumpet]

i don't get it jo.

datefound and dateresolved are both fields on forms, right?

and the datediff function needs to calculate the diff from those two, right?

OK, so there's your timeframe of ONE day, but what field are you trying to apply that "one day from today" concept on? there can only be ONE field, otherwise it wouldn't make any sense!

this:

SELECT [tbl1].[Date found on Recon], [tblSISImport].[Resolved_Date]

[/code]looks unrelated to everything else, and this:

WHERE abs(datediff("d",DateFound,Date_Time_Resolved))<1;

is irrelevant, because there are no table fields to be found anywhere in that criteria statement...are there?

furthermore, field refs on forms need this syntax:

forms!formname!DateFound

etc...

[jo15765]

I understand that. I have a calculated field on my form (TAT) that shows
=DateDiff("y",[DateFound],[Date_Time_Resolved]) and gives me a numerical value for the number of days that it took to be resolved. The problem that I am completely lost on, is trying to build a query from that field that will display only the records where my TAT field is 2 or greater.

I have been trying to get this code to work:

SELECT [tbl1].[Date_found_on_Recon], [tbl1].[Resolved_Date], [tbl1].[Days_taken_to_resolve]
FROM tbl1
WHERE [forms]![Frm1]![Days_taken_to_resolve] < 1;

But it displays no records.

[jo15765]

Oh nevermind. I had the control source for my TAT set wrong on my table. Something simple and obvious I should have caught earlier. Thanks for the input.