date diff("n", [starttime],[endtme] or Round((endtime-startime)*24,4)

[buddyr]

Hello I am working on time sheet form.
Whether I create fields in query =[minutes] \60 & format([minutes] mod 60, "\00")

or Round((Table2.O UT-Table2.[IN])*24,4) AS HOURS

When I try to add my totals in the fields to give me one Total- It only lets me if I have every field entered data into- And so If somebod is sick and does not fill in Tuesday IN and OUT
The total will not show up in my report.
any ideas
I tried notNull - maybe not in right place-- I have tried adding the fields together in a field in the query and then tried in a report
Thank you

[mlcottingham]

I've had a similar problem in queries. It's not the most elegant solution, but I've gotten around it this way:

val(nz([field_name],"0")

Function nz() says if the first value is null, return the second, else return the first... but it does it as a text string.

Function val() converts a text string to a number.


Therefore, Round((Table2.O UT-Table2.[IN])*24,4) AS HOURS would become
Round((val(nz([Table2].[OUT],"0")-val(nz([Table2].[IN],"0")*24,4) AS HOURS

[buddyr]

Hello, thank you
but now its telling me " the expression is has wrong number of arguments"
could the syntax be off a little

[missinglinq]

If you're referring to

Round((val(nz([Table2].[OUT],"0")-val(nz([Table2].[IN],"0")*24,4) AS HOURS

I expect it has something to do with it having 5 left-handed parens and only 3 right-handed parens. These generally have to be equal!

Linq ;0)>

[mlcottingham]

Yup.. I'm missing the second paren for the val function call all through the message... Let me see if it will let me edit.

[buddyr]

I am not sure this will help me all the way - but I tried:
total: nz([HOURS])+nz([HOURS1])+nz([HOURS2])+nz([HOURS3])+nz([HOURS5])+nz([HOURS6])
at end query
and seems to add values whether some fields are empty or not

thank you for the help