crosstab qry as report source breaks Access2007

We have 3 reports whose record sources are crosstab queries.  All have work
fine on the pc's running Access2003.  But, the one pc running Access2007
doesn't like the crosstab queries as the report source.  We don't get an
error - only the dreaded 'Access has encountered a problem and has closed...'
It totally shuts down the access application.  I stepped through the little
bit of onOpen code while opening the reports and all works fine.  I open the
crosstab query and it opens just fine.  To test the theory that it is a
crosstab source problem, I tried to recreate one of the reports and when any
crosstab query was selected as the source there were no fields to choose from.
Is this a known problem in Access2007 or some new setting I'm not aware of?
Any suggestions appreciated.

-- 
TIA

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200708/1

0
backToASP
8/9/2007 5:49:31 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
885 Views

Similar Articles

[PageSpeed] 44

Yes: if you programmatically assign fields to controls in Report_Open, 
Access 2007 will crash.

We have this issue listed here:
    http://allenbrowne.com/Access2007.html#Bugs

Hopefully MS will fix it in Service Pack 1.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"backToASP via AccessMonster.com" <u27805@uwe> wrote in message
news:76727dab74023@uwe...
> We have 3 reports whose record sources are crosstab queries.  All have 
> work
> fine on the pc's running Access2003.  But, the one pc running Access2007
> doesn't like the crosstab queries as the report source.  We don't get an
> error - only the dreaded 'Access has encountered a problem and has 
> closed...'
> It totally shuts down the access application.  I stepped through the 
> little
> bit of onOpen code while opening the reports and all works fine.  I open 
> the
> crosstab query and it opens just fine.  To test the theory that it is a
> crosstab source problem, I tried to recreate one of the reports and when 
> any
> crosstab query was selected as the source there were no fields to choose 
> from.
> Is this a known problem in Access2007 or some new setting I'm not aware 
> of?
> Any suggestions appreciated. 

0
Allen
8/10/2007 12:42:48 AM
Don't tease, Duane!  :-)

Perhaps you are talking about this link:
    http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:DDFBBC08-1F82-415B-9094-B03AFF749F6E@microsoft.com...
> There is at least one method/solution for creating crosstab reports 
> without
> first knowing any of the field names and without having to assign control
> sources at run-time.
> -- 
> Duane Hookom
> Microsoft Access MVP
>
>
> "Allen Browne" wrote:
>
>> Yes: if you programmatically assign fields to controls in Report_Open,
>> Access 2007 will crash.
>>
>> We have this issue listed here:
>>     http://allenbrowne.com/Access2007.html#Bugs
>>
>> Hopefully MS will fix it in Service Pack 1.
>>
>> "backToASP via AccessMonster.com" <u27805@uwe> wrote in message
>> news:76727dab74023@uwe...
>> > We have 3 reports whose record sources are crosstab queries.  All have
>> > work
>> > fine on the pc's running Access2003.  But, the one pc running 
>> > Access2007
>> > doesn't like the crosstab queries as the report source.  We don't get 
>> > an
>> > error - only the dreaded 'Access has encountered a problem and has
>> > closed...'
>> > It totally shuts down the access application.  I stepped through the
>> > little
>> > bit of onOpen code while opening the reports and all works fine.  I 
>> > open
>> > the
>> > crosstab query and it opens just fine.  To test the theory that it is a
>> > crosstab source problem, I tried to recreate one of the reports and 
>> > when
>> > any
>> > crosstab query was selected as the source there were no fields to 
>> > choose
>> > from.
>> > Is this a known problem in Access2007 or some new setting I'm not aware
>> > of?
>> > Any suggestions appreciated. 

0
Allen
8/10/2007 2:00:39 PM
Allen,
That solution is one I always recommend for date intervals since dates are 
actually numeric. The one I had in mind is hosted over at Roger's site:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Cross%20Tab'

This solution uses much less code and is much more efficient than the 
solution that assigns control sources at run-time.

-- 
Duane Hookom
Microsoft Access MVP


"Allen Browne" wrote:

