subtract one hour in query (time/date) field

[tuktuk]

Hey there,

I have a query with the following iif statement.

Adj Interval Time: IIf([Interval Time]=#12:00:00 AM#,#11:00:00 PM#,IIf([Interval Time]=#12:30:00 AM#,#11:30:00 PM#,[Interval Time]))

My goal is to subtract one hour from [Interval Time] for the else portion of the statement.

Something like this but need to specify the hour...

Denver Adj Interval Time: IIf([Interval Time]=#12:00:00 AM#,#11:00:00 PM#,IIf([Interval Time]=#12:30:00 AM#,#11:30:00 PM#,[Interval Time]-1))

Hope that makes sense.
Thanks
Tuk

 

[tuktuk]

i think i got it. if you recommend other method please advise.

T

Denver Adj Interval Time: IIf([Interval Time]=#12:00:00 AM#,#11:00:00 PM#,IIf([Interval Time]=#12:30:00 AM#,#11:30:00 PM#,Format(TimeValue([Interval Time])-TimeValue("01:00"),"hh:nn am/pm")))

 

[tuktuk]

hey there. so i'm runnign into an issue.

i use the above formula to append to a table "tbl_Interval" field named "Interval Time". I have another query that appends to the same table, "tbl_Interval" field but does not require me to use the format time value to subrtract one hour. so my prob is when i query off tbl_Interval i'm uanble to group by on the "Interval Time" field even tho the time interval is the same. It actually lists the Interval Time twice even tho it is the same and show the value individually.

if i view the SQL for the query i see that the "Interval Time" field is in brackets............tbl_Interval.[Interval Time],.....

how do i resolve this issue? are they different datatypes? it should be Date/Time.

Thanks
Tuk

 

[BiocideJ]

If it is supposed to be Date/Time, then your problem is likely the Format() formula as that outputs a string (formatted however you specify). I believe if you just take out the bounding FORMAT you may be fine.

i.e.
Denver Adj Interval Time: IIf([Interval Time]=#12:00:00 AM#,#11:00:00 PM#,IIf([Interval Time]=#12:30:00 AM#,#11:30:00 PM#,TimeValue([Interval Time])-TimeValue("01:00")))

 

[tuktuk]

gotcha.......actually i just reformatted in the query. thanks..that will get me there.

Interval Time Formatted: Format([Interval Time],"hh:nn:ss ampm")