Help needed for "Between Parameter"

[Graham]

Hello all,
I am using Access2002(XP)
I have a query with a date field and criteria as:
Between [Start Date] And [End Date]
This works fine.
But is there a way to get ALL the dates
without having to enter the First and Last dates
when the query is run.
I thought I would be able to just hit the enter key
when prompted for the dates without entering any dates.
This doesn't work.
Is there any way to do this?

 

[fredg]

Hello all,
I am using Access2002(XP)
I have a query with a date field and criteria as:
Between [Start Date] And [End Date]
This works fine.
But is there a way to get ALL the dates
without having to enter the First and Last dates
when the query is run.
I thought I would be able to just hit the enter key
when prompted for the dates without entering any dates.
This doesn't work.
Is there any way to do this?

Where [DateField] Between [Start Date] and [End Date] Or Like
IIf(IsNull([Start Date])"*")

Don't enter anything in either prompt to get all the records.

 

[Duane Hookom]

Or try:
Where [DateField] Between Nz([Start Date], [DateField]) and Nz([End Date]
,[DateField])
This assumes the [DateField] will not be null.

--
Duane Hookom
MS Access MVP

Hello all,
I am using Access2002(XP)
I have a query with a date field and criteria as:
Between [Start Date] And [End Date]
This works fine.
But is there a way to get ALL the dates
without having to enter the First and Last dates
when the query is run.
I thought I would be able to just hit the enter key
when prompted for the dates without entering any dates.
This doesn't work.
Is there any way to do this?

Where [DateField] Between [Start Date] and [End Date] Or Like
IIf(IsNull([Start Date])"*")

Don't enter anything in either prompt to get all the records.

 

[Graham]

Thanks guys,
I'll try them both. I appreciate the responses

Graham

Or try:
Where [DateField] Between Nz([Start Date], [DateField]) and Nz([End Date]
,[DateField])
This assumes the [DateField] will not be null.

--
Duane Hookom
MS Access MVP

Hello all,
I am using Access2002(XP)
I have a query with a date field and criteria as:
Between [Start Date] And [End Date]
This works fine.
But is there a way to get ALL the dates
without having to enter the First and Last dates
when the query is run.
I thought I would be able to just hit the enter key
when prompted for the dates without entering any dates.
This doesn't work.
Is there any way to do this?

Where [DateField] Between [Start Date] and [End Date] Or Like
IIf(IsNull([Start Date])"*")

Don't enter anything in either prompt to get all the records.

 

[Graham]

Hi Fred,
Just a quick note.
The query grid doesn't allow me to use the star key "*"
Are you able to use it?

 

[fredg]

Hi Fred,
Just a quick note.
The query grid doesn't allow me to use the star key "*"
Are you able to use it?

Where [DateField] Between [Start Date] and [End Date] Or Like
IIf(IsNull([Start Date])"*")

Don't enter anything in either prompt to get all the records.

Yes it does, and the query does return all records if the [Enter
Start] parameter prompt is null.

On the first criteria line for the date field, type:
Between [Start Date] and [End Date]

on the next line down (on the OR criteria line, type
Like IIf(IsNull([Start Date]),"*")

Make sure the parenthesis and punctuation is the same as above.

In SQL View, the code will then read (with the additional parenthesis
that Access throws in:

WHERE (((YourTable.DateField) Between [Start Date] And [End Date])) OR
(((YourTable.DateField) Like IIf(IsNull([Start Date]),"*")));