Qbf Date Field Help

[LC85]

Hello,

Can someone help me please???

I have a field on my Main Table called "next Contact Date" and i want to be able to search by 'from and to' date or just by 'from' date only or 'to' date and if it is left null then to retrieve records matching the other criteria specified and return all records including field that are blank!

I also have 2 further date fields I want to search by, which are part of other tables related to the main table.

My main problem is if the date text boxes on my form is left blank it doesn't show records that are blank in my table.

I hope this makes sense..

 

[ajetrumpet]

LC,

It makes perfect sense, as there have been a million questions about this!!

First, check out Jon K's FAQ post in the FAQ section of this forum, here.

Then, check out my explanation of this technique in post #8 here.

And, if that is still confusing, there is another explanation (perhaps the same way) in post #8 here.

And, finally, for a twist (mix and match operators type thing), check out post #8 here.

 

[EMP]

My main problem is if the date text boxes on my form is left blank it doesn't show records that are blank in my table.

You can easily resolve the problem by following Jon K's method in the thread Adam has linked to. Just put two criteria in the query grid like these:-

---------------------
Field: [DateField] >= [Forms]![FormName]![txtStartDate] Or [Forms]![FormName]![txtStartDate] Is Null

Show: uncheck

Criteria: True
---------------------

Field: [DateField] <= [Forms]![FormName]![txtEndDate] Or [Forms]![FormName]![txtEndDate] Is Null

Show: uncheck

Criteria: True
---------------------

^

 

[LC85]

Hello,

Thanks for the response, I can't seem to get it to work...
I obviously done something wrong....

I entered the [DateField] as the name on the table...
Field: [DateField] <= [Forms]![FormName]![txtEndDate] Or [Forms]![FormName]![txtEndDate] Is Null

As you can tell i am a little new to access!

 

[EMP]

Sorry, should have mentioned that you have to use your correct field name, form name and text box names in the expressions.

See the query and form in the attached sample database.

^

 

[LC85]

Thank You that was very helpful... It works!!!