Querying Between Dates

[Jim W]

please dont laugh

Why when I use a query with the Between [Date1] And [Date 2]
the results do not have the Date 1 or Date 2 just the Between Dates. How do I get the Dates?

Jim
Sorry for the stupid question I just can't figure this one out

 

[WayneRyan]

Jim,

I take it that you are using "Between [Date1] And [Date 2]"
as the criteria for some date field. If they are "short" dates,
with no time element, then you WILL get the first date included,
but you won't get the last date because you are only getting
one microsecond of Date2.

Use [Date 2] + 1 and you should be all right.

Wayne

 

[Jim W]

Wayne should it be like this

Between [Start Date] AND [End Date] + 1

?

Jim

 

[WayneRyan]

Jim,

Yes,

Wayne

 

[Jim W]

Wayne tried but get a expression error when I try to run with +1 added????

Jim

 

[Mile-O]

Where are you getting [Date1] and [Date2] from? A function, an expression, or a form?

 

[Jim W]

From a form

Jim

 

[WayneRyan]

Jim,

Between Forms![YourForm]![Date 1] And Forms![YourForm]![Date 2] + 1

Wayne

 

[Jim W]

Wayne this is what I have

Between Forms![Frm-Availability by System]![Start Date] And Forms![Frm-Availability by System]![End Date] + 1

When I run the query I get and error saying the experssion is typed incorrectly or to complex to be evaluated

Jim

 

[WayneRyan]

Jim,

Can you Compact, ZIP and post it here?

That would be easier than trying to re-create the situation.

Wayne

 

[Mile-O]

Between CDate([Forms]![Frm-Availability by System]![Start Date]) And CDate([Forms]![Frm-Availability by System]![End Date])

Or:

Between CDate([Forms]![Frm-Availability by System]![Start Date]) And DateAdd("s",-1,DateAdd("d",1,CDate([Forms]![Frm-Availability by System]![End Date]))

 

[Jim W]

Looks like the CDate works using the +1. Do I need to have a -1 for the start Date?

Jim

 

[WayneRyan]

Jim,

Sorry, I had to leave for a while.

A guy that I work with used the [Date 2] + 1 method and it
worked. Oh well, I'll have to check on that later.

I see that you and Mile got it working.

You don't need the "- 1" on [Date 1] because the short date
field defaults to the start of the day.

Wayne

 

[Rose412]

I take it that you are using "Between [Date1] And [Date 2]"as the criteria for some date field. If they are "short" dates,with no time element, then you WILL get the first date included,but you won't get the last date because you are only gettingone microsecond of Date2.

Use [Date 2] + 1 and you should be all right.

I have been using [DateField] between [Date1] And [Date 2] in my queries. The field contains pure dates, with no time element. Both the start date and the end date are always included in my query results. I have no problems at all.

I'm a little confused. When should one use [Date 2]+1?

 

[Jim W]

Wayne, I follow you. Thank you for all the support.

Jim

 

[Jim W]

Rose are you sure that you get records that are for the Date 2.
My problem was that if I ran the query Between 12/10/03 the 12/16/03 records that were entered on 12/16/03 would not show up unless I entered 12/17/03. But with the +1 is works fine.

Jim

 

[Rose412]

Jim I'm absolutely sure. In the attached Access 2000 database, I have a table with these sample records:
ID DateEntered
1 12/9/2003
2 12/10/2003
3 12/14/2003
4 12/15/2003
5 12/16/2003
6 12/17/2003


When I ran the query:
SELECT tblData.ID, tblData.DateEntered
FROM tblData
WHERE (((tblData.DateEntered) Between #12/10/2003# And #12/16/2003#));

I got:
ID DateEntered
2 12/10/2003
3 12/14/2003
4 12/15/2003
5 12/16/2003


You can try it and see if you get the same results as mine.

Rose

 

[WayneRyan]

Rose,

I tried it and even changed the format in the table and query
to Short Date. And it works!

I know that I have seen this "not work" before and the
solution was to add one day to the ending value.

Thanks (I think), now I'm really confused. Maybe somebody
else will have some thoughts on it.

Wayne

 

[Jim W]

I'm not sure but I think that my problem was due to the fact that I was quering a field with both a Date and Time using the Between [Date 1] and [Date 2] and a record with a Date entered that looked like this 11/16/03 11:00AM so if Date 2 was 11/16/03 then access uses 11/16/03 00:01 so that record would not show up. But this is just a thought not 100% sure.

Jim

 

[WayneRyan]

Jim,

Wow, it has been a long week! The situation that you just
described is what I thought we were talking about. Your
data was date/time, but your query criteria was "Short Date".
That is the case where you must add 1 day to the second
short date.

Rose's example compared short date to short date and
mystified me until I read your last post.

Wayne