Like Date Parameter

[Guest]

Hello,
I have a parameter that uses
Between [Forms]![frmMaster]![BeginDate] And [Forms]![frmMaster]![EndDate]
The problem is: I don't want the dates to be required. Is there a way to
tell my query to ignore the dates?
My query uses 8 other parameters that come from this form that use Like.
Thanks,
Melinda

 

[Ken Snell [MVP]]

Assuming that, by "ignore" the dates, you mean that the form's controls may
be empty for the dates? One way to do this would be this criterion
expression:

Between Nz([Forms]![frmMaster]![BeginDate],0) And
Nz([Forms]![frmMaster]![EndDate],Date())

 

[Guest]

Thanks Ken,
That did exactly what I needed!

 

[Guest]

Hi Ken - this is extremely useful and I have used this in my query - thanks,
I was wondering if there is a version of this that would make the last date
inclusive? - at the moment I have to go a day further in last date parameter.

Thanks for your advice
--
Kind Regards

Hazel

Assuming that, by "ignore" the dates, you mean that the form's controls may
be empty for the dates? One way to do this would be this criterion
expression:

Between Nz([Forms]![frmMaster]![BeginDate],0) And
Nz([Forms]![frmMaster]![EndDate],Date())

--

Ken Snell
<MS ACCESS MVP>

Hello,
I have a parameter that uses
Between [Forms]![frmMaster]![BeginDate] And [Forms]![frmMaster]![EndDate]
The problem is: I don't want the dates to be required. Is there a way to
tell my query to ignore the dates?
My query uses 8 other parameters that come from this form that use Like.
Thanks,
Melinda

 

[Gary Walter]

Hi Hazel,

I think you are asking for:

= Nz([Forms]![frmMaster]![BeginDate],0) And

< Nz([Forms]![frmMaster]![EndDate],Date()+1)

"huzzlepuzzle"wrote

Hi Ken - this is extremely useful and I have used this in my query -
thanks,
I was wondering if there is a version of this that would make the last
date
inclusive? - at the moment I have to go a day further in last date
parameter.

Thanks for your advice
--
Kind Regards

Hazel

Assuming that, by "ignore" the dates, you mean that the form's controls
may
be empty for the dates? One way to do this would be this criterion
expression:

Between Nz([Forms]![frmMaster]![BeginDate],0) And
Nz([Forms]![frmMaster]![EndDate],Date())

--

Ken Snell
<MS ACCESS MVP>

Hello,
I have a parameter that uses
Between [Forms]![frmMaster]![BeginDate] And
[Forms]![frmMaster]![EndDate]
The problem is: I don't want the dates to be required. Is there a way
to
tell my query to ignore the dates?
My query uses 8 other parameters that come from this form that use
Like.
Thanks,
Melinda

 

[Van T. Dinh]

Hazel

Small variation from Gary's response and use whichever suitable for you:

= Nz( [Forms]![frmMaster]![BeginDate], 0 ) And

< IIf( IsNull([Forms]![frmMaster]![EndDate]),
DateAdd( "d", 1, Date() ),
DateAdd( "d", 1, [Forms]![frmMaster]![EndDate] ) )

--
HTH
Van T. Dinh
MVP (Access)

Hi Hazel,

I think you are asking for:

= Nz([Forms]![frmMaster]![BeginDate],0) And

< Nz([Forms]![frmMaster]![EndDate],Date()+1)

 

[Gary Walter]

Yup...completely forgot about incrementing
non-null ending date..

Thanks Van!

gary

Hazel

Small variation from Gary's response and use whichever suitable for you:

= Nz( [Forms]![frmMaster]![BeginDate], 0 ) And

< IIf( IsNull([Forms]![frmMaster]![EndDate]),
DateAdd( "d", 1, Date() ),
DateAdd( "d", 1, [Forms]![frmMaster]![EndDate] ) )

--
HTH
Van T. Dinh
MVP (Access)

Hi Hazel,

I think you are asking for:

= Nz([Forms]![frmMaster]![BeginDate],0) And

< Nz([Forms]![frmMaster]![EndDate],Date()+1)