Create a Query for Yes field and Between Dates field

[Brian62]

I have a query that I am trying to pull information from a yes/no field that is for open studies and a date field. The date field is for studies that have closed.

I need to capture all current open studies and all past closed studies that were closed during a specific time frame.

Is there a way for me to get the two to work?

 

[boblarson]

Why have both a date field and a yes/no?

If the date field is not null then it is closed and if null it is open. Is that not correct?

If so, then the yes/no field is extraneous.

You can then just query for the date range in the date field:

Between #1/1/2000# And #12/31/2000#

or with a form reference:

Between [Forms]![YourFormName]![StartDate] And [Forms]![YourFormName]![EndDate]

 

[Brian62]

I tried your solution and it comes up with no records.
I have to capture what is open which will be yes (yes/no check box= unchecked) and what closures there were during any period of time for instance between 06/01/09 and 05/31/10.

I have to capture both to show on one report versus 2 that I have now through the query.

Activestudy
Where
=yes

Closure
Where
Between #6/1/2009# And #4/26/2010#

Also tried

Between [Enter Start Date Like: 01/01/09] And [Enter End Date Like: 12/31/09]
(Which I prefer to use)

I hope this helps.

 

[Brianwarnock]

In the criteria of the date field you should just need

(Between [Enter Start Date Like: 01/01/09] And [Enter End Date Like: 12/31/09]) or is null

Brian

 

[Brian62]

That worked with a little tweak. Thanks!

 

[Brianwarnock]

what tweak?

 

[Brian62]

Sorry. Your criteria worked, it was something else in the query I had to tweak to get all the records I needed. Thanks Again!

 

[Brianwarnock]

Ah! ok thanks for the update , just thought that I'd made a mistake.

brian