access report does not display all records in a query when filtere

G

Guest

I have the following query as my report record source. If I run this query
as is it will display all (correct number) records. However, if I run it
specifying a date range for [Check Request Recd Date], it does not display
the right amount of data. In fact it categorizes the data into the wrong
months as well (when the data is grouped by month).

SELECT [Claims Details].Job, Format(DMin("[Check Request Recd Date]","Claims
Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy") AS [Check Request Recd
Date], Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy");

Any help would be appreciated.

Thanks
 
D

Duane Hookom

You have converted your [Check Request Recd Date] to a text field, not a
date. Is this what you want? The way you are calculating your column, a
value of "28-JAN-2006" will not be between "01-JAN-2006" and "01-FEB-2006".

I generally find no good reason to format a date within a query.
 
G

Guest

That is not what I want. I want it to be a date field. The reason I had to
format it in the query is that I could not format the field in the report.
Could you help me with this. The field in the table is a date field.

Thanks

Duane Hookom said:
You have converted your [Check Request Recd Date] to a text field, not a
date. Is this what you want? The way you are calculating your column, a
value of "28-JAN-2006" will not be between "01-JAN-2006" and "01-FEB-2006".

I generally find no good reason to format a date within a query.
--
Duane Hookom
MS Access MVP

Kpatel said:
I have the following query as my report record source. If I run this query
as is it will display all (correct number) records. However, if I run it
specifying a date range for [Check Request Recd Date], it does not display
the right amount of data. In fact it categorizes the data into the wrong
months as well (when the data is grouped by month).

SELECT [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims
Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy") AS [Check Request Recd
Date], Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy");

Any help would be appreciated.

Thanks
Click to expand...
Click to expand...
 
D

Duane Hookom

I would change the SQL to the below. You should be able to format this in
your report.

SELECT [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" & [Job]
& "'"))
AS [Check Request Recd Date],
Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" & [Job]
& "'"));
--
Duane Hookom
MS Access MVP


Kpatel said:
That is not what I want. I want it to be a date field. The reason I had
to
format it in the query is that I could not format the field in the report.
Could you help me with this. The field in the table is a date field.

Thanks

Duane Hookom said:
You have converted your [Check Request Recd Date] to a text field, not a
date. Is this what you want? The way you are calculating your column, a
value of "28-JAN-2006" will not be between "01-JAN-2006" and
"01-FEB-2006".

I generally find no good reason to format a date within a query.
--
Duane Hookom
MS Access MVP

Kpatel said:
I have the following query as my report record source. If I run this
query
as is it will display all (correct number) records. However, if I run
it
specifying a date range for [Check Request Recd Date], it does not
display
the right amount of data. In fact it categorizes the data into the
wrong
months as well (when the data is grouped by month).

SELECT [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims
Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy") AS [Check Request
Recd
Date], Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy");

Any help would be appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I changed my query per your suggestion. It works. However, as soon as I try
to filter it by a date range it gives me "Invalid use of null" error. Here
is my query:
SELECT [Claims Details].Job, CDate(DMin("[Check Request Recd Date]","Claims
Details","[Job] = '" & [Job] & "'")) AS [Check Request Recd Date],
Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, CDate(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"))
HAVING (((CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] =
'" & [Job] & "'")))>=#1/1/2006# And (CDate(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'")))<=#1/31/2006#))
ORDER BY CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '"
& [Job] & "'"));

Can you help me with this?

Thanks

Duane Hookom said:
I would change the SQL to the below. You should be able to format this in
your report.

SELECT [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" & [Job]
& "'"))
AS [Check Request Recd Date],
Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" & [Job]
& "'"));
--
Duane Hookom
MS Access MVP


Kpatel said:
That is not what I want. I want it to be a date field. The reason I had
to
format it in the query is that I could not format the field in the report.
Could you help me with this. The field in the table is a date field.

Thanks

Duane Hookom said:
You have converted your [Check Request Recd Date] to a text field, not a
date. Is this what you want? The way you are calculating your column, a
value of "28-JAN-2006" will not be between "01-JAN-2006" and
"01-FEB-2006".

I generally find no good reason to format a date within a query.
--
Duane Hookom
MS Access MVP

I have the following query as my report record source. If I run this
query
as is it will display all (correct number) records. However, if I run
it
specifying a date range for [Check Request Recd Date], it does not
display
the right amount of data. In fact it categorizes the data into the
wrong
months as well (when the data is grouped by month).

SELECT [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims
Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy") AS [Check Request
Recd
Date], Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy");

Any help would be appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I expect you might not have some Claims details records for every Job or at
least not all jobs have a Check Request Recd Date. If so, make sure you
filter these out or handle them in some other manner.

Taking another look at your query, I don't know why you are using Dmin().
Doesn't this do what you want it to?

