cross tab filter form

[Guest]

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in the form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.

 

[Duane Hookom]

You haven't set any criteria in the crosstab that we can see. I assume the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or many. You can
add a subform control to your form and set the Source Object to a query with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

 

[Guest]

yes my criteria is in the earlier query.

if run the crosstab query with paramerter it gives me result.

but i am not able generate subform with this query because in the wizard
it shows it does not have any fields.

as you told if i give this Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"
in this main forms load event...then i dont know how to proceed....
nothing showing in the subform...

also my user may select "All" in the combo how to select all the columns
if his selection is "All"


i am doing this kind of work first..please bear with me...
expecting your reply.

Regards

You haven't set any criteria in the crosstab that we can see. I assume the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or many. You can
add a subform control to your form and set the Source Object to a query with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

--
Duane Hookom
MS Access MVP
--

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in the form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.

 

[Duane Hookom]

Tell the Wizards to take the day off. Your form for setting the criteria
must be open.

Search Google Groups on this string
fsubone query "fsubone" group:*access* author:hookom

--
Duane Hookom
MS Access MVP
--

yes my criteria is in the earlier query.

if run the crosstab query with paramerter it gives me result.

but i am not able generate subform with this query because in the wizard
it shows it does not have any fields.

as you told if i give this Me.fsubOne.SourceObject =
"Query.qxtbMyCrosstab"
in this main forms load event...then i dont know how to proceed....
nothing showing in the subform...

also my user may select "All" in the combo how to select all the columns
if his selection is "All"


i am doing this kind of work first..please bear with me...
expecting your reply.

Regards

You haven't set any criteria in the crosstab that we can see. I assume
the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or many. You
can
add a subform control to your form and set the Source Object to a query
with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

--
Duane Hookom
MS Access MVP
--

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in the form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.

 

[Guest]

Yes it is working...thanks ....thanks.....

but why you are not helping me ..in this part
in my combo i have "All"

i tried this
.............IIf([Forms]![searchfrm]![stidcmb]='0',"*",[Forms]![searchfrm]![stidcmb])

IIf([Forms]![searchfrm]![stidcmb]='0',"Is null",[Forms]![searchfrm]![stidcmb])

'0' means "ALL" in combo

not working....please give me that trick.

i have 33 columns...i have put 33 column names column Heading...

also how can i show only one column (most of the time) and
all the columns...............Pleeaaase ........help me.

Best Regards

Tell the Wizards to take the day off. Your form for setting the criteria
must be open.

Search Google Groups on this string
fsubone query "fsubone" group:*access* author:hookom

--
Duane Hookom
MS Access MVP
--

yes my criteria is in the earlier query.

if run the crosstab query with paramerter it gives me result.

but i am not able generate subform with this query because in the wizard
it shows it does not have any fields.

as you told if i give this Me.fsubOne.SourceObject =
"Query.qxtbMyCrosstab"
in this main forms load event...then i dont know how to proceed....
nothing showing in the subform...

also my user may select "All" in the combo how to select all the columns
if his selection is "All"


i am doing this kind of work first..please bear with me...
expecting your reply.

Regards

You haven't set any criteria in the crosstab that we can see. I assume
the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or many. You
can
add a subform control to your form and set the Source Object to a query
with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

--
Duane Hookom
MS Access MVP
--

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in the form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.

 

[Tom Ellison]

Dear Ismail:

The way you have used "Is null" in your code is just the way you would
search for the literal string "Is null" and not how you can test whether the
column is null.

We need to switch to the SQL View of your query to do this in a newsgroup.
Text is all we have.

I believe what you need would read a bit like this:

WHERE (YourColumn IS NULL AND [Forms]![searchfrm]![stidcmb] = "0") OR
(YourColumn = [Forms]![searchfrm]![stidcmb] AND
[Forms]![searchfrm]![stidcmb] <> "0")

This uses just the strength of logic to perform what I understand to be what
you want.

Tom Ellison

Yes it is working...thanks ....thanks.....

but why you are not helping me ..in this part
in my combo i have "All"

i tried this
............IIf([Forms]![searchfrm]![stidcmb]='0',"*",[Forms]![searchfrm]![stidcmb])

