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

 

[PHV]

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

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

 

[SkipVought]

Hi,

Do you really waht to have BOTH a Date Value and STRING in the same column?

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

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

 

[SkipVought]

I meant NUMERIC & STRING in the same column ???

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

 

[mddaniels]

Skip, your formula is working as far as not having errors, but I negelected to include the fact that the Complete Date and the Ordered Date columns include the time (24 hrs) as well. And the time difference is needed as well. Some of the fields, with your formula, give me the correct time difference, but others do not.

Completed Date
2/1/2009 8:24:00 AM
Ordered Date
2/1/2009 7:55:00 AM
Ordered to Completed HH:MM
12:29:00 AM (this is the formula results)

 

[dhookom]

mddaniels,
An IIf() function call should have 3 arguements which means there needs to be 2 commas between the ()s.

Also, any time you write an expression like this, you should make sure you have the same number of ( as ).

Also, if you must include spaces in your field/control names, you have to enclose the name in []s. It doesn't work to place a single set of []s around 2 or more field names with spaces.


Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

The difference between 2 Date/Time values is a decimal value in units of days.

So from your example the difference is 0.020138889 DAYS

If the DATE part if the two were 2 days apart, then the difference would be 2.020138889 DAYS.

Of course the VALUE can be expressed in DAYS or HOURS, or MINUTES or SECONDS or a combination thereof.

So the question is, how do you want to display the difference?


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

 

[PHV]

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

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

 

[mddaniels]

Display the HH:MM. Here are the results from the same formula in an excel spreadsheet.

0:29:00
1:05:53
24:55:00
18:35:00
16:24:00
1:20:50
11:29:00
0:29:46
24:19:00

 

[mddaniels]

This formula is not looking at the date to determine the lapse of time from the ordered date/time to the complete date/time. For example Ordered Date is 1/31/09 11:32 AM and Completed Date is 2/1/09 12:27 PM. The formula is returning 12:45 lapse of time, when it is actually 24:45.

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

Also, do you understand why it is returning only 5 out of 743 records?

Completed Date Procedure Ordered Date Ordered to Completed HH:MM
2/24/2009 10:45:00 AM VEIN DUPLX BIL LOW EXT 2/24/2009 10:00:00 AM 12:45:00 AM
3/12/2009 8:45:00 AM VEIN DUPLX BIL LOW EXT 3/12/2009 8:00:00 AM 12:45:00 AM
2/1/2009 12:27:00 PM VEIN DUPLX UNI UPP EXT 1/31/2009 11:32:00 AM 12:55:00 AM
3/26/2009 9:30:00 AM VEIN DUPLX BIL LOW EXT 3/26/2009 8:45:00 AM 12:45:00 AM
4/9/2009 11:45:00 AM VEIN DUPLX BIL LOW EXT 4/9/2009 11:00:00 AM 12:45:00 AM

 

[SkipVought]

This formula is not looking at the date to determine the lapse of time from the ordered date/time to the complete date/time.

Ah, but it MUST.

The DIFFERENCE is 1.038194444 DAYS, which can be CONVERTED to HOURS, MINUTES & SECONDS

HOURS: = (1.038194444 DAYS) * (24 HOURS / DAY) = 24.91666667

MINUTES: = (.91666667 HOURS) * (60 MINUTES / HOUR) = 55

24:55

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

 

[mddaniels]

That makes sense, but would you mind terribly showing me how to put this into the IIf formula?

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

 

[SkipVought]

Substitute this for the FORMAT statement

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


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

 

[SkipVought]

Sorry, discard the trailing & ":", as I thought i was also appending SECONDS.

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

 

[mddaniels]

What is the & _ doing?

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

 

[SkipVought]

Concatenating a string that looks like a time value.

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

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

 

[mddaniels]

A Data type mismatch error is showing on this formula. I've checked the design table and the data types on the columns are all set to Date/Time. Could there be a problem within the formula?

 

[SkipVought]

Please post your entire formula.

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

 

[mddaniels]

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

 

[SkipVought]

What happened to the IIF formula that you were supposed to substitute into?

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