Between date query

[bibbyd01]

Hi all

I'm trying to write a query based on today's date and whether it sits between a start and end date.

I have a table with plenty of fields, two of which are a start date and end date. I want the query to display data if today's date is equal to or between those two dates. I hope this is relatively easy to achieve?

 

[Scooterbug]

One way to do it is to create a new field in the query:

Expr1: Date()

then in the criteria, put in the following:
Between [Start Date] and [End Date]

 

[bibbyd01]

All this appears to do is to display today's date?

 

[namliam]

LOL Your missing the Between part....

YOu have to add the Between bit in the criteria

 

[bibbyd01]

I added the 'betwee' bit but it still didn't appear to work. I'm using access 2007 if that helps?

 

[Scooterbug]

Can you copy the EXACT sql statement that you are using? (To view the Sql from the query builder, click on design --> View Sql. Copy that and post it)

 

[boblarson]

I believe the criteria should be:

Under the start date: <= Date()

Under the End date: >= Date()

(on the same line if you are using the QBE grid - in order to give you an AND)

 

[bibbyd01]

Between [Start] And [End]

This is exactly as written. I've also tried the start and end method, but this didn't work either (that was the original way I thought of doing it before coming on here). This should work but doesn't, so I don't understand what else could possibly affect it?

 

[namliam]

What is the data type of your Start and End fields??

 

[bibbyd01]

Ah, success! It appears I hadn't set the date format! Thanks for the help!