IIf formula help

[mddaniels]

Please, can someone tell me what is wrong with this formula?

IIF([Completed Date]>[Ordered Date][Completed Date-Ordered Date]),"Order Date Time After Completed")

The results should be if the Completed Date is greater than the Ordered Date, subtract the ordered date from the completed date, otherwise put in the field the comment "Order Date Time After Completed

 

[mddaniels]

I'm sorry, I must not be understanding. I thought you meant for me to use the int formula you supplied:

SkipVought (Programmer) 29 May 09 10:32

Substitute this for the FORMAT statement

Int(([Completed Date] - [Ordered Date]) * 24) & ":" & _
(([Completed Date] - [Ordered Date]) * 24 - Int(([Completed Date] - [Ordered Date]) * 24)) * 60 & ":"

What did you mean for me to do?

 

[SkipVought]

In this...

IIf([Completed Date]>[Ordered Date], [b]Format([Completed Date]-[Ordered Date],"HH:NN")[/b], "Order Date Time After Completed")

substitute the supplied code for the Format function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mddaniels]

I'm not sure I'm understanding fully, here is the formula now and it does not work, so I am positive I have it wrong.

IIf([Completed Date]>[Ordered Date], Int(([Completed Date]-[Ordered Date])*24) & ":" & (([Completed Date]-[Ordered Date])*24-Int(([Completed Date]-[Ordered Date]*24))*60 & ":"),"HH:NN"), "Order Date Time After Completed")

 

[SkipVought]

This works for me. Are you sure that you have proper date/time values in your table?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mddaniels]

The error is 'the expression you entered contains invalid syntax, or you need to enclose your text data in quotes'.

IIf([Completed Date]>[Ordered Date], Int(([Completed Date]-[Ordered Date])*24) & ":" & (([Completed Date]-[Ordered Date])*24-Int(([Completed Date]-[Ordered Date]*24))*60 & ":"),"HH:NN"), "Order Date Time After Completed")

The data in the table is:
Completed Date Procedure Ordered Date Ordered to Completed HH:MM
2/27/2009 9:32:00 AM VEIN DUPLX BIL LOW EXT 2/26/2009 4:06:00 PM
3/20/2009 11:16:00 AM VEIN DUPLX BIL LOW EXT 3/20/2009 8:53:23 AM
3/11/2009 2:45:00 PM VEIN DUPLX UNI LOW EXT 3/11/2009 1:47:28 PM
3/11/2009 4:26:00 PM VEIN DUPLX UNI LOW EXT 3/11/2009 4:00:00 PM
2/27/2009 11:03:00 AM VEIN DUPLX BIL LOW EXT 2/27/2009 10:23:00 AM
3/19/2009 1:06:00 PM VEIN DUPLX UNI LOW EXT 3/19/2009 10:56:45 AM
3/23/2009 10:55:00 AM VEIN DUPLX BIL LOW EXT 3/22/2009 10:11:00 PM
3/19/2009 10:08:00 AM VEIN SAPH MAPPING UNI EXT 3/19/2009 9:15:00 AM

 

[PHV]

IIf([Completed Date]>[Ordered Date], Int(([Completed Date]-[Ordered Date])*24) & ":" & (([Completed Date]-[Ordered Date])*24-Int(([Completed Date]-[Ordered Date])*24))*60, "Order Date Time After Completed")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mddaniels]

Still getting the 'data type mismatch in criteria expression' error.

IIf([Completed Date]>[Ordered Date], Int(([Completed Date]-[Ordered Date])*24) & ":" & (([Completed Date]-[Ordered Date])*24-Int(([Completed Date]-[Ordered Date])*24))*60, "Order Date Time After Completed")

 

[SkipVought]

What do you get if you query this as an expression...
[tt]
([Completed Date]-[Ordered Date])
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mddaniels]

Here is the result from the simple expression ([Completed Date]-[Ordered Date]). Notice that ID #1 and #1 5 are missing. I'll show you why but don't understand why.

ID Completed Date Location Ordered Date Ordered to Completed HH:MM
2 3/13/2009 9:38:00 AM CARDIOLOGY - MEDICAL CENTER 3/13/2009 8:09:02 AM 1:28
3 3/4/2009 12:55:00 PM CARDIOLOGY - MEDICAL CENTER 3/3/2009 8:24:00 PM 16:31
4 3/17/2009 10:29:00 AM CARDIOLOGY - MEDICAL CENTER 3/17/2009 9:57:18 AM 0:31
6 3/19/2009 2:16:00 PM CARDIOLOGY - NORTH CAMPUS 3/19/2009 9:02:00 AM 5:14

Here is what ID# 1 and #5 look like in the table:

ID Completed Date Ordered Date Ordered to Completed HH:MM
1 3/26/2009 6:00:00 PM 3/25/2009 5:21:00 PM
ID Completed Date Ordered Date Ordered to Completed HH:MM
5 3/23/2009 10:13:00 AM 3/20/2009 5:21:00 PM

Why are these two records missing from the simple expression?

I tried running the expression above with the HH:MM column blank in all fields, but this only results in nothing showing up in the query.

 

[dhookom]

