Crosstab report - handling Null fields

I have a report that is based on a Crosstab query with three column headings 
.. The results of the crosstab query are based on selected rows from a multi 
list box.  If less than three selections are made OR if three selections are 
made and one column contains No Values, the report fails and does not open.  
I get the message:
"The Microsoft Jet database engine does not recognize " as a valid 
field name or expression."  I open the report in design view and see the 
error message on the field that does not exist. There are over 2500 rows in 
my List Box and no way of knowing which contain/do not contain related 
records.

How might I go about hiding the column heading/field when null values arise 
as the report opens?  Any help appreciated.

0
Utf
12/21/2009 2:15:01 AM
access.reports 4434 articles. 0 followers. Follow

4 Replies
2633 Views

Similar Articles

[PageSpeed] 22

It makes the most sense to continue posting in the same thread rather than 
creating a new one. Did you implement the solution I suggested earlier that 
creates column aliases?

-- 
Duane Hookom
Microsoft Access MVP


"NEWER USER" wrote:

> I have a report that is based on a Crosstab query with three column headings 
> . The results of the crosstab query are based on selected rows from a multi 
> list box.  If less than three selections are made OR if three selections are 
> made and one column contains No Values, the report fails and does not open.  
> I get the message:
> "The Microsoft Jet database engine does not recognize " as a valid 
> field name or expression."  I open the report in design view and see the 
> error message on the field that does not exist. There are over 2500 rows in 
> my List Box and no way of knowing which contain/do not contain related 
> records.
> 
> How might I go about hiding the column heading/field when null values arise 
> as the report opens?  Any help appreciated.
> 
0
Utf
12/21/2009 6:35:01 AM
YES I did.  I created the alias table with sub report headings which 
generated the correct column names and the correct data in fileds A,B,C  in 
Detail section.  As long as there is one row row of data in each column, the 
report runs.  If I select only one row from my list box, the report errors as 
Column B and C on the report can't be found. Select two rows with known data 
and C can't be found.

I somwhow need to re-create the report each time I run and only show Columns 
ABC when data is present from query. Any thoughts from here?

"Duane Hookom" wrote:

> It makes the most sense to continue posting in the same thread rather than 
> creating a new one. Did you implement the solution I suggested earlier that 
> creates column aliases?
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "NEWER USER" wrote:
> 
> > I have a report that is based on a Crosstab query with three column headings 
> > . The results of the crosstab query are based on selected rows from a multi 
> > list box.  If less than three selections are made OR if three selections are 
> > made and one column contains No Values, the report fails and does not open.  
> > I get the message:
> > "The Microsoft Jet database engine does not recognize " as a valid 
> > field name or expression."  I open the report in design view and see the 
> > error message on the field that does not exist. There are over 2500 rows in 
> > my List Box and no way of knowing which contain/do not contain related 
> > records.
> > 
> > How might I go about hiding the column heading/field when null values arise 
> > as the report opens?  Any help appreciated.
> > 
0
Utf
12/21/2009 1:47:01 PM
Apparently you might not have set the Column Headings property of the 
Crosstab Query. If you look at the SQL of the sample crosstab, you will 
notice:

PIVOT tblEmpCustAlias.ColumnAlias In ("A","B","C","D","E","F","G","H");

-- 
Duane Hookom
Microsoft Access MVP


"NEWER USER" wrote:

> YES I did.  I created the alias table with sub report headings which 
> generated the correct column names and the correct data in fileds A,B,C  in 
> Detail section.  As long as there is one row row of data in each column, the 
> report runs.  If I select only one row from my list box, the report errors as 
> Column B and C on the report can't be found. Select two rows with known data 
> and C can't be found.
> 
> I somwhow need to re-create the report each time I run and only show Columns 
> ABC when data is present from query. Any thoughts from here?
> 
> "Duane Hookom" wrote:
> 
> > It makes the most sense to continue posting in the same thread rather than 
> > creating a new one. Did you implement the solution I suggested earlier that 
> > creates column aliases?
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "NEWER USER" wrote:
> > 
> > > I have a report that is based on a Crosstab query with three column headings 
> > > . The results of the crosstab query are based on selected rows from a multi 
> > > list box.  If less than three selections are made OR if three selections are 
> > > made and one column contains No Values, the report fails and does not open.  
> > > I get the message:
> > > "The Microsoft Jet database engine does not recognize " as a valid 
> > > field name or expression."  I open the report in design view and see the 
> > > error message on the field that does not exist. There are over 2500 rows in 
> > > my List Box and no way of knowing which contain/do not contain related 
> > > records.
> > > 
> > > How might I go about hiding the column heading/field when null values arise 
> > > as the report opens?  Any help appreciated.
> > > 
0
Utf
12/21/2009 3:30:01 PM
I overlooked that small/HUGE piece of code.  Worked perfectly after entering 
ABC in Column Headings Properties.  Thanks again for all the help.  Happy 
Holidays!

