Hi there and thanks in advance for any help,
I have a simple unbound form with two text fields in which two dates are entered: Date From and Date To.
It has a button that opens a query. The resulting data has a date field.
I have this in the query criteria in the date field... >=[forms]![frm_Extract_Informal_Events]![txt_DateFrom] And <=[Forms]![frm_Extract_Informal_Events]![txt_DateTo]
This works well if both the from and to dates are entered into the form, but returns zero results if just one date or neither dates are entered.
What I want is that if one or both of the form date fields is left blank then it affects the query as follows:
If only From Date then the results have all records from that date to now.
If only To Date then the results have all records up to that date.
If none are entered then return all results.
I have tried having two fields in my query - one each for the from and to dates, but it still doesn't work. I'm assuming none of this works because my criteria contains operators (=>), and if a date is left blank then the operators remain in the criteria.
I want to keep the query as an object rather than as SQL.
Has anyone any ideas how I can do this? The only solution I can see is having 4 queries and calling the appropriate one depending on what is in the form. Yuck!