Is this the proper way to query dates

[cdoyle]

Hi,
If I have 2 date fields (start_date and end_date)

And I want o create a query, that captures everything either on or betwen those 2 dates.

Would my query be

On the start date field.

>=[Forms]![frm_PRC_Yes_search]![txt_date_start]

Then on the end Date

<=[Forms]![frm_PRC_Yes_search]![txt_date_end]


I think it is, but just want to make sure that I'm not leaving data out.

 

[Uncle Gizmo]

You could also use the "between statement" this includes both the dates, or values, depending.

 

[cdoyle]

hmm, so what I have should work right?

It's not getting me the results that I should be.

I have an entry in my database, that has a start date of 2/1/08 and a end date of 2/2/08

In my form, if I put start date 2/2/08 and end date 2/3/08. I'm thinking, it should pull this record, but it doesn't..

Since the query is looking for anything with a start date equal too, or greater then what the user inputs (2/2/08) and less then or equal too what they enter 2/3/08.

So it should pull this record because the record is active from 2/1-2/2.

Or am I thinking about this wrong? Or do I need to modify my query to get that..

 

[pbaldy]

You want to test that the start date in your table is before the end date on the form and the end date in the table is after the start date on the form (and equal to on both).