Getting dates of parameters to appear in the query/report

[Guest]

With much help, I have set up the parameters to select a date range for my
query. Now I need the date range to appear on the query and subsequent
reports.

In the Expression Builder, if I click the Show box, I get an error: You
tried to execute a query that does not include the specified expression 'Date
Worked On' as part of an aggregate function. If I am reading that correctly,
this is the only place that I am using this field in my query, so it would
not be part of an aggregate function.

Here is the SQL that I am working with. I thank you in advance for all of
your assistance.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked]!Responsibility) AS [Total
Appeals], Sum([Resolved by hospice Date Worked]![Sales Amount]) AS [Total
Appealed Money], Sum([Resolved by hospice Date Worked]![Credit Amount]) AS
[Total Credits to Hospice], Sum([Resolved by hospice Date Worked]![Pharmacy
Credit]) AS [Total Pharmacy Credits], Sum([Sales Amount])-[Total Pharmacy
Credits]-[Total Credits to Hospice] AS [Total Non-Credits]
FROM [Resolved by hospice Date Worked]
WHERE ((([Resolved by hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));

 

[fredg]

With much help, I have set up the parameters to select a date range for my
query. Now I need the date range to appear on the query and subsequent
reports.

In the Expression Builder, if I click the Show box, I get an error: You
tried to execute a query that does not include the specified expression 'Date
Worked On' as part of an aggregate function. If I am reading that correctly,
this is the only place that I am using this field in my query, so it would
not be part of an aggregate function.

Here is the SQL that I am working with. I thank you in advance for all of
your assistance.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked]!Responsibility) AS [Total
Appeals], Sum([Resolved by hospice Date Worked]![Sales Amount]) AS [Total
Appealed Money], Sum([Resolved by hospice Date Worked]![Credit Amount]) AS
[Total Credits to Hospice], Sum([Resolved by hospice Date Worked]![Pharmacy
Credit]) AS [Total Pharmacy Credits], Sum([Sales Amount])-[Total Pharmacy
Credits]-[Total Credits to Hospice] AS [Total Non-Credits]
FROM [Resolved by hospice Date Worked]
WHERE ((([Resolved by hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));

I have no idea what you mean by "... need the date range to appear on
the query ..."
If the query is the record source for the report, no one will ever see
the query.

To show the date range in the report, add an unbound control to the
Report Header.
Set it's control source to:
="For sales between " & [Enter Start Date] & " And " & [Enter End
Date]

The text within the brackets must be identical to the bracketed text
in the query criteria.

 

[Guest]

On a form or report based directly on this query put the following in a text
box either in a header or footer:

="Between " & [Enter Start Date] & " And " & [Enter End Date]

To have them show up in each record of a query, put the following in the
select part of the SQL statement.

, [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate

I hate to say this, but finding yourself building query upon query upon
query to get an answer is often a good indication that your data is not
properly normalized.

 

[John Spencer]

If you really need them displayed in the query, try the following.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count(Responsibility) AS [Total Appeals]
, Sum([Sales Amount]) AS [Total Appealed Money]
, Sum([Credit Amount]) AS [Total Credits to Hospice]
, Sum([Pharmacy Credit]) AS [Total Pharmacy Credits]
, Sum([Sales Amount])-[Total Pharmacy Credits]-[Total Credits to Hospice] AS
[Total Non-Credits]
FROM [Resolved by hospice Date Worked]
GROUP BY [Enter Start Date], [Enter End Date]
WHERE [Date Worked on] Between [Enter Start Date] And [Enter End Date]

If you just want to use them on a report or display them on a form you can
use them just as if they were fields (and not bother adding them to the
query fields). They won't show up in the field list, but you should be able
to type them (exactly) whereever you would use a field reference from the
query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I'm sure that you are correct in your assessment that my data is not properly
normalized. I am hitting walls at every turn due to my inexperience and I
truly appreciate yours and everyone's assistance.

With a little trial and error on my part, your solution worked. Thank you
very much.

On a form or report based directly on this query put the following in a text
box either in a header or footer:

="Between " & [Enter Start Date] & " And " & [Enter End Date]

To have them show up in each record of a query, put the following in the
select part of the SQL statement.

, [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate

I hate to say this, but finding yourself building query upon query upon
query to get an answer is often a good indication that your data is not
properly normalized.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

With much help, I have set up the parameters to select a date range for my
query. Now I need the date range to appear on the query and subsequent
reports.

In the Expression Builder, if I click the Show box, I get an error: You
tried to execute a query that does not include the specified expression 'Date
Worked On' as part of an aggregate function. If I am reading that correctly,
this is the only place that I am using this field in my query, so it would
not be part of an aggregate function.

Here is the SQL that I am working with. I thank you in advance for all of
your assistance.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked]!Responsibility) AS [Total
Appeals], Sum([Resolved by hospice Date Worked]![Sales Amount]) AS [Total
Appealed Money], Sum([Resolved by hospice Date Worked]![Credit Amount]) AS
[Total Credits to Hospice], Sum([Resolved by hospice Date Worked]![Pharmacy
Credit]) AS [Total Pharmacy Credits], Sum([Sales Amount])-[Total Pharmacy
Credits]-[Total Credits to Hospice] AS [Total Non-Credits]
FROM [Resolved by hospice Date Worked]
WHERE ((([Resolved by hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));

 

[Guest]

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm sure that you are correct in your assessment that my data is not properly
normalized. I am hitting walls at every turn due to my inexperience and I
truly appreciate yours and everyone's assistance.

With a little trial and error on my part, your solution worked. Thank you
very much.

On a form or report based directly on this query put the following in a text
box either in a header or footer:

="Between " & [Enter Start Date] & " And " & [Enter End Date]

To have them show up in each record of a query, put the following in the
select part of the SQL statement.

, [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate

I hate to say this, but finding yourself building query upon query upon
query to get an answer is often a good indication that your data is not
properly normalized.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

With much help, I have set up the parameters to select a date range for my
query. Now I need the date range to appear on the query and subsequent
reports.

In the Expression Builder, if I click the Show box, I get an error: You
tried to execute a query that does not include the specified expression 'Date
Worked On' as part of an aggregate function. If I am reading that correctly,
this is the only place that I am using this field in my query, so it would
not be part of an aggregate function.

Here is the SQL that I am working with. I thank you in advance for all of
your assistance.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked]!Responsibility) AS [Total
Appeals], Sum([Resolved by hospice Date Worked]![Sales Amount]) AS [Total
Appealed Money], Sum([Resolved by hospice Date Worked]![Credit Amount]) AS
[Total Credits to Hospice], Sum([Resolved by hospice Date Worked]![Pharmacy
Credit]) AS [Total Pharmacy Credits], Sum([Sales Amount])-[Total Pharmacy
Credits]-[Total Credits to Hospice] AS [Total Non-Credits]
FROM [Resolved by hospice Date Worked]
WHERE ((([Resolved by hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));