Date Paramter Query: Now I'm Worried

[amy3000]

Quite awhile ago I posted a question on how to get data on clients open within 2002. They may have been opened before 2002 and they may have been closed after 2002. I got a response, modified it and it seemed to be working ok. I have one query where it doesn't pull the right people. In the query I have the admission date in a field and the criteria:

>=[First Date of time period- mm/dd/yy] Or Is Null

Then there is the close date with the criteria:

<=[Last Date of time period- mm/dd/yy] Or Is Null

The dates I'm using are between 1/1/2002 and 12/31/2002. However, in one query I'm getting people who may have, for example been admitted and closed in 2001. They were not open at any time during 2002. I don't understand what's going since it seems to work fine in all my other queries. I would really appreciate some help with this. I'm weak in SQL by the way so use expressions. Thanks as always.

 

[KornGeek]

I'm not completely sure I understand what you're trying to do, but here's my best shot. If I understand correctly, you're using the lines of code above as criteria in a query.

Do you have both of these criteria on the same line in the query builder, or did you spread them across two criteria lines?

If you put them on two separate lines, the query returns the records that match either condition. If you put them on the same line, the query returns only the records that match both criteria.

Also, you might want to remove the &quot;-&quot; character. I'm not sure how Access handles that in parameter names. Because it can also be used as an operator, Access may not like it.

These are my best guesses at what might be causing your problems. Please keep us updated on your progress.

 

[JimRich]

Check your data in both time fields. Your query may be working but your data may be in error.

 

[Louiegee]

I've dealt with this same problem, here's the solution:

(BeginDate<= #12/31/02# or BeginDate is null) and (EndDate>=#1/1/02# or EndDate is null)

If it looks backwards to you, that's because it is. You check the begin date against the end of the date range you want and you check the end date against the start of the date range you want. Think about it, if it begins after your range then you don't want it. If it ends before your date range you don't want it.

I've been using this for years to test if the begin and end dates of one time period have any dates within another time period.
Period. heh heh.

I think this should help you.
LouieGee

 

[amy3000]

Well, I feel a little stupid because what I think was happening now, is that I was asking too much of one query. I had some criteria for other fields. When I removed those criteria then it handled the dates just fine.

 

[Louiegee]

If I'm not mistaken, the criteria you mentioned at the outset will only give you records where both the Admission date and the close date fall within the desired period. (Or records where one or the other or both is null).

Try this; verify that you are in fact getting dates that begin before your date range but end within or after it AND/OR you're getting dates that begin before or during your range and end after it. I don't think the criteria you have will do that correctly.

If the two expressions you show are on the same line in the Query grid they will be ANDed meaning BOTH will have to be true resulting in only records where BOTH fall into your range. Records would be excluded where the Admission date was in the range but the End date was not and viceversa.

If the two expressions are on different lines in the grid, (which I'm assuming is not the case), they will be OR'ed meaning EITHER *can* be true resulting in records being returned that satisfy one of your criteria but maybe not the other.

I could be wrong; I am every day; but I don't think your criteria will do *exactly* what you think it will.

LouieGee