SELECT [Claims Details].Job,
Min([Check Request Recd Date]) AS [Check Request Recd Date],
Sum([Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE [Check Request Recd Date] is not Null
GROUP BY Job;

--
Duane Hookom
MS Access MVP

Kpatel said:
I changed my query per your suggestion. It works. However, as soon as I
try
to filter it by a date range it gives me "Invalid use of null" error.
Here
is my query:
SELECT [Claims Details].Job, CDate(DMin("[Check Request Recd
Date]","Claims
Details","[Job] = '" & [Job] & "'")) AS [Check Request Recd Date],
Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, CDate(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"))
HAVING (((CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] =
'" & [Job] & "'")))>=#1/1/2006# And (CDate(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'")))<=#1/31/2006#))
ORDER BY CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] =
'"
& [Job] & "'"));

Can you help me with this?

Thanks

Duane Hookom said:
I would change the SQL to the below. You should be able to format this in
your report.

SELECT [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" &
[Job]
& "'"))
AS [Check Request Recd Date],
Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" &
[Job]
& "'"));
--
Duane Hookom
MS Access MVP


Kpatel said:
That is not what I want. I want it to be a date field. The reason I
had
to
format it in the query is that I could not format the field in the
report.
Could you help me with this. The field in the table is a date field.

Thanks

:

You have converted your [Check Request Recd Date] to a text field, not
a
date. Is this what you want? The way you are calculating your column,
a
value of "28-JAN-2006" will not be between "01-JAN-2006" and
"01-FEB-2006".

I generally find no good reason to format a date within a query.
--
Duane Hookom
MS Access MVP

I have the following query as my report record source. If I run this
query
as is it will display all (correct number) records. However, if I
run
it
specifying a date range for [Check Request Recd Date], it does not
display
the right amount of data. In fact it categorizes the data into the
wrong
months as well (when the data is grouped by month).

SELECT [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims
Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy") AS [Check Request
Recd
Date], Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy");

Any help would be appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for all your time and effort. That worked just fine.


Duane Hookom said:
I expect you might not have some Claims details records for every Job or at
least not all jobs have a Check Request Recd Date. If so, make sure you
filter these out or handle them in some other manner.

Taking another look at your query, I don't know why you are using Dmin().
Doesn't this do what you want it to?

SELECT [Claims Details].Job,
Min([Check Request Recd Date]) AS [Check Request Recd Date],
Sum([Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE [Check Request Recd Date] is not Null
GROUP BY Job;

--
Duane Hookom
MS Access MVP

Kpatel said:
I changed my query per your suggestion. It works. However, as soon as I
try
to filter it by a date range it gives me "Invalid use of null" error.
Here
is my query:
SELECT [Claims Details].Job, CDate(DMin("[Check Request Recd
Date]","Claims
Details","[Job] = '" & [Job] & "'")) AS [Check Request Recd Date],
Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, CDate(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"))
HAVING (((CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] =
'" & [Job] & "'")))>=#1/1/2006# And (CDate(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'")))<=#1/31/2006#))
ORDER BY CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] =
'"
& [Job] & "'"));

Can you help me with this?

Thanks

Duane Hookom said:
I would change the SQL to the below. You should be able to format this in
your report.

SELECT [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" &
[Job]
& "'"))
AS [Check Request Recd Date],
Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job,
CDate(DMin("[Check Request Recd Date]","Claims Details","[Job] = '" &
[Job]
& "'"));
--
Duane Hookom
MS Access MVP


That is not what I want. I want it to be a date field. The reason I
had
to
format it in the query is that I could not format the field in the
report.
Could you help me with this. The field in the table is a date field.

Thanks

:

You have converted your [Check Request Recd Date] to a text field, not
a
date. Is this what you want? The way you are calculating your column,
a
value of "28-JAN-2006" will not be between "01-JAN-2006" and
"01-FEB-2006".

I generally find no good reason to format a date within a query.
--
Duane Hookom
MS Access MVP

I have the following query as my report record source. If I run this
query
as is it will display all (correct number) records. However, if I
run
it
specifying a date range for [Check Request Recd Date], it does not
display
the right amount of data. In fact it categorizes the data into the
wrong
months as well (when the data is grouped by month).

SELECT [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims
Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy") AS [Check Request
Recd
Date], Sum([Claims Details].[Check Amount]) AS [Total Paid]
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Recd Date]) Is Null))
GROUP BY [Claims Details].Job, Format(DMin("[Check Request Recd
Date]","Claims Details","[Job] = '" & [Job] & "'"),"dd-mmm-yyyy");

Any help would be appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Comparing records by year in a report 2
Parameter Report with a Chart in the footer 1
Sum in Report Errors 3
Design of Payroll Report (Gross Pay >=$25) 1
Using Access 2003 convert numeric month to text for reports 2
Query filter question (attempting crosstab query) 2
Report Date Not Being Displayed Properly 3
Report w/same number of duplicate identical pages as there are records 7

Top