Form and parameters problem 1

[ColdDay]

I am wanting to be able to enter the date ranges in a form named “CrossTabQueries” and then click a button to run a query and produce a report. “CrossTabQueries” is a tab page.

When I run the query (or try to save or close it), the parameter boxes for the Begin Date and End Date pop up. I do not want the parameter boxes. I want the info from the form used as the parameters.

When the boxes pop up they display (w/o the quotes) “frm_aaa_TabbedPagesPractice.tab_Pages.CrossTabQueries.txt_CrossTabBeginDate”
for the Begin Date and
“frm_aaa_TabbedPagesPractice.tab_Pages.CrossTabQueries.txt_CrossTabEndDate” for the End Date.

When I do enter dates into the parameter boxes, the query does pull and display the data as desired in a data sheet, but not a report. I do have a report right now that shows all records, not just those in the requested date range. If I can get the query to work, I’m confident I can get the report to display properly.

The record source for the report is: qry_Crosstab_ByDate_ByCategory.

The query is pulling info from only one table: tbl_DisruptionDetails.

How do I get the info from the form text boxes to be used and not the parameter pop up boxes?

Here is my SQL:

Parameters[frm_aaa_TabbedPagesPractice].[tab_Pages].[CrossTabQueries].[txt_CrossTabBeginDate] Date,
[frm_aaa_TabbedPagesPractice].[tab_Pages].[CrossTabQueries].[txt_CrossTabEndDate] Date;
TRANSFORM Count(tbl_DisruptionDetails.Category) AS CountOfCategory
SELECT tbl_DisruptionDetails.ReportDate, Count(tbl_DisruptionDetails.Category) AS [Total Of Category]
FROM tbl_DisruptionDetails
WHERE (((tbl_DisruptionDetails.ReportDate) Between [frm_aaa_TabbedPagesPractice].[tab_Pages].[CrossTabQueries].[txt_CrossTabBeginDate] And [frm_aaa_TabbedPagesPractice].[tab_Pages].[CrossTabQueries].[txt_CrossTabEndDate]))
GROUP BY tbl_DisruptionDetails.ReportDate
PIVOT tbl_DisruptionDetails.Category;

Thanks.

 

[PHV]

I'd try this:

Parameters [!][Forms]![aaa_TabbedPagesPractice]![CrossTabQueries]![txt_CrossTabBeginDate][/!] Date,
[!][Forms]![aaa_TabbedPagesPractice]![CrossTabQueries]![txt_CrossTabEndDate][/!] Date;
TRANSFORM Count(tbl_DisruptionDetails.Category) AS CountOfCategory
SELECT tbl_DisruptionDetails.ReportDate, Count(tbl_DisruptionDetails.Category) AS [Total Of Category]
FROM tbl_DisruptionDetails
WHERE (((tbl_DisruptionDetails.ReportDate) Between [!][Forms]![aaa_TabbedPagesPractice]![CrossTabQueries]![txt_CrossTabBeginDate][/!] And [!][Forms]![aaa_TabbedPagesPractice]![CrossTabQueries]![txt_CrossTabEndDate][/!]))
GROUP BY tbl_DisruptionDetails.ReportDate
PIVOT tbl_DisruptionDetails.Category

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

Same result. It is selecting the range of info and displaying it in a datasheet, but it keeps popping up the parameter boxes.

 

[dhookom]

Are there any subforms involved in this?

The references to the controls on a main form would be:

Forms!YourFormName!YourControlName

You should also include all possible Categories in the Column Headings property.

Duane
Hook'D on Access
MS Access MVP

 

[ColdDay]

No subforms on the tab page “CrossTabQueries”. The only subforms are on another tab page “Tables” that has lookup tables only. No records.

The text boxes for the dates are on the tab page “CrossTabQueries”.

When entering dates in parameter boxes that cover all records, then all categories appear in data sheet, as they should. When narrowing down the dates, only those categories that are between the beginning and end dates appear, as they should.

Changed the code to the following with same result:

PARAMETERS forms!frm_aaa_TabbedPagesPractice!tab_Pages!CrossTabQueries!txt_CrossTabBeginDate DateTime, forms!frm_aaa_TabbedPagesPractice!tab_Pages!CrossTabQueries!txt_CrossTabEndDate DateTime;
TRANSFORM Count(tbl_DisruptionDetails.Category) AS CountOfCategory
SELECT tbl_DisruptionDetails.ReportDate, Count(tbl_DisruptionDetails.Category) AS [Total Of Category]
FROM tbl_DisruptionDetails
WHERE (((tbl_DisruptionDetails.ReportDate) Between forms!frm_aaa_TabbedPagesPractice!tab_Pages!CrossTabQueries!txt_CrossTabBeginDate And forms!frm_aaa_TabbedPagesPractice!tab_Pages!CrossTabQueries!txt_CrossTabEndDate))
GROUP BY tbl_DisruptionDetails.ReportDate
PIVOT tbl_DisruptionDetails.Category;

“You should also include all possible Categories in the Column Headings property.” Is this for consistency in the appearance of the reports so all cat’s are listed and if count is zero then insert a zero, as opposed to not showing a cat at all in the report?

Thanks.

 

[PHV]

No subforms on the tab page
So, I'd try this:

PARAMETERS [!]Forms!frm_aaa_TabbedPagesPractice!txt_CrossTabBeginDate[/!] DateTime, [!]Forms!frm_aaa_TabbedPagesPractice!txt_CrossTabEndDate[/!] DateTime;
TRANSFORM Count(tbl_DisruptionDetails.Category) AS CountOfCategory
SELECT tbl_DisruptionDetails.ReportDate, Count(tbl_DisruptionDetails.Category) AS [Total Of Category]
FROM tbl_DisruptionDetails
WHERE (((tbl_DisruptionDetails.ReportDate) Between [!]Forms!frm_aaa_TabbedPagesPractice!txt_CrossTabBeginDate[/!] And [!]Forms!frm_aaa_TabbedPagesPractice!txt_CrossTabEndDate[/!]))
GROUP BY tbl_DisruptionDetails.ReportDate
PIVOT tbl_DisruptionDetails.Category

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

With no subforms on the tab page, left out the
“!CrossTabQueries” part as suggested.

Works!

Thanks again!