Date Ranges still pulling all dates entered

tafnuef1

Registered User.
Local time
Today, 13:45
Joined
Apr 22, 2010
Messages
43
Ok I followed this example: http://www.allenbrowne.com/casu-08.html

I created a query from my data table. I then followed through to the report and set it up so it will ask for a date range.

However I am still getting data for all dates entered in both the query and the Report

How do I fix this?
 
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT DIQA.[Batch Number ID], DIQA.[Prepper Name], DIQA.[Date of Audit], DIQA.[Document Type], DIQA.[Document Count], Count([Audit Errors].[Prepper Error]) AS [CountOfPrepper Error], [Audit Errors].[Prepper Error]
FROM DIQA RIGHT JOIN [Audit Errors] ON (DIQA.DIQA=[Audit Errors].[DIQA #]) AND (DIQA.[Batch Number ID]=[Audit Errors].[Batch Number ID])
GROUP BY DIQA.[Batch Number ID], DIQA.[Prepper Name], DIQA.[Date of Audit], DIQA.[Document Type], DIQA.[Document Count], [Audit Errors].[Prepper Error]
HAVING (((DIQA.[Date of Audit])>=[StartDate]<[EndDate]+1) And (((DIQA.[Date of Audit])>=[StartDate])<[EndDate]+1))
ORDER BY DIQA.[Date of Audit];
 
Shouldn't be using Between for a from to date criteria?
 
Ghudson: Regarding your note below... Can you explain how it should state? sort of give me steps to where I fix this? I am a newbie to databases, forms and reports in Access but I have taught myself all three by getting help from folks here and it has been a blessing.. I just need to know where to fix this.

Re: Date Ranges still pulling all dates entered
Shouldn't be using Between for a from to date criteria?
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2007 with Windows XP

The Advanced Search function on this forum really does work. :cool:
.................................................. ......
 
Hi -

Try modifying statements like:

Code:
DIQA.[Date of Audit])>=[StartDate]<[EndDate]+1

to:

DIQA.[Date of Audit])>=[StartDate] [COLOR="Red"]and[/COLOR] <[EndDate]+1

While I deeply respect Allen Browne's knowledge and solutions, I can't make the where statement, as published, work. In my tests the statement is accepted but, as you pointed out, all records are returned -- no actual filtering appears to occur.

Please post back with your findings.

Best Wishes - Bob

Added:
Here's a working example I used based on Northwind's Orders table:
Code:
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate)>=#7/1/1995# And (Orders.OrderDate)<#7/31/1995#+1));
 
Last edited:

Users who are viewing this thread

Back
Top Bottom