Forms with parameters to pass to cross-tab queries

  • Thread starter Thread starter DeepTrouble
  • Start date Start date
D

DeepTrouble

Hi,
I have a cross-tab query built on another query. I have created a
form to display the cross-tab query. Now, I would like to restrict the
results of the cross-tab query by passing a couple of params to the
base query. Am just not getting anywhere. Can someone please help?
Here is some more info on this.

Base query: qrySLA Summary
Columns: apart from plenty of others, includes "Resolved Date &
Time" (Date/Time)

Cross-tab query: qry SLA Summary Cross-tab
columns: does *not* include "Resolved Date & Time"

Form: frmSLA Summary
Form is based on: qry SLA Summary Cross-tab
Parameters available in : 2 text fields (txtFromDate and txtToDate)

Now, I tried to reference the text fields from the base query. But, I
get an error - Jet database engine does not recognize [Forms]![frmSLA
Summary]!txtFromDate as a valid field or expression. I have also tried
to declare global variables and tried passing them as parameters -
doesnt work. I have tried bringing the "Resolved Date & Time" field
into the cross-tab query, but, that doesnt work either.

Can anyone tell me where I am being wrong? Thanks a lot in advance.
Will let you know if I discover anything.

Do let me know if you'd like more info.
 
hi,
Now, I tried to reference the text fields from the base query. But, I
get an error - Jet database engine does not recognize [Forms]![frmSLA
Summary]!txtFromDate as a valid field or expression.
Click to expand...
Hmm, this should work, when the form is displayed.
I have also tried
to declare global variables and tried passing them as parameters -
doesnt work.
Click to expand...
Maybe this is a further try worth:

Create a public function in a normal module:

Public Function FromDate() As Date

On Local Error Goto LocalError

FromDate = Now()
FromDate = Forms!frmSLASummary!txtFromDate

Exit Function

LocalError:
MsgBox Err.Description

End Function


Use it in your query for your condition like:


WHERE [DateFrom] > FromDate()

What does the error message say?


mfG
--> stefan <--
 
DeepTrouble said:
Hi,
I have a cross-tab query built on another query. I have created a
form to display the cross-tab query. Now, I would like to restrict the
results of the cross-tab query by passing a couple of params to the
base query. Am just not getting anywhere. Can someone please help?
Here is some more info on this.

Base query: qrySLA Summary
Columns: apart from plenty of others, includes "Resolved Date &
Time" (Date/Time)

Cross-tab query: qry SLA Summary Cross-tab
columns: does *not* include "Resolved Date & Time"

Form: frmSLA Summary
Form is based on: qry SLA Summary Cross-tab
Parameters available in : 2 text fields (txtFromDate and txtToDate)

Now, I tried to reference the text fields from the base query. But, I
get an error - Jet database engine does not recognize [Forms]![frmSLA
Summary]!txtFromDate as a valid field or expression. I have also tried
to declare global variables and tried passing them as parameters -
doesnt work. I have tried bringing the "Resolved Date & Time" field
into the cross-tab query, but, that doesnt work either.

Can anyone tell me where I am being wrong? Thanks a lot in advance.
Will let you know if I discover anything.

Do let me know if you'd like more info.
Click to expand...

If you right-click in the upper area of your crosstab query designer grid you
will see an option in the resulting menu called "Parameters". You might not
have ever known that was there because most Access queries do not need
parameters to be explicitly defined. They "just work".

Crosstab queries however DO need all parameters to be explicitly defined. You
can use that dialog to do so. They are so picky about this that even if you
feed another query that uses parameters into a crosstab query you will then be
forced to explicitly define the parameters in the other query before the
crosstab will work.

(it's not a bad idea to explicitly define them even when Access does not require
it)
 
Thanks guys,
Per Stefan's suggestion and got the contents of two text fields by
using function calls to get them. The text item was not getting
properly resolved in the criteria section of the query. Using two
function calls (FromDate(), and ToDate()), which essentially do the
same thing, the query criteria did not have any problem.

Much obliged and thanks again.

Deep
 
Back
Top