Asking for Date Parameters twice 1

[dawnd3]

Hello, I have a crosstab query as a record source for a report. In the record source for the crosstab query I have date parameters in the criteria for the date field. In the report I reference those parameters in the report header so that it prints the dates that it is reporting on. I have done this a ton of times, but this time for some reason it is prompting me for the date twice. Why would this happen? Is it something to do with the Crosstab query? This is in Access 2000.

Thank you,

Dawn

http://www.calliopeconsulting.net

 

[THWatson]

Dawn
If the parameter works fine in the query, this generally means that you have made a small, probably very small, spelling error in stating the parameter in the report.

Either that or you deleted a parameter from the query design grid but not from the Query Parameters dialog box.

Tom

 

[dhookom]

As per your other thread.
Create columns (row headings) in your crosstab with:
FromDate:[Enter FROM date - dd/mm/yyyy]
ToDate:[Enter TO date - dd/mm/yyyy]
You can use these like any other field/column in your query.

Duane
MS Access MVP

 

[dawnd3]

Duane and Tom,

Does the parameter prompt HAVE to be in the crosstab (the one that is the datasource for the report?) or can it be in any of the underlying queries?

Tom, I checked and double checked and there isn't any spelling errors.

Thanks to you both!!

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

I think you can set the parameters in the underlying queries or in the crosstab. The reason you were prompted twice is because the crosstab isn't passing the parameters. That is fixed by the method I suggested.

Duane
MS Access MVP

 

[dawnd3]

Thanks Duane, I did what you said and it still occasionally asks me twice for the parameter. Usually the first time I run the report when I open the database. The strange part is that I created 2 fields [Begin] and [End] in the underlying query, that I used in the report instead of the parameter prompts, [enter beginning date] and [Enter Ending Date]. So, I am really stumped as to why it is asking me twice. Not only that, it is prompting on close and when I go into design view. Wanna see it? I am really frustrated here and I have to deliver this tomorrow.

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[Absalom]

Maybe this will help dawnD3.

Open the query that you have based your report on.

Open the 'Show Table' dialog (Query/Show Table).

Click on the 'Queries' tab and add to your query, a copy of the parameter query you want to use.

Insert an appropriate link from the parameter query to one of your existing tables, and there you are.

The data in you parameter query should now flow through into the report query and you will be asked for the information only once.

Maintenance Planner in a Steel Mill

 

[dhookom]

Dawn,
Please paste the SQL of your query(s) into a posting so we can see what you are working with. It is nearly impossible to troubleshoot without additional information.

Duane
MS Access MVP

 

[dawnd3]

Ok, this is the first query:

PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
SELECT tblUsage.lngAgencyID, tblUsage.lngConfRoomID, tblUsage.dtmDateUsed, tblUsage.numHoursUsed, tblUsage.strTimeUsed, DateDiff("d",[Enter Beginning Date],[Enter Ending Date])/7 AS TotalWeeks, [Enter Beginning Date] AS [Enter Beginning Date], [Enter Ending Date] AS [Enter Ending Date]
FROM tblUsage
WHERE (((tblUsage.dtmDateUsed)>=[Enter Beginning Date] And (tblUsage.dtmDateUsed)<=[Enter Ending Date]) AND ((tblUsage.strTimeUsed) Is Not Null))
WITH OWNERACCESS OPTION;

This is the 2nd query:

SELECT tblConfRoom.strConfRoom, qryConfUsagePre.numHoursUsed, qryConfUsagePre.strTimeUsed, qryConfUsagePre.TotalWeeks, qryConfUsagePre.[Enter Beginning Date], qryConfUsagePre.[Enter Ending Date]
FROM qryConfUsagePre INNER JOIN tblConfRoom ON qryConfUsagePre.lngConfRoomID = tblConfRoom.lngConfRoomID
WITH OWNERACCESS OPTION;

And this is the 3rd (the datasource for the report)

TRANSFORM Sum(qryConfUsage.numHoursUsed) AS SumOfnumHoursUsed
SELECT qryConfUsage.strConfRoom, qryConfUsage.TotalWeeks, qryConfUsage.[Enter Beginning Date], qryConfUsage.[Enter Ending Date]
FROM qryConfUsage
GROUP BY qryConfUsage.strConfRoom, qryConfUsage.TotalWeeks, qryConfUsage.[Enter Beginning Date], qryConfUsage.[Enter Ending Date]
PIVOT qryConfUsage.strTimeUsed;

Thank you for your time.

Dawn

