Trying to assign null value to an expression

[Leah]

Hi there, I am sure there is an easy answer for this... but please bear with me...

I have a hr database with a query called 'qrytotalleave', I have several fields on it, two of which are made up of expressions.

As follows:

Leave Total Hours: Sum(IIf([leave start time]>[leave end time],(#23:59:59#-[leave start time])+[leave end time],[leave end time]-[leave start time]))

and

Leave Total Days Taken: Sum(DateDiff("d",[leave start date],[leave end date]))

When I run the query, it does not bring up employees who have a null value in either of these fields.

I'm not sure how to get it to run where it will show all employees, with values in either field, not having to have values in both.

Please help!

Thank you!

Leah

 

[AncientOne]

IIf(IsNull([Leave Start Date] OR IsNull ([Leave End Date]), 0,Sum(DateDiff("d",[leave start date],[leave end date])))

In other words the summing expression becomes the FALSE part of an IIF statement. Same for the other expression.

 

[Scott Kennedy]

Finding nulls as well as everything "*"

I've had a look at the solution above but unfortunately it doesn't answer a question I've come across in my database. I'm using forms with unbound text boxes to act as criteria sources for select queries.

The criteria I've been using in the select query is like the following -
Like IIf([Forms]![Search]![Width] Is Not Null,[Forms]![Search]![Width],"*")

If somebody puts a value into the width box on the form then it's used to search on. If not then everything is search for. Unfortuantely, some records in the database have null values for width and I'm not getting them on running the query thanks to the properties of "*". I've tried placing "Is Null" on the OR criteria line of the query but that always gives me every record with a null value if I want it or not. Can anybody suggest a way around this?
Yours in hope,
Scott

 

[AncientOne]

IIf([Forms]![Search]![Width] Is Not Null,[Forms]![Search]![Width], Like "*" AND Not Is Null)

 

[Jon K]

Scott,

Try these criteria for the Width field in the query grid:-

Criteria: Like [Forms]![Search]![Width] & "*"
Or:        [Forms]![Search]![Width] is null


Note. If the query also contains similar criteria for other fields, you may need to use the approach set out in this thread (but replacing the parameters with the references to the controls on the form):-
http://www.access-programmers.co.uk/forums/showthread.php?threadid=48462

 

[Leah]

Thanks AncientOne, but I seem to be getting the error message

'The Expression you entered has a function containing the wrong number of arguments'

Any ideas?

Thanks!

Leah

 

[Scott Kennedy]

Null values

Ancient One - thanks for the reply but alas is doesn't work. When I typed the code in it auto expanded to the following :

Like IIf([Forms]![Search]![Width] Is Not Null,[Forms]![Search]![Width],([1Labels].[Width]) Like "*" And Not ([1Labels].[Width]) Is Null)

Where [1Labels].[Width] is the field on the table [1Labels] being searched by the query. Reading through the above statement it seems logical that it would work but the result of the search is an empty dataset. For what ever reason this code excludes every record on the table.


Jon K - thanks for your tip but again this code doesn't work. The criteria - Like [Forms]![Search]![Width] & "*" results in everything being selected and listed by the query no matter what is typed in as a width. The & "*" strips the query of its power to decriminate.

Hmm, as it stands I may just have to put in zeros or something to change the records to non nulls.

Thanks for your suggestions!
Scott

 

[AncientOne]

IIf(IsNull([Leave Start Date] OR IsNull ([Leave End Date]), 0,Sum(DateDiff("d",[leave start date],[leave end date])))

In other words the summing expression becomes the FALSE part of an IIF statement. Same for the other expression.

Leah, sorry to be tardy in getting back to you, but I've been away.

I omitted a closing bracket after IsNull([Leave Start Date] , that's all.

Scott, take out the first "Like".

 

[realnine]

Leah,
I didn't take a lot of time to study your problem but you might try the Nz function which returns a zero for null values. Don't know if this function works on date formats.

HTH

 

[Leah]

Thanks everyone for your help!

Cheers,

Leah