IIf([Forms]![searchfrm]![stidcmb]='0',"Is
null",[Forms]![searchfrm]![stidcmb])

'0' means "ALL" in combo

not working....please give me that trick.

i have 33 columns...i have put 33 column names column Heading...

also how can i show only one column (most of the time) and
all the columns...............Pleeaaase ........help me.

Best Regards

Tell the Wizards to take the day off. Your form for setting the criteria
must be open.

Search Google Groups on this string
fsubone query "fsubone" group:*access* author:hookom

--
Duane Hookom
MS Access MVP
--

yes my criteria is in the earlier query.

if run the crosstab query with paramerter it gives me result.

but i am not able generate subform with this query because in the
wizard
it shows it does not have any fields.

as you told if i give this Me.fsubOne.SourceObject =
"Query.qxtbMyCrosstab"
in this main forms load event...then i dont know how to proceed....
nothing showing in the subform...

also my user may select "All" in the combo how to select all the
columns
if his selection is "All"


i am doing this kind of work first..please bear with me...
expecting your reply.

Regards


:

You haven't set any criteria in the crosstab that we can see. I assume
the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or many. You
can
add a subform control to your form and set the Source Object to a
query
with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

--
Duane Hookom
MS Access MVP
--

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in the
form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.

 

[Guest]

Thank you very much...it is working...
i spent lot of time on this believe me...around 6 hours....???

so i display single or multiple columns in the subform...now the tricky
part is .....

is it possible to display extra calculated fied in the subform..
if yes how..?

in normal form i display "totalsales" and calculated field "percentage "
{i call function preval....for previous value and then i calculate this
percentage}
same thing can i do here..in crosstab query's subform.

kindly help me.

Regards

Dear Ismail:

The way you have used "Is null" in your code is just the way you would
search for the literal string "Is null" and not how you can test whether the
column is null.

We need to switch to the SQL View of your query to do this in a newsgroup.
Text is all we have.

I believe what you need would read a bit like this:

WHERE (YourColumn IS NULL AND [Forms]![searchfrm]![stidcmb] = "0") OR
(YourColumn = [Forms]![searchfrm]![stidcmb] AND
[Forms]![searchfrm]![stidcmb] <> "0")

This uses just the strength of logic to perform what I understand to be what
you want.

Tom Ellison

Yes it is working...thanks ....thanks.....

but why you are not helping me ..in this part
in my combo i have "All"

i tried this
............IIf([Forms]![searchfrm]![stidcmb]='0',"*",[Forms]![searchfrm]![stidcmb])

IIf([Forms]![searchfrm]![stidcmb]='0',"Is
null",[Forms]![searchfrm]![stidcmb])

'0' means "ALL" in combo

not working....please give me that trick.

i have 33 columns...i have put 33 column names column Heading...

also how can i show only one column (most of the time) and
all the columns...............Pleeaaase ........help me.

Best Regards

Tell the Wizards to take the day off. Your form for setting the criteria
must be open.

Search Google Groups on this string
fsubone query "fsubone" group:*access* author:hookom

--
Duane Hookom
MS Access MVP
--

yes my criteria is in the earlier query.

if run the crosstab query with paramerter it gives me result.

but i am not able generate subform with this query because in the
wizard
it shows it does not have any fields.

as you told if i give this Me.fsubOne.SourceObject =
"Query.qxtbMyCrosstab"
in this main forms load event...then i dont know how to proceed....
nothing showing in the subform...

also my user may select "All" in the combo how to select all the
columns
if his selection is "All"


i am doing this kind of work first..please bear with me...
expecting your reply.

Regards


:

You haven't set any criteria in the crosstab that we can see. I assume
the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or many. You
can
add a subform control to your form and set the Source Object to a
query
with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

--
Duane Hookom
MS Access MVP
--

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in the
form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.

 

[Guest]

please help me...i can select single or multiple column only when i run the
query....

but in subform...no...i found the column which i want plus empty coulmn....

kindly look in to this matter....it will help me great...

Thanks in advance

Thank you very much...it is working...
i spent lot of time on this believe me...around 6 hours....???

so i display single or multiple columns in the subform...now the tricky
part is .....

is it possible to display extra calculated fied in the subform..
if yes how..?

in normal form i display "totalsales" and calculated field "percentage "
{i call function preval....for previous value and then i calculate this
percentage}
same thing can i do here..in crosstab query's subform.

kindly help me.

Regards

Dear Ismail:

The way you have used "Is null" in your code is just the way you would
search for the literal string "Is null" and not how you can test whether the
column is null.

We need to switch to the SQL View of your query to do this in a newsgroup.
Text is all we have.

I believe what you need would read a bit like this:

WHERE (YourColumn IS NULL AND [Forms]![searchfrm]![stidcmb] = "0") OR
(YourColumn = [Forms]![searchfrm]![stidcmb] AND
[Forms]![searchfrm]![stidcmb] <> "0")

This uses just the strength of logic to perform what I understand to be what
you want.

Tom Ellison

Yes it is working...thanks ....thanks.....

but why you are not helping me ..in this part
in my combo i have "All"

i tried this
............IIf([Forms]![searchfrm]![stidcmb]='0',"*",[Forms]![searchfrm]![stidcmb])

IIf([Forms]![searchfrm]![stidcmb]='0',"Is
null",[Forms]![searchfrm]![stidcmb])

'0' means "ALL" in combo

not working....please give me that trick.

i have 33 columns...i have put 33 column names column Heading...

also how can i show only one column (most of the time) and
all the columns...............Pleeaaase ........help me.

Best Regards






:

Tell the Wizards to take the day off. Your form for setting the criteria
must be open.

Search Google Groups on this string
fsubone query "fsubone" group:*access* author:hookom

--
Duane Hookom
MS Access MVP
--

yes my criteria is in the earlier query.

if run the crosstab query with paramerter it gives me result.

but i am not able generate subform with this query because in the
wizard
it shows it does not have any fields.

as you told if i give this Me.fsubOne.SourceObject =
"Query.qxtbMyCrosstab"
in this main forms load event...then i dont know how to proceed....
nothing showing in the subform...

also my user may select "All" in the combo how to select all the
columns
if his selection is "All"


i am doing this kind of work first..please bear with me...
expecting your reply.

Regards


:

You haven't set any criteria in the crosstab that we can see. I assume
the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or many. You
can
add a subform control to your form and set the Source Object to a
query
with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

--
Duane Hookom
MS Access MVP
--

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in the
form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.

 

[Duane Hookom]

I have created a form with a subform that dynamically displays a crosstab
query with varying column numbers. You can download the demo at
http://www.access.hookom.net/dynamsubform.zip.

--
Duane Hookom
MS Access MVP
--

please help me...i can select single or multiple column only when i run
the
query....

but in subform...no...i found the column which i want plus empty
coulmn....

kindly look in to this matter....it will help me great...

Thanks in advance

Thank you very much...it is working...
i spent lot of time on this believe me...around 6 hours....???

so i display single or multiple columns in the subform...now the tricky
part is .....

is it possible to display extra calculated fied in the subform..
if yes how..?

in normal form i display "totalsales" and calculated field "percentage "
{i call function preval....for previous value and then i calculate this
percentage}
same thing can i do here..in crosstab query's subform.

kindly help me.

Regards

Dear Ismail:

The way you have used "Is null" in your code is just the way you would
search for the literal string "Is null" and not how you can test
whether the
column is null.

We need to switch to the SQL View of your query to do this in a
newsgroup.
Text is all we have.

I believe what you need would read a bit like this:

WHERE (YourColumn IS NULL AND [Forms]![searchfrm]![stidcmb] = "0") OR
(YourColumn = [Forms]![searchfrm]![stidcmb] AND
[Forms]![searchfrm]![stidcmb] <> "0")

This uses just the strength of logic to perform what I understand to be
what
you want.

Tom Ellison


Yes it is working...thanks ....thanks.....

but why you are not helping me ..in this part
in my combo i have "All"

i tried this
............IIf([Forms]![searchfrm]![stidcmb]='0',"*",[Forms]![searchfrm]![stidcmb])

IIf([Forms]![searchfrm]![stidcmb]='0',"Is
null",[Forms]![searchfrm]![stidcmb])

'0' means "ALL" in combo

not working....please give me that trick.

i have 33 columns...i have put 33 column names column Heading...

also how can i show only one column (most of the time) and
all the columns...............Pleeaaase ........help me.

Best Regards






:

Tell the Wizards to take the day off. Your form for setting the
criteria
must be open.

Search Google Groups on this string
fsubone query "fsubone" group:*access* author:hookom

--
Duane Hookom
MS Access MVP
--

yes my criteria is in the earlier query.

if run the crosstab query with paramerter it gives me result.

but i am not able generate subform with this query because in the
wizard
it shows it does not have any fields.

as you told if i give this Me.fsubOne.SourceObject =
"Query.qxtbMyCrosstab"
in this main forms load event...then i dont know how to
proceed....
nothing showing in the subform...

also my user may select "All" in the combo how to select all the
columns
if his selection is "All"


i am doing this kind of work first..please bear with me...
expecting your reply.

Regards


:

You haven't set any criteria in the crosstab that we can see. I
assume
the
criteria is set in an earlier query.
It seems that your result could generate 1 column heading or
many. You
can
add a subform control to your form and set the Source Object to a
query
with
code like:

Me.fsubOne.SourceObject = "Query.qxtbMyCrosstab"

--
Duane Hookom
MS Access MVP
--

kindly help me in this issue..

i have a cross tab query

PARAMETRS [Forms]![searchfrm]![stidcmb] value;
TRANSFORM SUM(q1.totals) As sumoftotals
SELECT q1.yr, sum(q1.totals) as [Total Sales]
from q1

GROUP BY q1.yr
PIVOT q1.stid


user will select from combo stidcmb....
"Depends upon the user selection i want to show the result in
the
form
ie. only the particular column How can i do this.
also i have "all" value in the combo values..

Please kindly reply for this.