> Don't tease, Duane!  :-)
> 
> Perhaps you are talking about this link:
>     http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> news:DDFBBC08-1F82-415B-9094-B03AFF749F6E@microsoft.com...
> > There is at least one method/solution for creating crosstab reports 
> > without
> > first knowing any of the field names and without having to assign control
> > sources at run-time.
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> >
> >
> > "Allen Browne" wrote:
> >
> >> Yes: if you programmatically assign fields to controls in Report_Open,
> >> Access 2007 will crash.
> >>
> >> We have this issue listed here:
> >>     http://allenbrowne.com/Access2007.html#Bugs
> >>
> >> Hopefully MS will fix it in Service Pack 1.
> >>
> >> "backToASP via AccessMonster.com" <u27805@uwe> wrote in message
> >> news:76727dab74023@uwe...
> >> > We have 3 reports whose record sources are crosstab queries.  All have
> >> > work
> >> > fine on the pc's running Access2003.  But, the one pc running 
> >> > Access2007
> >> > doesn't like the crosstab queries as the report source.  We don't get 
> >> > an
> >> > error - only the dreaded 'Access has encountered a problem and has
> >> > closed...'
> >> > It totally shuts down the access application.  I stepped through the
> >> > little
> >> > bit of onOpen code while opening the reports and all works fine.  I 
> >> > open
> >> > the
> >> > crosstab query and it opens just fine.  To test the theory that it is a
> >> > crosstab source problem, I tried to recreate one of the reports and 
> >> > when
> >> > any
> >> > crosstab query was selected as the source there were no fields to 
> >> > choose
> >> > from.
> >> > Is this a known problem in Access2007 or some new setting I'm not aware
> >> > of?
> >> > Any suggestions appreciated. 
> 
> 
0
Utf
8/10/2007 2:22:01 PM
Thanks to both of you for the info - I assumed it was an Access 2007 issue
because the 2003 versions were running just great.  I can make a pretty easy
fix - I can name my columns because the reports are designed for current year
and previous year columns of data only.  Then I can change my two column
headings in the report to be lable fields to display the selected year and
previous year from the Report Selection Form.

Thanks again

Duane Hookom wrote:
>Allen,
>That solution is one I always recommend for date intervals since dates are 
>actually numeric. The one I had in mind is hosted over at Roger's site:
>
>http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Cross%20Tab'
>
>This solution uses much less code and is much more efficient than the 
>solution that assigns control sources at run-time.
>
>> Don't tease, Duane!  :-)
>> 
>[quoted text clipped - 37 lines]
>> >> > of?
>> >> > Any suggestions appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200708/1

0
backToASP
8/10/2007 2:58:09 PM
Reply:

Similar Artilces:

