By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,077 Members | 2,160 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,077 IT Pros & Developers. It's quick & easy.

Possible to use a crosstab query in a report (A97)?

P: n/a
pw
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
You can write code in the report's OnOpen event which will reassign
controlsources for textboxes.
It can also make extra textboxes invisible.
What you can't do at that point is to create new textboxes, so you'll need
to create the maximum possible number of textboxes first in design view.

HTH
- Turtle

"pw" <***paulwilliamson@***spamcop.net> wrote in message
news:o0********************************@4ax.com...
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---
Nov 13 '05 #2

P: n/a
On Wed, 26 May 2004 13:14:11 -0600, pw
<***paulwilliamson@***spamcop.net> wrote:

There is no simple way, as the number of columns is unknown.

There is a hard way: in the Report_Open event, inspect the
RecordSource's Fields collection, and bind the data at that time. Make
sure you have plenty of controls lined up. Set the unused ones to
invisible.

-Tom.

Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---


Nov 13 '05 #3

P: n/a
pw
>You can write code in the report's OnOpen event which will reassign
controlsources for textboxes.
It can also make extra textboxes invisible.
What you can't do at that point is to create new textboxes, so you'll need
to create the maximum possible number of textboxes first in design view.

HTH
- Turtle

Thanks Turtle. But what if I don't know the name of the fields that
the crosstab query will generate?

"pw" <***paulwilliamson@***spamcop.net> wrote in message
news:o0********************************@4ax.com.. .
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet

News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000

Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
Nov 13 '05 #4

P: n/a
pw
>On Wed, 26 May 2004 13:14:11 -0600, pw
<***paulwilliamson@***spamcop.net> wrote:

There is no simple way, as the number of columns is unknown.

There is a hard way: in the Report_Open event, inspect the
RecordSource's Fields collection, and bind the data at that time. Make
sure you have plenty of controls lined up. Set the unused ones to
invisible.

-Tom.


I appreciate your help Tom! Thank you so much!

Do you know of any "magic" 3rd party utility that can generate any
fancy reports like this?
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
Nov 13 '05 #5

P: n/a
I think Tom's answer covered this -
do you need to know more?

Or perhaps your case is not quite so complex as you present.

For example, if your crosstab's ColumnHeader is the name of the month, you
may not have any entries for a particular month, and thus not generate that
column.
But if you list all the months in the ColumnHeaders property, you will get a
column for each month, whether or not there is data.
This can vastly simplify creating reports.

HTH
- Turtle

"pw" <***paulwilliamson@***spamcop.net> wrote in message
news:9o********************************@4ax.com...
You can write code in the report's OnOpen event which will reassign
controlsources for textboxes.
It can also make extra textboxes invisible.
What you can't do at that point is to create new textboxes, so you'll needto create the maximum possible number of textboxes first in design view.

HTH
- Turtle

Thanks Turtle. But what if I don't know the name of the fields that
the crosstab query will generate?

"pw" <***paulwilliamson@***spamcop.net> wrote in message
news:o0********************************@4ax.com.. .
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet

News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000

Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption=---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet

News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---
Nov 13 '05 #6

P: n/a
On Wed, 26 May 2004 21:25:24 -0600, pw
<***paulwilliamson@***spamcop.net> wrote:

None that I know of. But a professional developer should be able to
write this code for you within a few days. Check out 'Microsoft
Solution Provider' in your yellow pages.

Another option: programmatically open the report in design view and
use CreateReportControl to build the report. I've done this in a
project. Requires the full version of Access, and MDB rather than MDE.

-Tom.
On Wed, 26 May 2004 13:14:11 -0600, pw
<***paulwilliamson@***spamcop.net> wrote:

There is no simple way, as the number of columns is unknown.

There is a hard way: in the Report_Open event, inspect the
RecordSource's Fields collection, and bind the data at that time. Make
sure you have plenty of controls lined up. Set the unused ones to
invisible.

-Tom.


