Query isn't working right using Between And

[jmriddic]

Hi,

I am trying to build a query that using the Between And phrase on a date field. The thing is is if I do between 08/01/03 and 08/07/03 it returns only one record. IF I do it with an end date of 08/08/03 it pulls the record for the 7th that I know is in there. Here is basically I want I am trying to do. I am trying to build reports that use a date range. So I am first trying to build queries to do this. Now the date field has a default value of Now(). That shouldn't matter So why is my Between and phrase not working right. Granted some of my reports are lists and some show Totals but that shouldn't matter as far as the date range. Here is a sample of onr query I am trying to do:

Name Date Entered Amount
Group by Where Group By
Between[Start Date:]And[End Date:]

For this I guess I want more of a list of every record entry but I still a date range. And I have tried every which way to get this to work. Any suggestions?

 

[Mile-O]

You say your field uses the Now() function...

When you enter a date on it's own (with no time) the date is evaluated as, in this case: 07-Aug-2003 00:00:00 so, as you can see, Between midnight on one date to midnight on the second date is essentially what you are asking for.

The dates you obviously want have their times after midnight on the 7th August so you'll need to use the DateValue() function:

i.e

Between DateValue([StartDate]) And DateValue([EndDate])

 

[dcx693]

I want more of a list of every record

, then perhaps you shouldn't be using a Totals query?

 

[jmriddic]

For some I want a list like the one i listed but some i need to do totals.

 

[dcx693]

Well, OK...but if you're grouping on something other than the date field as you indicated, then you might not get a listing that includes every date. In the example you posted, it looks like you're grouping by Name and Amount Entered. Is that right?

 

[daveUK]

In order to get the query to include records on the end date, you need to add '+1' to the query expression

i.e. Between [Start Date] And [End Date] +1

Dave

 

[jmriddic]

Correct. I see the problem there. But how could I pull the records for those dates. This date range is used on every query and everyone of them I now see will not pull correctly. Now I have a couple of queries where I am grouping on the first field(may be different fields, summing on the amount and using Where on the date and thats not working though theortically it should. And I did try the date value function to no avail. So what gives?

 

[jmriddic]

Dave,

Could you elaborate,

That syntax isn't right is it?

 

[Mile-O]

Days are stored as (long) integers - today is 37845.

By adding 1 to 37845, it effectively becomes the next day.

So, you are changing 07-Aug-2003 00:00:00 to 08-Aug-2003 00:00:00 which encapsulates all of the records within the 7th that you want to capture.

 

[jmriddic]

I mean the syntax itself is not right as written. I tried it and it gave me a error. So what's the proper way to write it.

 

[Mile-O]

It looks fine to me.

Try, however:

Between [Start Date] And ([End Date] + 1)

 

[jmriddic]

Doesn't work when I stick in that field. Give it a try. Only thing I get is an error.

 

[Jon K]

Switch to query SQL View and add the criteria as a Where Clause after the table name (using the correct date field name), e.g.

SELECT ..........
FROM [TableName]
WHERE DateValue([DateField]) Between [Start Date:] And [End Date:]

Notes.
The DateValue() function returns the date, ignoring the time.
The Where Clause also works in Totals Queries.

 

[blue_mercedes]

try this in criteria for date

=[Date]>=[StartDate] And [Date]<=[EndDate]

[Date] here is field in table

[StartDate] and [EndDate] here is date range required,

It should work. Done is countless times.

GOod Luck.

 

[Rich]

The Between operator is more efficient than > and <

 

[namliam]

Are you sure your pulling the right dates? Access sometimes acts up on dates...
you are trying to pull aug 1st thru aug 7, right?
well it might be access interprets that as jan 8th thru Jul 8th.

Make sure by checking a full full query
Select * from table where SomeDate between #Startdate# and #Enddate#

Between should INCLUDE both start and end dates! (beeing the same as >= Start and <= end)

Regards

The mailman

P.S. if you keep having problems post a sample... and we will see what we can do about it....