469,609 Members | 1,994 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

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

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

or Round((Table2.OUT-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
Jul 31 '08 #1
5 2162
I've had a similar problem in queries. It's not the most elegant solution, but I've gotten around it this way:
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.OUT-Table2.[IN])*24,4) AS HOURS would become
Round((val(nz([Table2].[OUT],"0")-val(nz([Table2].[IN],"0")*24,4) AS HOURS
Aug 1 '08 #2
107 100+
Hello, thank you
but now its telling me " the expression is has wrong number of arguments"
could the syntax be off a little
Aug 1 '08 #3
3,532 Expert 2GB
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)>
Aug 1 '08 #4
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.
Aug 1 '08 #5
107 100+
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
Aug 1 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Lauren Quantrell | last post: by
7 posts views Thread by Sunny K | last post: by
8 posts views Thread by Grey | last post: by
3 posts views Thread by Arne Gemsa | last post: by
1 post views Thread by saran3b2 | last post: by
reply views Thread by Solution2021 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.