Query between dates on form fields and return all if left blank (1 Viewer)

MushroomKing

Registered User.
Local time
Today, 03:36
Joined
Jun 7, 2018
Messages
100
Hi everyone! I know youre thinking there is alot of solutions out there to this, and a common question, however its driving me up the wall for hours on end and will not work. I have simply no idea what to try anymore :confused::confused:


I have a linked excel file as my data source.
I have query with a date field, that takes the values from my form.

No problem, until i want it to return all if the form fields are left blank.

How to do this guys? I should not be so difficult but i dont get it LOL.

Here are some solutions I tried:

Code:
Between IIf([forms]![filter_form].[startdatefield] Is Null,#1/1/1900#,[forms]![filter_form].[startdatefield]) And IIf([forms]![filter_form].[enddatefield] Is Null,#12/12/9999#,[forms]![filter_form].[enddatefield])

Code:
Between [Forms]![filter_form]![startdatefield] And [Forms]![filter_form]![enddatefield] Or Is Null

Code:
Like "*" & (([datadump].[Datum]) Between [Forms]![filter_form]![startdatefield] And [Forms]![filter_form]![enddatefield]) & "*"



Please help. Thanks in advance!
 

isladogs

MVP / VIP
Local time
Today, 10:36
Joined
Jan 14, 2017
Messages
18,186

bastanu

AWF VIP
Local time
Today, 03:36
Joined
Apr 13, 2010
Messages
1,401
Add two calculated fields to your query and apply diferent criteria to them on two different lines in QDE. Here is for the start date: StartDateEmpty:isnull([Forms]![filter_form]![startdatefield]). On the same line where you have your existing "between" criteria add False and on the one below add True. Similar for the End Date.

Cheers,
Vlad
 

Users who are viewing this thread

Top Bottom