Can you provide the full SQL view? If records are not being returned then there must be some type of filtering applied. I expect this is a join issue but can't see your SQL or your data.

Duane
Hook'D on Access
MS Access MVP

 

[mddaniels]

I think it will be easier to just put the formula into the excel spreadsheet that has to be imported anyway. But, could you tell me how to make the custom formatting in excel [h]mm:ss to work in Access. I don't see custom formatting in the design view for the table. The difference is the excel formatting makes the formula show the exact hours, ie., when the date completed is 3/26/09 18:00 and the date ordered is 3/25/09 17:21 that is 24 hrs and 39 min lapse in time. I need for access to display this as 24:39:00 but it is displaying it as 39:00.

 

[SkipVought]

tell me how to make the custom formatting in excel [h]mm:ss to work in Access.

AFAIK, you cannot.

That is why I posted a conversion.

Still waiting on your SQL as requested by Duane.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mddaniels]

SELECT [Critical Value Quality Report tbl].ID, [Critical Value Quality Report tbl].[Completed Date], [Critical Value Quality Report tbl].Procedure, [Critical Value Quality Report tbl].[Dictating MD], [Critical Value Quality Report tbl].Technologist, [Critical Value Quality Report tbl].Location, [Critical Value Quality Report tbl].[Ordered Date], [Critical Value Quality Report tbl].[Arrived Date], [Critical Value Quality Report tbl].[Ordered to Completed HH:MM]
FROM [Critical Value Quality Report tbl]
WHERE ((([Critical Value Quality Report tbl].[Ordered to Completed HH:MM])=IIf([Completed Date]>[Ordered Date],Format([Completed Date]-[Ordered Date],"HH:NN"),"Order DateTime After Completed")));

 

[dhookom]

Whoa! Why did you place the expression in the WHERE clause? Your questions suggested you wanted to create a new column in your query so the expression should go in the SELECT clause.

In future posts, please provide the SQL view of your queries so we can actually see what you are doing.


Duane
Hook'D on Access
MS Access MVP

 

[PHV]

WHERE [Ordered to Completed HH:MM]=IIf([Completed Date]>[Ordered Date],(24*Int([Completed Date]-[Ordered Date])+Format([Completed Date]-[Ordered Date],'h')) & Format([Completed Date]-[Ordered Date],':nn'),'Order DateTime After Completed')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mddaniels]

Sorry, I did not mean to be confusing. Is this how the entire expression should look to have the query to return the lapse of time from ordered date to completed date? If so there is still a data type mismatch.

SELECT [Critical Value Quality Report tbl].ID, [Critical Value Quality Report tbl].[Completed Date], [Critical Value Quality Report tbl].Procedure, [Critical Value Quality Report tbl].[Dictating MD], [Critical Value Quality Report tbl].Technologist, [Critical Value Quality Report tbl].Location, [Critical Value Quality Report tbl].[Ordered Date], [Critical Value Quality Report tbl].[Arrived Date], [Critical Value Quality Report tbl].[Ordered to Completed HH:MM]
FROM [Critical Value Quality Report tbl]
WHERE [Ordered to Completed HH:MM]=IIf([Completed Date]>[Ordered Date],(24*Int([Completed Date]-[Ordered Date])+Format([Completed Date]-[Ordered Date],'h')) & Format([Completed Date]-[Ordered Date],':nn'),'Order DateTime After Completed')

 

[PHV]

What about this ?

SELECT ID, [Completed Date], [Procedure], [Dictating MD], Technologist, Location, [Ordered Date], [Arrived Date], IIf([Completed Date]>[Ordered Date],(24*Int([Completed Date]-[Ordered Date])+Format([Completed Date]-[Ordered Date],'h')) & Format([Completed Date]-[Ordered Date],':nn'),'Order DateTime After Completed') AS [Ordered to Completed HH:MM]
FROM [Critical Value Quality Report tbl]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mddaniels]

This expression returns no data: Why does the ; continue to appear in the expression, when it is not typed into the expression? Is this causing the problem?

SELECT ID, [Completed Date], [Procedure], [Dictating MD], Technologist, Location, [Ordered Date], [Arrived Date], IIf([Completed Date]>[Ordered Date],(24*Int([Completed Date]-[Ordered Date])+Format([Completed Date]-[Ordered Date],'h')) & Format([Completed Date]-[Ordered Date],':nn'),'Order DateTime After Completed') AS [Ordered to Completed HH:MM]
FROM [Critical Value Quality Report tbl]
WHERE [Ordered to Completed HH:MM]=IIf([Completed Date]>[Ordered Date],(24*Int([Completed Date]-[Ordered Date])+Format([Completed Date]-[Ordered Date],'h')) & Format([Completed Date]-[Ordered Date],':nn'),'Order DateTime After Completed');

 

[PHV]

Sorry, but in my suggestion NO WHERE CLAUSE !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mddaniels]

Thank you so much for your help!! I have not idea why this is working, but I intend to study it until I do know. Placing PHV's expression in the 'field' area of the query is giving the results that I need, with the exception of the seconds.

Sorry it took so long, but believe me I do appreciate everyone's diligence and patience.

Thanks again.