Between Dates

[Galphanore]

I have a table with start dates, end dates and holiday names. What I am trying to do is determine if today falls between any of those start and stop dates, but I can't figure out what to do. I think it is probibly something to do with BETWEEN, but I'm not sure how to use it correctly, could anyone give me a few pointers on this?

 

[statsman]

In the criteria of your startdate field enter:

Between (>=[startdate]) And (<=[endate])

The query will prompt you to enter a start date and an end date.

PS I may have got the bracketting wrong, its been a while since I did this

 

[Galphanore]

The problem is that I have a table that has, currently, ten, start and end dates, and I want to have it check between them all. The start and end dates are in a table.

I attached the current table and a report with an iif statement of what I want...but it's not right, and I'm not sure why.

 

[KeithG]

In the criteria row for StartDate put

>=Date()

In the criteria row for EndDate put

<=Date()

 

[raskew]

Hi -

Copy/paste this to a new query in your database. If you run it today (3-May-06) it'll come up empty because today's date doesn't fall into any of the ranges. Try adjusting the ranges to include today's date and you'll see the results. Tip: The Now() function includes time and isn't conducive to what you're after. Use the Date() function instead.

HTH - Bob

SELECT Date() AS mydate
  , Holidays.StartDate
  , Holidays.EndDate
  , Holidays.[Holiday Name]
FROM
   Holidays
WHERE
   (((Date()) Between [startdate] 
AND
   [enddate]));