I appreciate your help Tom! Thank you so much!

Do you know of any "magic" 3rd party utility that can generate any
fancy reports like this?
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---


Nov 13 '05 #7

P: n/a
pw,
You have to declare the column headings in the properties panel of the
query. As long as the column headings keep the same name you can bind the
report to the query. If they change then you would need code to set the
column heading property on the way to opening the report.

"pw" <***paulwilliamson@***spamcop.net> wrote in message
news:o0********************************@4ax.com...
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---
Nov 13 '05 #8

P: n/a
pw
>I think Tom's answer covered this -
do you need to know more?

Or perhaps your case is not quite so complex as you present.

For example, if your crosstab's ColumnHeader is the name of the month, you
may not have any entries for a particular month, and thus not generate that
column.
But if you list all the months in the ColumnHeaders property, you will get a
column for each month, whether or not there is data.
This can vastly simplify creating reports.

HTH
- Turtle
Thanks ya Turtle! :-)


"pw" <***paulwilliamson@***spamcop.net> wrote in message
news:9o********************************@4ax.com.. .
>You can write code in the report's OnOpen event which will reassign
>controlsources for textboxes.
>It can also make extra textboxes invisible.
>What you can't do at that point is to create new textboxes, so you'llneed >to create the maximum possible number of textboxes first in design view.
>
>HTH
> - Turtle
>


Thanks Turtle. But what if I don't know the name of the fields that
the crosstab query will generate?

>"pw" <***paulwilliamson@***spamcop.net> wrote in message
>news:o0********************************@4ax.com.. .
>> Hi,
>>
>> Is that possible if the crosstab query is dynamic? Doesn't seem so as
>> I have to specify the control source for the text boxes and the number
>> of columns may change, along with their field names.
>>
>> Just thought I'd take a shot in the dark.
>>
>>
>> -pw
>> remove astericks (*) from e-mail address
>> (use paulwilliamson at spamcop dot net)
>>
>>
>>
>>
>> ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
>News==----
>> http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000
>Newsgroups
>> ---= 19 East/West-Coast Specialized Servers - Total Privacy viaEncryption >=---
>


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet

News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000

Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
Nov 13 '05 #9

P: n/a
pw
>On Wed, 26 May 2004 21:25:24 -0600, pw
<***paulwilliamson@***spamcop.net> wrote:

None that I know of. But a professional developer
Like myself? <bg>
should be able to
write this code for you within a few days. Check out 'Microsoft
Solution Provider' in your yellow pages.
Another option: programmatically open the report in design view and
use CreateReportControl to build the report. I've done this in a
project. Requires the full version of Access, and MDB rather than MDE.

-Tom.

I'll try that Tom! Thanks!


On Wed, 26 May 2004 13:14:11 -0600, pw
<***paulwilliamson@***spamcop.net> wrote:

There is no simple way, as the number of columns is unknown.

There is a hard way: in the Report_Open event, inspect the
RecordSource's Fields collection, and bind the data at that time. Make
sure you have plenty of controls lined up. Set the unused ones to
invisible.

-Tom.


I appreciate your help Tom! Thank you so much!

Do you know of any "magic" 3rd party utility that can generate any
fancy reports like this?
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
Nov 13 '05 #10

P: n/a
pw
>pw,
You have to declare the column headings in the properties panel of the
query. As long as the column headings keep the same name you can bind the
report to the query. If they change then you would need code to set the
column heading property on the way to opening the report.

I get it Alan. Thanks!!

"pw" <***paulwilliamson@***spamcop.net> wrote in message
news:o0********************************@4ax.com.. .
Hi,

Is that possible if the crosstab query is dynamic? Doesn't seem so as
I have to specify the control source for the text boxes and the number
of columns may change, along with their field names.

Just thought I'd take a shot in the dark.
-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet

News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000

Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---


-pw
remove astericks (*) from e-mail address
(use paulwilliamson at spamcop dot net)


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.