A bit more than a nice simple date query..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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...
 
Back
Top