Forms with parameters to pass to cross-tab queries

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.
 
S

Stefan Hoffmann

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.
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.
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 <--
 
R

Rick Brandt

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.

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)
 
D

DeepTrouble

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
 

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

Top