P.S. I have reverted back to using [Enter Beginning Date] and [Enter ending date] all the way through, even in the report. (so disregard what I said earlier about creating new fields [Begin] and [End].

http://www.calliopeconsulting.net

 

[dhookom]

Try these SQLs. Ideally, I would never use parameter queries. It is much more flexible to use references to controls on forms.

PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
SELECT [Enter Beginning Date] as BeginDate, [Enter Ending Date] as EndDate,
lngAgencyID, lngConfRoomID, dtmDateUsed, numHoursUsed, strTimeUsed,
DateDiff(&quot;d&quot;,[Enter Beginning Date],[Enter Ending Date])/7 AS TotalWeeks,
FROM tblUsage
WHERE dtmDateUsed Between [Enter Beginning Date] And [Enter Ending Date]
AND strTimeUsed Is Not Null
WITH OWNERACCESS OPTION;

SELECT tblConfRoom.strConfRoom, qryConfUsagePre.numHoursUsed,
qryConfUsagePre.strTimeUsed, qryConfUsagePre.TotalWeeks,
qryConfUsagePre.[BeginDate], qryConfUsagePre.[EndDate]
FROM qryConfUsagePre INNER JOIN tblConfRoom ON
qryConfUsagePre.lngConfRoomID = tblConfRoom.lngConfRoomID
WITH OWNERACCESS OPTION;

TRANSFORM Sum(numHoursUsed) AS SumOfnumHoursUsed
SELECT strConfRoom, TotalWeeks, [BeginDate], [EndDate]
FROM qryConfUsage
GROUP BY strConfRoom, TotalWeeks, [BeginDate], [EndDate]
PIVOT qryConfUsage.strTimeUsed;


Duane
MS Access MVP

 

[dawnd3]

Duane, ironically this is occuring in 2 DB's I am currently working on. In the 2nd DB I did use references to controls on forms, as you suggested above. Still had the same issue, and sometimes the query wouldn't recognize the reference untill I did the Query menu/Parameters option. Anyway, another strange problem occured, and I will try to explain. The end result is a report that pulls from the crosstab. Since I made changes, like inserting the Query parameters information, the data does not show up in the report. The headings are correct, so you can tell that it is pulling the correct date range, but the data is blank. (but the underlying crosstab is fine.) I really don't know how best to explain this and was hoping that I can show it to someone to help me out. I am at a loss.

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

Crosstabs always require query|parameters. Are you suggesting that you have a query that returns records and the same query when used as the report's record source doesn't show records?

Duane
MS Access MVP

 

[dawnd3]

I am saying that the crosstab query, that is the reports record sourse IS returning records, but the report itself is not.

Thanks,

Dawn (p.s. the star was from me.

http://www.calliopeconsulting.net

 

[dhookom]

Thanks for the star. Do you have any filter set on your report? Are you opening the report with code that applies a where clause?

Duane
MS Access MVP

 

[dawnd3]

Hi there, I don't think so. Here is the code on the on open of the report:

Private Sub Report_Open(Cancel As Integer)
Dim rst As Recordset, qdf As QueryDef
Dim fld As Field, ctl As Control
Dim strMos As String
Dim dblocal As Database
Set dblocal = CurrentDb()

Set qdf = dblocal.querydefs(&quot;qryCrosstab&quot
For Each ctl In Me.Controls
If Left(ctl.Name, 3) = &quot;fld&quot; Then
For Each fld In qdf.Fields
If fld.Name = Right(ctl.Name, 3) Then
strMos = Right(ctl.Name, 3)
ctl.ControlSource = fld.Name
' If strMos = &quot;jan&quot; Then
Me.Controls(strMos & &quot;Inv&quot.ControlSource = &quot;=IIF([Type]='Invs',[&quot; & strMos & &quot;])&quot;
Me.Controls(strMos & &quot;Cks&quot.ControlSource = &quot;=IIF([Type]='Chks',[&quot; & strMos & &quot;])&quot;
Me.Controls(strMos & &quot;Pct&quot.ControlSource = &quot;=iif(nz([&quot; & strMos & &quot;Inv],0)=0,'',[&quot; & strMos & &quot;Inv] / [&quot; & strMos & &quot;Cks])&quot;
'End If
End If
Next
End If
Next

End Sub

And here is the crosstab query it is based on:


PARAMETERS [forms]![frmConfig]![dtmBegDate] DateTime, [forms]![frmConfig]![dtmEndDate] DateTime;
TRANSFORM Sum(qryUnionChkInv.CountOfCHECKNUM) AS SumOfCountOfCHECKNUM
SELECT qryUnionChkInv.Year, qryUnionChkInv.Type
FROM qryUnionChkInv
GROUP BY qryUnionChkInv.Year, qryUnionChkInv.Type
PIVOT qryUnionChkInv.Month
WITH OWNERACCESS OPTION;

Thanks again,

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

I would not use code to set the control sources like your solution. I find it inflexible. Decide how may columns of months you want and then use a solution like this suggestion from a previous posting of mine.

Try not to use &quot;absolute&quot; column headings for dates. You could possibly use &quot;relative&quot; dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:&quot;Mth&quot; & DateDiff(&quot;m&quot;,[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth11
Build your report based on these &quot;relative&quot; months. If you need column labels in your report, use text boxes:
=DateAdd(&quot;m&quot;,0,Forms!frmA!txtEndDate)
=DateAdd(&quot;m&quot;,-1,Forms!frmA!txtEndDate)
=DateAdd(&quot;m&quot;,-2,Forms!frmA!txtEndDate)
=DateAdd(&quot;m&quot;,-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.


Duane
MS Access MVP