"Duane Hookom" wrote:

> Apparently you might not have set the Column Headings property of the 
> Crosstab Query. If you look at the SQL of the sample crosstab, you will 
> notice:
> 
> PIVOT tblEmpCustAlias.ColumnAlias In ("A","B","C","D","E","F","G","H");
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "NEWER USER" wrote:
> 
> > YES I did.  I created the alias table with sub report headings which 
> > generated the correct column names and the correct data in fileds A,B,C  in 
> > Detail section.  As long as there is one row row of data in each column, the 
> > report runs.  If I select only one row from my list box, the report errors as 
> > Column B and C on the report can't be found. Select two rows with known data 
> > and C can't be found.
> > 
> > I somwhow need to re-create the report each time I run and only show Columns 
> > ABC when data is present from query. Any thoughts from here?
> > 
> > "Duane Hookom" wrote:
> > 
> > > It makes the most sense to continue posting in the same thread rather than 
> > > creating a new one. Did you implement the solution I suggested earlier that 
> > > creates column aliases?
> > > 
> > > -- 
> > > Duane Hookom
> > > Microsoft Access MVP
> > > 
> > > 
> > > "NEWER USER" wrote:
> > > 
> > > > I have a report that is based on a Crosstab query with three column headings 
> > > > . The results of the crosstab query are based on selected rows from a multi 
> > > > list box.  If less than three selections are made OR if three selections are 
> > > > made and one column contains No Values, the report fails and does not open.  
> > > > I get the message:
> > > > "The Microsoft Jet database engine does not recognize " as a valid 
> > > > field name or expression."  I open the report in design view and see the 
> > > > error message on the field that does not exist. There are over 2500 rows in 
> > > > my List Box and no way of knowing which contain/do not contain related 
> > > > records.
> > > > 
> > > > How might I go about hiding the column heading/field when null values arise 
> > > > as the report opens?  Any help appreciated.
> > > > 
0
Utf
12/21/2009 4:16:01 PM
Reply:

Similar Artilces:

Request for custom report
I apologize if this is an inappropriate forum for the request, but I would like to hire someone to write a custom RMS report for my company. Please send me an email offlist if you are available and interested. Thanks, Paul Contact me by email with detail of what report you want. Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = "montare" <montare@gmail.com> wrote in message news:1158341748.667831.253330@d34g2000cwd.googlegroups.com... >I apologize if this is an inappropriate forum for the request, but I > would like to hire someo...

Printing reports as a PDF
How do enable printing reports as a pdf. Adobe is installed on the workstation. We are using GP 8.0 with SP1 what kind of report - financial, FRx, ... ? "Davidjc52" wrote: > How do enable printing reports as a pdf. Adobe is installed on the > workstation. We are using GP 8.0 with SP1 It is a financial report "anon" wrote: > what kind of report - financial, FRx, ... ? > > "Davidjc52" wrote: > > > How do enable printing reports as a pdf. Adobe is installed on the > > workstation. We are using GP 8.0 with SP1 You could just s...

Maximising report previews
I have a form running a VBA Do loop that assembles reports. As each report is created, it is necessary to preview it, to determine whether or not it is to be printed. This means pausing the Do loop in order to take the necessary action. One way of doing this is to open the report in dialogue mode and a second is to open it from an intermediate form which is itself opened in dialogue mode. The latter is the more practical because the intermediate form can carry a pair of command buttons marked ‘Accept’ and ‘Skip’, for example, but both methods suffer from the same drawback, with diff...

How do I select records were a field contains a key word??
I have a field named [Comments], a text field. I want to create a query that selects records that contain the word "Not" somewhere (anywhere) in the field. Is this possible? OldEd OldManEd wrote: > I have a field named [Comments], a text field. I want to create a > query that selects records that contain the word "Not" somewhere > (anywhere) in the field. Is this possible? > > OldEd .... WHERE Comments Like "* not *" -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Thanks, that worked....

How to set multiple filter values for a filter in the page fields
Hello all I have a filter on a time dimension I want to use time on the page fields and still set the filter on different quarters. When time is in a row or a column of the pivot table I can restrict and set multiple filter values but when I move this time dimension to the page field I loose the ability to set multiple filter values can of you please suggest work arounds on how to accomplish setting multiple filter values in the page fields of a pivot table. Your help is highly appreciated. Thanks Simon There are some instructions here: http://www.contextures.com/xlPivot03.html#Page ...

