query format question

[Guest]

im trying to create a query where i look up a particular set of dates between
a certain point in time. for instance i want to look up events that happened
between 12/12/2005 and 12/19/2005. whats the "formula" that i would use?

 

[Tom Ellison]

Dear Ryan (not Bryan):

Assuming you have a column which is date/time (I'll call it MyDateTime) you
can use a criterion that would read:

MyDateTime BETWEEN "12/12/2005" AND "12/19/2005"

Now, this will work well if there is no time recorded in the column. If
there are any rows where there is a time recorded, and if the date is
12/19/2005, then these rows will be excluded. Why? Because the date
12/19/2005 has an implied time component of 00:00:00. That is, it includes
only the first fraction of a second of the day 12/19/2005. Any date/time
values in the column that were entered without any time component will match
the 00:00:00 time and will be included.

This is a common problem with date/time data. One solution is to use a
different construction:

MyDateTime >= "12/12/2005" AND MyDateTime < "12/20/2005"

Please let me know if this helped, and if I can be of any further
assistance.

 

[Guest]

well access accepted the format but no information came up in the select
query window (not because there wansnt any info to post). more specifically
i have two fields "check in date" and a "check out date" and im trying to
find the days in between those the two stopping point dates (12/12/05 and
12/19/05). any more help would be appreciated and thanks for getting back to
me.

 

[Guest]

Don't you need to use the pound sign (#) around the date field instead of
double quotes?

Randall Arnold

 

[Tom Ellison]

Depends. I'm so used to MSDE rather than Jet, that wasn't on my mind. For
Jet, the # is correct, and probably the better answer. So, thanks for
adding that!