Making a query understand that a date doesn’t exist yet? 1

[sarajini]

Hi All,
I'd like to run a query on records with both open dates and close dates. (I would appreciate any tips you could give me!!!!)

The query should spit back all the cases that were “open” during certain time periods.
For example, the query would show cases open during May '03.

1) The “open date” would be AFTER 4/30/03 and would be BEFORE 6/1.
2) The “close date” for all the cases would be AFTER 6/1 (OR blank, since some cases would have no close dates at all.)

I already have some of the query set up.
Under the field for Open Date, I wrote <#6/01/03#.
Under the field for Close Date, I wrote >#6/01/03#.

So, this gives me all the cases that were both opened before 6/1/03, and closed after 6/1/03. That’s part of what I want.

But I need the query to expand. My query doesn’t spit back cases with a) open date was before 6/1 AND b) no close date at all.

How do I tell Access that I want the criterion for CloseDate to be “after 6/1 OR does not exist”? I tried entering “CloseDate = 0” as a criteria, but that didn’t work.

If you’ve read through all of this, thanks! If you have any tips, thanks very much!!!
Sincerely,
sarajini

 

[psemianonymous]

I have the solution:

Put your criteria on two lines.

Your first line is fine as is. On the second line put

[OpenDate]              [CloseDate]
<#6/01/03#              >#6/01/03#

<#6/01/03#              Is Null

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[sarajini]

Hi...that was definitely less complicated than I expected!
Thanks very much for telling me about that!
sarajini

 

[OnTheFly]

I would think you would want to use the Between operator for the OpenDate (i.e. Between #5/1/2003# And #5/31/2003#) or else you would get those items open before 4/30/2003.

 

[sarajini]

Thanks for looking out, onthefly, but the query needs to display cases that have been opened either during or before May. (they still count as &quot;open&quot; during the month of may). so far the query is working--but i may be coming back for more help soon!