New fields won't save data ??
Hi I have an upgraded installation to 4.0 where new fields created on accounts won't save data. When I change data in any new field and hit save it blanks out the field. When I type somehting in the old fields on the form these fields save fine. What could this be ? Jack It sounds like a javascript issue with the form. I would check to see if any javascript is running against the form (Onload, Onchange, etc.) Regards "CRM consultant" wrote: > Hi > > I have an upgraded installation to 4.0 where new fields created on accounts > won't save data. When I...

? Handling WM_SIZE/WM_SIZING But Not From Restore
Hi, I need to handle WM_SIZE and WM_SIZING but only for when the size of the window is actually changing, not for when it is being restored from a minimized state (yes, I know it's technically changing in that case.) I've got a resizing dialog app which has a list control in in. I've set up the dialog to move and resize the list control as well as set the column widths whenever the dialog's size is altered (in OnSize). However, this is a waste of time when the dialog is just being restored from a minimized state, specifically if the list control has lots of items in it (you...

Report Writer VBA question
I have created 4 calculated fields in Report Writer. The report is a standard employee paycheck stub on top bottom report all of the fields are currency fields dlr_r2 formatting Field 1 - Is extracted in VBA directly from a SQL Table has a format of numeric Field 2 - Is extracted from the report writer Field 3 - Is extracted from the report writer Field 4 - This field is a formula which is Field 1 + Field 2 - Field 3 The problem that I am having is the the field 1 is properly extracted from the SQL table. however, it is not picked up by the field 4 formula. I am using VBA code direc...

create flixible forecast reports?
Is it possible to create flexible forecast reports (global reports, reports for one country, for one business-line, one account-executive, ...) with revenue and profit margin, given the following information for the opportunities: - global revenue and profit margin (percentage), equal split over several months - global revenue and profit margin (percentage), not equal split over several months - hourly revenue and cost price for a consultant (linked at an opportunity), split over several months based on working days per month - hourly revenue and cost price for different consultants (lin...

How to change the AutoNumbers inID field in my finished database?
This may be a simple question but... I have already entered in my data and want to change the autonumbers, to a different way I have set it up. Is this possible? On Mon, 4 Feb 2008 19:48:01 -0800, Jako <Jako@discussions.microsoft.com> wrote: Define "different". -Tom. >This may be a simple question but... >I have already entered in my data and want to change the autonumbers, to a >different way I have set it up. Is this possible? Jako <Jako@discussions.microsoft.com> wrote: >I have already entered in my data and want to change the autonumbers, to a ...

Advance Finanical Reports : Statement of Cash Flow
Hi, To creat a Statement of Cash Flow in Advance Financial Reports i need an existing sample report of Statement of Cash Flow , so from where can i get this sample report. Any help would be greatly appreciated! Thanks! -- Developer ...

Ahmed Nashat
Nashat, you posted the following about historical inventory back in May 2007. Can you post it here again? "Either run the last "select" statement alone, or use the attached RMS report, i had to make it in RMS report coz i felt that people r not happy using the query i prepared thank you! ...

Dynamic Controls on a Report
Hello all, Here is the code I have.. I am looking for a way from a dynamics table to have dynamics columns Private Sub Report_Open(Cancel As Integer) Dim Rst As Recordset, f As Field, i As Integer, x As Control Set Rst = CurrentDb.OpenRecordset("tblStand") For Each f In Rst.Fields If f.Name <> "DBH" And f.Name <> "Flag" Then i = 1 Do While i < 16 Me.Controls("txtSpc" & (i)).Value = f.Name Loop Rst.MoveNext End If Next Rst.Close 'txtSpc(i...

Outlook custom field at contacts...
Hi We have an Exchange 5.5 server with public contact. We designed a custom form with additional fields like “Salesman” etc. It is works OK. When we try to use these contacts in a mail merge, these custom fields are not showing up on the list. What we missing here? We are using Office XP (Office 2002). Thank you in advanced for any help Victo Maybe this is it? Action When you design a form and are planning on creating user-defined fields to mail merge within Outlook, the user-defined fields are not available in the Word mail-merge document. The only fields that are available are the ...

Free sales detail report fix -- proper cost and profit
If anybody would like a revised version of the sales detail report that corrects the bad calculations on price and cost, please contact me. It was a simple fix, so this report is free of charge. If it saves you any frustration at all, it's worth it! The original out of the box report does not take into account quantities when calculating gross profit -- a pretty important detail. The gross profit percentage was accurate however. -- Alex Nielsen ...

Printing only the current record on a report
I have created a report and a command button to preview and then print the current record of a report. Originally it previewed the report, however it keeps showing the wrong record and not the current record. Here is my code I then tried and its still not working: DoCmd.OpenReport "Individual Package Peer Review Summary", acViewPreview "[stats Package Number] like '" & Me![stats Package Number] & "'" The stats package number is the unique identifier for the records. Any help is appreciated. Hi Belinda, Try replacing the LIKE keyword with ...

Crystal Reports Logon Error 12-01-04
I get the following message when I try to bring up a list of available reports. "Please verify that you have enough Crystal licenses." I can do this from the web interface on the server but not from any PC connecting via the web interface. Is there someplace I can check to see if there are licenses installed? I didn't install any that I'm aware of..I guess I assumed what I needed came with CRM. Thanks, Gary This is about the most generic message you can get. When troubleshooting CRM and Crystal check out http://www.microsoft.com/BusinessSolutions/Community/CRMC...

need to make 3 fields into 1 field
I have an mail list that has been created with 3 address fields. These fields need to be consolidated into 1 field. Can this be done. Thanks try =a1 & " " & b1 etc -- Don Guillett SalesAid Software donaldb@281.com "Georgia" <Georgia@discussions.microsoft.com> wrote in message news:967B03F1-717D-47BB-80D6-3A289DCAC1A8@microsoft.com... > I have an mail list that has been created with 3 address fields. These > fields need to be consolidated into 1 field. Can this be done. > > Thanks > Georgia Most programs that use Adresses like them ...

CRM Role/Privileges Report
Is there a report, or, even better, a bit of SQL code, that will generate a report or listing of each business unit, its roles, and their level of privileges for each function? Thanks! Anyone? On Apr 25, 2:33 pm, LeftyRodiguez <rifle...@gmail.com> wrote: > Is there a report, or, even better, a bit of SQL code, that will > generate a report or listing of each business unit, its roles, and > their level of privileges for each function? > > Thanks! On May 2, 8:35=A0am, LeftyRodiguez <rifle...@gmail.com> wrote: > Anyone? > On Apr 25, 2:33 pm, LeftyRodiguez <...

Crosstab
Hi, I have a query that looks like this: TRANSFORM Count(mytable.var2) AS cnt SELECT mytable.var1, count(mytable.var2) AS TOTAL FROM mytable GROUP BY mytable.var1 PIVOT mytable.var2; and my result looks like this: var1 female male 1 30 10 2 40 20 3 50 30 Is there a way I can name the columns to "column1", "column2" and so forth, instead of female and male? Assume there are any number of columns. Thanks in advance, Jenni You can alias your column headings but it may take a few steps/queries. The first step might be to cr...

Conditional formula for a date field
I've placed an unbound textbox on a form that I want to display an expected expiration date for a security clearance based on the type of clearance that it is and when the original clearance was granted. So basically I want it to do the following: If it's a secret clearance, then I want it to take the date the clearance was granted and calculate the expiration date 10 years into the future. If it's a Top Secret clearance then calculate the expiration date 5 years into the future. I tried the following code and it didn't seem to work. if me.clearancetype = 1 then m...

Remove Part from Field Service Call
I am trying to remove a part from a service call which was linked in by a purchase order. When I try to delete the row the sytem says "There is an RMA, a PO or a Transfer for this line. You may not delete this line." How do I remove this link when the PO has already been received? Unfortunately, the only way we are able to work around this is by updating the PONMBRSTR to '' (2 apostrophes to indicate blank) for the item in the SVC00203 table. to find the specific line needed, use something to the effect of: select callnbr, itemnmbr, lnitmseq, dex_row_id from svc00203 ...

get data from SSRS report
hi guys i want to know how to get output of SSRS(another project in the same solution) to win form using c# language (in the same solution) with visual studio 2008 so any articles or answers can help me thanks Submitted via EggHeadCafe - Software Developer Portal of Choice Build a Windows XP "Site Changer" IIS Utility http://www.eggheadcafe.com/tutorials/aspnet/86d5b18d-871d-4d68-929c-1f91b38ffb58/build-a-windows-xp-site.aspx SSRS in most cases is a server based solution. If the issue is that you want to be able to embed a report into your C# win form you have severa...

VBA?Macro Newbie Question//Last field in a column with a value.
I am a newbie to programming Excel. I Have a workbook which has a average about 40 worksheets. I keep individual attendance and payment for my Alcohol and Drug Treatment program on these worksheets. Th individual worksheets are linked to a master roster worksheet in different workbook. My question for today is how can I link the last payment in a column i an individual worksheet to the master roster so I can tell at a glanc when a person made their last payment? Also, how could I link th appropriate cells to the master without doing it manually every time enter a new client? Thanks ...

system date returns null
Testing for the year returns a null using the system date. I am using Access2007. This worked in Access2003. My code is: 190 If Right(Year(Date), 2) = txtEYear Then txtEYear = "" When I run the code and check "date" with debug, it shows 'null'. On Sun, 14 Feb 2010 14:21:02 -0800, Dave <Dave@discussions.microsoft.com> wrote: That's unusual. Does your code compile? Check a code window > Debug > Compile. -Tom. Microsoft Access MVP >Testing for the year returns a null using the system date. I am using &...