Between dates or is null

[DrJ]

I have the following criteria in my query:

Between [Forms]![frmMenu]![Sub]![van] And [Forms]![frmMenu]![Sub]![tot]

This works fine, but when the boxes [van] and [tot] contain no data, the query shows no results.

I want the query to show all records if these boxes are empty.

Does anyone has an idea?

Thanks.

Rob

 

[Malcy]

Hi
Probably being a bit thick here but you are asking the query to show you all results between two specified dates from textboxes.
If either of the textboxes are empty then what is Access meant to do? It is a very (too) logical beast and if one date is null it is simply unable to do anything.
Suggest you either put some code in to test that the boxes have valid entries (and if not then shunt the user back to complete them properly) or else re-think how you structure your query.
One thought might be to say if van is null then van = #01/01/2000# (i.e. the beginning of time!) and again if tot is null then tot = #31/12/2010# (i.e. well beyond the forseeable future.
Others may have better ideas but this might kick start something for you.
Good luck

 

[Jon K]

Put this in a new column in the query grid (replacing with the correct DateField name):-

Field: IIf([Forms]![frmMenu]![Sub]![van] Is Null Or [Forms]![frmMenu]![Sub]![tot] Is Null, True, [DateField] Between [Forms]![frmMenu]![Sub]![van] And [Forms]![frmMenu]![Sub]![tot])

Show: uncheck

Criteria: True


When either [van] or [tot] is null, all records will be returned.
.

 

[DrJ]

that's the trick

Thank a lot Jon.

Rob