Help Please with a tricky Iif statement

[Bar_NZ]

Hi all, please help me...

I have a form that calculates time (In Minutes) however i'm having a little trouble with one specific iif statement (Below)..

What it should do:

Place Time (KPI) 01:00
Place Time (ACT), could be any time from 22:00 - 04:00 Hours

It should process the time in minutes and give me a calculation of how late or early.

What it does:

It's all good unless I go Under 01:00 hrs
E.G KPI is 01:00, Act time is 23:30 Result -1350, should be 30

Statement:
=IIf(DateDiff("n",DateAdd("n",200,[PlaceKPI12]),DateAdd("n",200,[PlaceACT12]))*-1>120,DateDiff("n",DateAdd("n",200,[PlaceKPI12]),DateAdd("n",200,[PlaceACT12]))*-1-1440,DateDiff("n",DateAdd("n",200,[PlaceKPI12]),DateAdd("n",200,[PlaceACT12]))*-1)

Can anyone see where I'm going wrong. :banghead:

Cheers

BAR_NZ

 

[vbaInet]

Do you have a Date element as part of your time field?

 

[Bar_NZ]

No, it's only time.. If I was to change it to use a date I have one field in my table where the user has to type the date each day (Short Date, 26/08/2012), can I referance that some how to show..

I'm not adverse to re-writing the entire table, however I have 13 Place and 13 Pull records to report on, these are all in one table.

Don't know if that helps at all

 

[vbaInet]

You need a date element because the time part on it's own doesn't know whether it's today or yesterday you're referring to. Concatenate a date part to it when you're doing your calculations.

 

[Bar_NZ]

I'm going to show my ignorance now sorry... How do I do that? Concatenate a date to the calculation?

Sorry for being such a dumb ass.. lol

 

[vbaInet]

E.g. if I have a time field and I need to concatenate today's date to it, I would do something like this:

CDate(Date() & " " & [TimeField])

 

[Bar_NZ]

Have attached a screen shot FYI...

 

[vbaInet]

Have attached a screen shot FYI...

Not sure what I should do with the screenshot

 

[Bar_NZ]

that's in a query i suppose?

 

[vbaInet]

In a query or a form. What I wrote there is an expression and remember expressions can be used in the Control Source of a control.

 

[Bar_NZ]

I have tried this and I get #Name?

 

[vbaInet]

Show me what you tried.

 

[Bar_NZ]

=CDate(DateStamp() & " " & [PullACT12])

 

[vbaInet]

I'm pretty sure I didn't write DateStamp() in my code. Please see post #6 again.

 

[Bar_NZ]

New result: 26/08/2012 23:30:00, however dosnt Date() mean todays date??

 

[vbaInet]

It does. It's a fictitious date you need for your calculations as mentioned in second post. If you are trying to get the difference between 01:00 and 23:00 the functions need to know whether 01:00 is yesterday or today and the same goes for 23:00.