By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

Query filter between two dates if datefield is filled

P: 64

I have the following situation;

A form with a few selection field and buttons that will generate a report.


[type_of_case] : value can be "new" or "renewal"
[date_from] : can be any date
[date_to] : can be any date

The user does not have to fill out the date fields (so they can be empty if the user does not want to select a timeslot)

I have a query where in criteria it says;

- type_of_case : Like [Forms]![frm_reports].[type_of_case] & "*"

The "*" makes sure that the report is also generated if nothing is selected in [type_of_case]

Now, in the criteria of the date field [tr_ingang] i would like to do the following;

1. if [date_from] and/or [date_to] is empty it should list all items (like the "*" with [type_of_case]
2. if [date_from] and [date_to] are filled, it should list all records that fit those dates

Any idea on how i would fill the criteria field in the query ?

I really want to do this using the criteria field because i also use other criteria fields on this query.

Thanks in advance !
Feb 26 '14 #1

✓ answered by NeoPa

If that is required then applying the criteria to the date field itself won't work.

You could use the above with the date field and add a separate criteria in the next OR row that simply says Is Null.

Share this Question
Share on Google+
7 Replies

Expert Mod 5K+
P: 5,397
Basically the same way you've already done; however, you should include the date litteral ( # ) and I would use VBA to validate the fields that "from" is less than "to"

Please show us what you have already attempted for the date.
Feb 26 '14 #2

P: 64

Thanks for your reply zmbd !

This is what i have in the criteria;

Expand|Select|Wrap|Line Numbers
  1. >=[forms]![frm_rapporten].[datum_vanaf] And <=[forms]![frm_rapporten].[datum_tm]
This works fine if the user has filled both fields. However, the user can decide not to fill these fields and then the query should discard the filter on the date field.

Any suggestions ?

Feb 26 '14 #3

P: 294
Expand|Select|Wrap|Line Numbers
  1. IIf([forms]![frm_rapporten].[datum_vanaf] = ""
  2.  And [forms]![frm_rapporten].[datum_tm] = "", ,
  3. >=[forms]![frm_rapporten].[datum_vanaf] 
  4. And <=[forms]![frm_rapporten].[datum_tm]) 
This checks if the text boxes have text in them. If they do, do nothing, if not, evaluate them.

In Line 2, inbetween the commas, you would need to enter what you want to happen if the text boxes are indeed empty or have 0 length strings.

You could simply do:
Expand|Select|Wrap|Line Numbers
  1. [forms]![frm_rapporten].[datum_vanaf] = ""
  2.  And [forms]![frm_rapporten].[datum_tm] = ""
which is really not doing anything except keeping the text boxes the same.

Edit: It's also possible to check if the text box is empty by this method:
Expand|Select|Wrap|Line Numbers
  1. Trim
  2. ([forms]![frm_rapporten].[datum_vanaf] & "")
  3.  = vbNullString
Another edit:
Expand|Select|Wrap|Line Numbers
  1. If 
  2. Len(Me.TextBoxNameHere & vbNullString) = 0 Then
  3. ~~Your Code Here~~
It's also important to note when you want to check if the text boxes have something entered into them? On the click of a button? That would require VBA.
Feb 26 '14 #4

P: 64

Thanks for your reply, but it did not help me any further...

I do not seem to have the right solution to my problem.
The problem lies in the fact that i have a few other fields on which i have a filter in the query... I have to use more than one criteria. And as soon as i use the second criteria line (in which i put the same code as in the first criteria line, the buttons (which lauch the reports) remain invisible on the form, indicating there is something wrong. When I dont use the "or" criteria line, the filter does not work.

I tried to put hard data in like;

on the first criteria line
Expand|Select|Wrap|Line Numbers
  1.  >=#1-1-2014# And <= #1-1-2015# 
This does not work, there is no filter on the date but when i then use a copy of this in the "or" criteria it DOES work.

However, when i replace these lines with;

Expand|Select|Wrap|Line Numbers
  1.  >=[forms]![frm_rapporten].[datumbegin] And <=[forms]![frm_rapporten].[datumeind] 
The buttons disappear and i am not able to run any report.
How is this possible ?

Please be so kind to help me...
Feb 26 '14 #5

Expert Mod 15k+
P: 31,769
Your question asks for both dates to be ignored if either is Null. That's unusual. How you would handle that is a little more complicated than handling each being Null separately. For your question you would need to check if either is Null. This can be done by adding the two together and checking the result for Null. Depending on that result you use either the form controls or two extreme date values :
Expand|Select|Wrap|Line Numbers
  1. Between IIf(([forms]![frm_rapporten].[datumbegin]+[forms]![frm_rapporten].[datumeind]) Is Null,#1/1/1900#,[forms]![frm_rapporten].[datumbegin]) And IIf(([forms]![frm_rapporten].[datumbegin]+[forms]![frm_rapporten].[datumeind]) Is Null,#12/12/9999#,[forms]![frm_rapporten].[datumeind])
Now let's assume that you really want to replace each value only if that particular value is Null. A much more common requirement :
Expand|Select|Wrap|Line Numbers
  1. Between IIf([forms]![frm_rapporten].[datumbegin] Is Null,#1/1/1900#,[forms]![frm_rapporten].[datumbegin]) And IIf([forms]![frm_rapporten].[datumeind] Is Null,#12/12/9999#,[forms]![frm_rapporten].[datumeind])
Very long and apparently complicated. Mainly due to the length of the references to the form controls. Let's rewrite that using [F] for [forms]![frm_rapporten].[datumbegin] and [T] for [forms]![frm_rapporten].[datumeind]. This makes it much easier to appreciate :
Expand|Select|Wrap|Line Numbers
  1. Between IIf(([F]+[T]) Is Null,#1/1/1900#,[F]) And IIf(([F]+[T]) Is Null,#12/12/9999#,[T])
& :
Expand|Select|Wrap|Line Numbers
  1. Between IIf([F] Is Null,#1/1/1900#,[F]) And IIf([T] Is Null,#12/12/9999#,[T])
Much easier to work with and understand.

BTW. Strangely, the names of the controls in your question seem to have changed between one post and the next so I used those from your most recent post. It's not a good idea ever to allow that to get posted as it just causes confusion all round. Try to keep all changes limited to just those things that need to for the purposes of the question itself.
Mar 1 '14 #6

P: 64
Hi Neopa,

I tried your suggestion;

Expand|Select|Wrap|Line Numbers
  1. Between IIf([forms]![frm_rapporten].[datumbegin] Is Null;#1-1-1900#;[forms]![frm_rapporten].[datumbegin]) And IIf([forms]![frm_rapporten].[datumeind] Is Null;#12-12-9999#;[forms]![frm_rapporten].[datumeind])
This works fine as long as there is a date filled in in the fields. However, if on the form the dates are left empty, the report will not list the records where there is no date. I would like to have those records as well...

Any idea on this ?

Thanks for replying and helping me so much !
Mar 11 '14 #7

Expert Mod 15k+
P: 31,769
If that is required then applying the criteria to the date field itself won't work.

You could use the above with the date field and add a separate criteria in the next OR row that simply says Is Null.
Mar 11 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.