Add a new Column

[tmpalaniselvam]

Hi!
I have some set of tables. I want to find difference between two (time) inputs in a particular table. After that I want to put different value according to some boundaries( like If above 10 means put as 10 and below 4 hours, put as zer0). I have written a function to set values according to time difference value.
How can I use this function in queries? How can I do this..? Pls help me. Its urgent.

Thanks,
Palani.

 

[papparuff]

To find records between two times for the same field, in the criteria field of the query use:
Between #7:00:00 AM# And #9:00:00 AM# (substitue the specific times you are filtering for)

You can create a derived field in your query to search for hours that are equal to or greater than 10 hours or less than 4 hours. Here is what it would look like:

HourDiff: IIf(DateDiff(&quot;h&quot;,[timein],[timeout])>=10,10,IIf(DateDiff(&quot;h&quot;,[timein],[timeout])<4,0))

In this example, [timein] is the time the person punched in and the [timeout] is the time the person punched out.

If you already have a field in the table that stores this time difference then you can use an update query to update the field. In the Update query's Update To field, the code would be this:

IIf(DateDiff(&quot;h&quot;,[timein],[timeout])>=10,10,IIf(DateDiff(&quot;h&quot;,[timein],[timeout])<4,0))

John Ruff - The Eternal Optimist

 

[tmpalaniselvam]

Hi, Papparuff!
Thanks a lot! I appreciate your help.....
Thanks Again,
Palani.