Money Break Up
I am also wondering if there is a way to take a $ value and get it broken down to the notes/coin required for cash. e.g. is I have to pay $285.75 then it might look like this $285.75 5 under the $50 column, 1 under the $20 column, 1 under the $10 column, 1 under the $5 column, 1 under the 50cent column and 1 under the ..20cent column and 1 under the .5cent column. Thanks Mel Hi Mel In cells C1:M1 enter 50, 20, 10 etc. through to .01 for the denominations you wish to pay. In A2 enter the amount to be paid. Leave column B totally blank in cell C2 enter =INT(ROUND(($A2-SUMPRODUCT(($B$...

Income & Expenses Report
I am confused by the Transfers at the end of the Income & Expenses report. It seems to double count on the bottom line. If I purchase $1000 in stock from my savings, the transfer shows a -$1000 (negative because I have taken $1000 out of my cash and transfered it to an investment). If I sell that stock for $1500. My report shows $500 capital gain in income, and a positive tranfers of $1500 (positive because I have transfered the money back into my cash). However these are added and thus shows a positive $2000 on the bottom line. It doubled the bottom line impact of the capital...

Code Comment Web Report,
I have enabled VC++ XML comments in Visual Studio 2005. However, I do not see the "Build Comment Web Pages" under the Tools file menu (which my book says should be there--but the book was written for VS02). Did they get rid of Build Comment Web Pages in VS05, or simply move it? > I have enabled VC++ XML comments in Visual Studio 2005. However, I do not > see the "Build Comment Web Pages" under the Tools file menu (which my book > says should be there--but the book was written for VS02). Did they get rid > of Build Comment Web Pages in VS05, or simply ...

Sums and percentages in reports
I have the following field in report's detail area: =Count([Cause]=1) (where 1 refers to a row of a table's dropdown) at the report footer I have the following sum for that column: =Sum(Abs([Cause]=1)) with the Runnin Sum - Over All. The calculation comes out correc so I'm assuming the formulas are correct. However, when I try to get a Percentage by deviding the field formula with the footer formula I don't get the correct answer. Can someone help me with this formula? You have told us what you attempted but it isn't clear what you want to accomplish. Your expres...

Reporting software
What kinda reporting software out there that people are using? I would like to be able to see who is sending/recieving the most mail internally and to the internet. I also would like to be able to see how much mail is being sent to my distribution lists. I am looking at a couple different products I found on msexchange.org's website but any info/advice would be appreciated. Thanks in advance. Jason On Mon, 28 Feb 2005 08:49:25 -0600, "JasonMeyer" <jason.meyer@nospam.isd623.org> wrote: > >What kinda reporting software out there that people are using? I woul...

Report Setup
Hi! I'm trying to create an Access report that looks much like a mail merge. I've got a letter that needs to go to school districts (one letter per district) that lists the schools in the district. The report will also contain mailing data - district name, administrator name, and address - while later in the letter it will list the county the district is in, the school district and the respective schools. I'm having trouble getting the sorting right so that I only get one letter per district regardless of the number of schools in the district. Any thoughts? Thanks, Betsy On ...

Linking queries on a report
Hello all, I built a number of queries that all tie to the same table. Each query sorts the data differently, but comes from the same data source. I would like to show the results of these queries on the same report, but I get and error message that the wizard can't connect the queries. How can I display multiple queries on the same report. I've tried subreports, but they don't seem to export as well. Thanks b Brennan What does "export" have to do with this? If I have multiple sets of results I want to show in one place, I create an empty "main"...

Parameterized Report
Hi all! Does anyone know how to send parameter eg. invoiceid to the custom report in MSCRM? I want to be able to display crystal report of a selected invoice by clicking a button in the CRM toolbar. Any help is greately appreciated. Thanks in advance. Rgds, Christine ...

Report Total Broker Commissions for year
Hi: I can't seem to figure out how to get a report on my investments with a broker how much I paid them in commissions for the year. I entered the commission with each buy or sell transaction from the confirmation statement from the broker so I know the data is there. -- Betty In microsoft.public.money, Betty wrote: > > >I can't seem to figure out how to get a report on my investments with a >broker how much I paid them in commissions for the year. I entered the >commission with each buy or sell transaction from the confirmation statement >from the broker s...

Customer Statement Crystal Report
Hi! Does anyone have a Customer Statement in Crystal format? Thanks -- Marisol Mortera Marisol, Your question is very generic. If you could be a little more precise, then I might have what you need. Girish "Marisol" wrote: > Hi! > > Does anyone have a Customer Statement in Crystal format? > > Thanks > -- > Marisol Mortera I am trying to build a crystal report for customer statements as well. The Great Plains one is very difficult to work with. If you have an example of this report in crystal, please email it at staceyleelee@hotmail.com Stacey &quo...

Color Break in Line Charts?
I have a cash projection spreadsheet with a linked line chart. In the projection sheet, the font goes from black to green when the data goes from actual to projected. Is there a way to make the line or the x-axis change colors at the point that the date does so (i.e., have the line turn green halfway through the chart)? Thank you in advance. On Oct 23, 8:39=A0am, Mr. Platypus <MrPlaty...@discussions.microsoft.com> wrote: > I have a cash projection spreadsheet with a linked line chart. =A0In the > projection sheet, the font goes from black to green when the data goes fr= om...

Kits in Sales Report
Is there a way for a kit to be broke out on the detailed sales report to refect the items that were sold instead of the acual kit? I hate to open a can of worms here, but you could do it by changing the query in the report (Sales - Detailed Sales Report.qrp, I believe) and then you can alter the columns to reflect the new query. -- -Jerry JR Data Inc. "annihil8or@msn.com" wrote: > Is there a way for a kit to be broke out on the detailed sales report to > refect the items that were sold instead of the acual kit? did you find a way to list kit sales details? -- Lasting...

Modified Reports Fonts
We are running Great Plains 7.5 on Terminal Server 2000 and on client PCs running Windows XP Pro. From a Terminal Server install, user A has been printing a modified report (cash requirments) for a couple years with a readable font. Recently we installed GP on her local machine. Now when she prints, the font is somewhat readable but noticably different then the terminal server GP. She is using the same printer and print drivers from both locations. User B prints the same modified report from terminal server and from a local install and prints to the same printer as user A, but has ...

Report Asking For a Parameter
So I had a report that called with its Record Source set to TableA. I then made a copy of this report, but change the Record Source to TableB. TableB is a summary of TableA, and is missing one field, NAME So when I run my report, it pops up asking for the paramater for NAME. I looked at the report and deleted the feild that was linked to NAME. The report is still asking for NAME, even though no feilds on the report ask for it now. How do I go about fixing this? On Mon, 3 Mar 2008 15:19:03 -0700, "James" <fake@email.com> wrote: NAME (which by the way is a Reserved Wor...

FRx errors with emailing reports
A customer is receiving error messages when emailing FRx reports - they don't seem to affect anything, the reports are sent and received via email and FRx doesn't close but with every report that they send, they get the same message. "Drill Down Viewer has encountered a problem and needs to close. We are sorry for the inconvenience." DrillDown Viewer closes, FRx remains open. We have checked all of the settings to enable email, everything seems to be OK and the reports do get sent, just an annoyance having the message pop up every time. Has anyone seen this before...

Calculating dates & creating reports
I need to design a database that will calculate due dates for evaluations. It will have 5 dates. 4 are calculated. These dates need to be on a form along with the employee's name and other vital info. The user should be able to enter that Start Date and have all the other dates calculate automatically. There are approximately 55 employees! The first date (Start Date) is entered by the user. Example: User enters Start Date: 2/01/07. #2 Next Date is 91 days from 2/1/07, which calculates to 5/03/07. #3 Six Month Date is 182 days from Start Date, which calculates to 8/02...

Shrinking font in Report Text Box
I have strings of varying length that I need to display in the same text box, so I would like to be able to shrink the text if it would otherwise be truncated. I've read other, similar posts but still have a question. It seems that Lebans AutoSizeFont is the right module for me to download (http://www.lebans.com/autosizefont.htm), but having done that, I'm not sure how to "apply" it to the text box in question. I've got no experience using modules. Could someone walk me through it? Thanks, Keith Keith wrote: >I have strings of varying length that I need to...

Printing only a disclaimer on the even pages of a report
I have a report that must have a disclaimer on the back of each page (no data just verbage). Can someone tell me how to set this up please. TIA for the help. Steve ...

Michigan Wage Report
Is it possible to create the Michigan Quarterly Wage for electronic filing in GP 9.0? I am going to write a app in MS Access to create it, but wanted to check if it is available elsewhere. ...

Cannot export to Excel in Crystal report (cr8)
Hi, I have an application written in vb6 and uses cr8 Crystal report. In the past, the end users were able to export the report to Excel...They have an option of Exporting to Excel 5, 7 and 8. But lately, they were not able to do so, export to Excel will just close the application down without any warning. We all currently have Office 2007 installed, and I am not sure if that is the cause of the issue. But when I export the report from my development machine I have this option to pick "Excel 97-2000" (I don’t' see the "Excel 5, 7, 8" option at all)...

Report names in listbox
I recently upgraded to Access 2007 and am having some trouble with the changes. I have a form with a listbox in which I would like to display the names of all the reports in the database. I've tried using the following as the rowsource for the listbox, but in form view nothing is displayed. Does anyone know how to do this? TIA, Ken Warthen kenwarthen@gmail.com "SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],1))<"~") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;" I think you made a slight typo. Try (((Left([Name],1))<>&quo...

zeros in the report
Reports-Sales-SalesAnalysis-AccountDue This report is showing item where the invoices are paid and the balance due is zero, making it a very long report. I have tried remove paid transactions and and reconcilling but neither works to handle this problem. Any ideas? Thanks Leslie May we assume that you selected to exclude zero balance accounts in the Sales Analysis Report Options window? "LASM" wrote: > Reports-Sales-SalesAnalysis-AccountDue > > This report is showing item where the invoices are paid and the balance due > is zero, making it a very long repor...

Breaking up the hi-lo series line?
This question is only tangentially about charting, but came up as a result of my charting project so I hope it's ok to post this here. I have a table of data that is used to create a VHLC stock chart. The series of hi-lo has a line which connects all the way across the chart. I want to insert dates in my table that will break up that line so that it shows a space on weekends/holidays (ie I only want the line to show one trade dates. My problem is that my data table only consosts of trade dates. My thought is to go through the table after it is created and check for concurrent dates, i...

Report Writer Functions #2
Where can I find instructions and examples on the various report writer functions? I've installed the SDK but I don't see anything there. The Report Writer On-line manual (Help - Printable Manuals - Reporting - Report Writer) has a fairly good description of the functions in Chapter 6. Do you look there or is there a specific function that you did not find there for which you are searching? "Elaine" wrote: > Where can I find instructions and examples on the various report writer > functions? I've installed the SDK but I don't see anything there. Hi El...

Reporting Sevices help!!!
Hi Guys, I need to know if there is a way to change the Reporting Services Reports in CRM to swith the est. Revenue field used to a seperate field. I'm doing a multi currency customisation all my user will be using different currency price lists e.g. euro price list, yen price list etc. I'm then doing a calculation to take each account opportunities etc est. revenue and put it in usd which is the reporting currency. the usd est revenue is stored in a seperate field calle new_estrevenueusd and i want this field to be used for all reports instead of the original est. revenue fiel...