Search Date Range with Null Sources

[MintChipMadness]

I have a start date control and an end date control to search a record source (date field). I found a thread with the expression to do the search but it doesn't completely work for me. The reason is I haven't populated all the table date fields (some are null) and when I don't have anything in start date and end date control it gives all the values without nulls. I would really like it to show all values including nulls. How would I adjust the expression to do so? Also would someone be able to explain the whole expression to me so I understand what is going on with it? I don't understand the portion that says [Forms]![DataEntry]![startdate] & ""="". I understand the true and false part of the if statement. Thank you for your help.

https://www.accessforums.net/forms/s...orm-13232.html

Between IIf([Forms]![DataEntry]![startdate] & ""="",#1/1/1900#,[Forms]![DataEntry]![startdate]) And IIf([Forms]![DataEntry]![enddate] & ""="",Date(),[Forms]![DataEntry]![enddate])

[MintChipMadness]

I decided to not worry about getting the records with null values because it will be a required field when everything is set up. If someone could still explain the portion of the expression I am confused about, that would be great. Thank you.

[John_G]

Hi -

You didn't say how you are using this, but if you are using it as part of a SQL statement in a string, you could try the Nz function instead of the Iif:


"Between #" & nz([Forms]![DataEntry]![startdate], #1/1/1900# ) & "# And #" & nz([Forms]![DataEntry]![enddate],date()) & "#"

The Nz functions say if startdate is null, use 01/01/1900, and if enddate is null, use the current date.

This is one case where Nz is a lot easier to use than iif. A word of caution: if your default system date format is not mm/dd/yyyy, then you will have to format the dates to match .

HTH

John

[MintChipMadness]

Hi -

You didn't say how you are using this, but if you are using it as part of a SQL statement in a string, you could try the Nz function instead of the Iif:


"Between #" & nz([Forms]![DataEntry]![startdate], #1/1/1900# ) & "# And #" & nz([Forms]![DataEntry]![enddate],date()) & "#"

The Nz functions say if startdate is null, use 01/01/1900, and if enddate is null, use the current date.

This is one case where Nz is a lot easier to use than iif. A word of caution: if your default system date format is not mm/dd/yyyy, then you will have to format the dates to match .

HTH

John

Thank you John. It is nice to learn a different way of doing the same thing. To answer your question, it is an expression in a query. I think I am going to keep the if statement because I believe it takes into account zero length strings as well. Those would be produced if someone deleted what they originally wrote in the field. I am confused at how the if statement works though. What I don't understand in the if statement is why the ""="" is added to the string. To take into account nulls and zero length strings, I would write something like if nz([field]) or [field] = "". I don't know how that is done by just saying if [field] & ""="".

[John_G]

Hi -

Since the query is referring to form fields, you don't need to worry about zero-length strings. Deleting all the contents of a control results in a Null. (If fact, for a text box, entering only blanks results in Null as well). A zero-length string cannot be entered by a user.

However, if you want to check for both Null and zero-length at the same time, you can use nz([field],"").

Now that you point it out, I cannot see what the IIf's do either - were you able to actually use them without error?

John

[MintChipMadness]

I didn't get an error with it and I tested it by trying all combinations so it looks good.

[John_G]

Hi -

Now I see it - it makes perfect sense. What it does is add a zero-length string to the date value; if the date value is Null, then Null & "" = "".

Adding brackets and spaces to the expression so that it is clearer gives:


IIf( ([Forms]![DataEntry]![startdate] & "") = ("") ,#1/1/1900#,[Forms]![DataEntry]![startdate])

And, it should work fine because it takes care of the nulls. Clever.

Cheers!

John

[orange]

Another common approach to identifying that a field is Null is

  If Len(FieldInQuestion & "") = 0  Then  FieldInQuestion is Null

in your example
IIf(Len(([Forms]![DataEntry]![startdate] & "") = 0 ,#1/1/1900#,[Forms]![DataEntry]![startdate])

[MintChipMadness]

John - Thank you. I see it now. I was confused because I saw it as an equal in between two quotes instead of quotes equal to the other quotes.

Orange - Thank you for your alternate example. That made it even more clear.