Specifying criterfia in a calculated field expression 1

[JSD]

Hello

I am working with a query that tracks log in times and log out times of employees. When the users log in, they are either logging in to run time, set-up time, or down time. They can specify set-up or down time by checking a box for either. All in one query, I want to have calculated fields that subtract the log in times from the log out times for set-up time, and down time. Right now, to see the log in times for set-up I have to put -1 in the criteria of the set-up check box and a 0 in the criteria for the down time check box, then create the calculated field:

act setup hrs: ([timeout]-[timein])*24

I then want to see the log in times for down time in the same query. Normally I would just create another similiar query, only I would put a 0 in the criteria for the set-up check box and -1 in the criteria of the down time check box. But I need both in the same query so I can get them into a chart. I guess my question is there a way to specify criteria in the expression of a calculated field, like IIf setup.Value = -1 AND downtime.Value = 0 Then setuphrs: ([timeout]-[timein])*24 Else setup.Value = ""

I am hoping in the end I will have setup and downtime calculated fields in the query, each one only populating where its check box is checked, leaving the other records blank in that column. Any ideas?

I appreciate the time an expertise

Jeremy

 

[mgolla]

You would want to create the calculcated field using the IIf statement and remove the criteria for the other fields.

Example:

act setup hrs:IIf(Setup.Value = -1 and Downtime.value = 0, ([timeout]-[timein])*24,"")

act downtime hrs:IIf(Setup.Value = 0 and Downtime.value = -1,([timeout]-[timein])*24,"")

HTH
Mike

 

[JSD]

Mike,

Thanks for your time and knowledge.

Jeremy