By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

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

P: 107
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
Share this Question
Share on Google+
5 Replies

P: 4
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

P: 107
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

Expert 2.5K+
P: 3,532
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

P: 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

P: 107
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.