A bit more than a nice simple date query..

  • Thread starter Chris Mortimore
  • Start date
C

Chris Mortimore

I'm looking for a query that will compare a range of dates, but a little bit
more than that as well... Using [Enter start date] and [Enter end date] in
the query...
if [Enter start date] and [Enter end date] are both given, I want it to
display everything between them
if [Enter start date] is given and [Enter end date] is left blank, I want it
to display everything from [Enter start date] to today
if [Enter start date] is left blank and [Enter end date] is given, I want it
to display everything since the beginning of the database to [Enter end date]
if [Enter start date] and [Enter end date] are both left blank, I want it to
display all dates.

I'm useless with queries, so anyone got any ideas how to do this one?

Thanks
 
V

Van T. Dinh

Do you have Records with future dates?

If you don't, you can use:

SELECT *
FROM [YourTable]
WHERE
( ([YourTable].[DateField] >= [Enter start date]) OR ([Enter start date] Is
Null) )
AND
( ([YourTable].[DateField] <= [Enter end date]) OR ([Enter end date] Is
Null) )

If you do have future dates, there is a slight inconsistency in the case
[Enter end date] is not entered. If [Enter start date] has value, you want
only "past" Records, i.e. no future dates. If [Enter start date] is not
entered, you want all Records including future dates. Still, it can be done
but a bit more complex.
 
C

Chris Mortimore

fantastic, works as i wanted, thanks

Van T. Dinh said:
Do you have Records with future dates?

If you don't, you can use:

SELECT *
FROM [YourTable]
WHERE
( ([YourTable].[DateField] >= [Enter start date]) OR ([Enter start date] Is
Null) )
AND
( ([YourTable].[DateField] <= [Enter end date]) OR ([Enter end date] Is
Null) )

If you do have future dates, there is a slight inconsistency in the case
[Enter end date] is not entered. If [Enter start date] has value, you want
only "past" Records, i.e. no future dates. If [Enter start date] is not
entered, you want all Records including future dates. Still, it can be done
but a bit more complex.

--
HTH
Van T. Dinh
MVP (Access)


Chris Mortimore said:
I'm looking for a query that will compare a range of dates, but a little bit
more than that as well... Using [Enter start date] and [Enter end date] in
the query...
if [Enter start date] and [Enter end date] are both given, I want it to
display everything between them
if [Enter start date] is given and [Enter end date] is left blank, I want it
to display everything from [Enter start date] to today
if [Enter start date] is left blank and [Enter end date] is given, I want it
to display everything since the beginning of the database to [Enter end date]
if [Enter start date] and [Enter end date] are both left blank, I want it to
display all dates.

I'm useless with queries, so anyone got any ideas how to do this one?

Thanks
Click to expand...
Click to expand...
 
Top