Date Range search on Field with null Values

[vbafun]

Is it possible to do date range search on a field with Null values??

I want to select all records even if the field is empty. If it value is Null then internally it should check the end date with the current date. If the current date is within the range it should display the record.


Trying to figure this out for so long but was unable to do that.

Any help will be much appreciated.


Thanks in advance

[Dal Jeanis]

There's at least three ways.

You can add "OR Mydate IS NULL" to the WHERE clause,

You can use NZ(MyDate,xxxxx) where xxxxx is Date() for the current date, or xxxx is DateAdd("d",1,Date()) for tomorrow, or whatever.

You can create a global function MyFix(MyDate) that returns MyDate if not null, or overrides the Null with a chosen date if it is Null. This method is operationally identical to using NZ, but I think I once had a reason to do it.

[vbafun]

Now the subform is displaying records with null values as in the criteria section I used <datefield> between startdate and enddate or <datefield> is null.

But the problem is as soon as the main form is loaded even before user enters start date and end date all the records have <datefield> Null is getting displayed.

I want On load of main form only empty subform should be displayed. Once the user enters the start date and end date the subform should display all the records based on the criteria.

Thanks.

[vbafun]

Dal Jeanis Thanks so much for your reply. As I mentioned adding Is Null in the where clause worked perfectly. But I want the records to be only displayed on a button click not on load.

Thanks

[vbafun]

Got an alternate way, just made the subform invisible on Main form Load event and when start date and end date are entered and clicked on serach button made the subform visible = true.

Any other solution is most welcomed.

Thanks

[Dal Jeanis]

That's workable. There are lots of options.

You can set default values for the start and end dates that will result in no records being loaded.

You can set defaults of Date() (which is today's date) for the start and end dates.

You can set "DataEntry" to Yes and then switch it to No when the dates are selected.

[vbafun]

That's workable. There are lots of options.

You can set default values for the start and end dates that will result in no records being loaded.

You can set defaults of Date() (which is today's date) for the start and end dates.

You can set "DataEntry" to Yes and then switch it to No when the dates are selected.

Thanks a lot Dal Jeanis. I tried DataEntry option and it worked like a charm. You solved my problem in no time.
I really appreciate your help.