Query/Report Error
in [General]
|
From: Marsha on 17 Jul 2008 17:56 I am trying create a simple report off of a make-table query that I have. The query itself works fine and creates the table. This is the SQL for the Depression Query: SELECT DISTINCT Table1.UniqueID, [Table 10].SatisfiedWithLife, [Table 10].DroppedActivitiesAndInterest, [Table 10].LifeIsEmpty, [Table 10].OftenBored, [Table 10].InGoodSpirits, [Table 10].AfraidSomethingBadWillHappen, [Table 10].HappyMostOfTheTime, [Table 10].FeelHelpless, [Table 10].PreferToStayAtHome, [Table 10].MoreMemoryProblems, [Table 10].WonderfulToBeAlive, [Table 10].FeelingWorthless, [Table 10].FullOfEnergy, [Table 10].Hopeless, [Table 10].MostPeopleAreBetterOffThanMe, [Table 10].Total, Table2.Depressed, Table8.DepressionManagement FROM ((Table1 INNER JOIN [Table 10] ON Table1.UniqueID=[Table 10].UniqueID) INNER JOIN Table2 ON Table1.UniqueID=Table2.UniqueID) INNER JOIN Table8 ON Table1.UniqueID=Table8.UniqueID; When I go to design a simple report using the report wizard, which includes all the fields from this make table in the report I get the following error: "Compile error. In query expression 'IIf(IsNumeric(tblStyles.StyleName),AppLoadString(tblSyleName),tbleStyles.Styles.SyleName)'. I get this twice. Then I get the error: "Invalid use of Null." This one is a bit over my head. Any ideas from anyone?? Any help is greatly appreciated. Thanks!!
From: John W. Vinson/MVP on 17 Jul 2008 18:25 On Thu, 17 Jul 2008 14:56:05 -0700, Marsha <Marsha(a)discussions.microsoft.com> wrote: >I am trying create a simple report off of a make-table query that I have. You can't. A Report can be based on a Table or on a Select Query (the latter is much more common); it's very rarely necessary to use a MakeTable query at all. >The >query itself works fine and creates the table. This is the SQL for the >Depression Query: > >SELECT DISTINCT Table1.UniqueID, [Table 10].SatisfiedWithLife, [Table >10].DroppedActivitiesAndInterest, [Table 10].LifeIsEmpty, [Table >10].OftenBored, [Table 10].InGoodSpirits, [Table >10].AfraidSomethingBadWillHappen, [Table 10].HappyMostOfTheTime, [Table >10].FeelHelpless, [Table 10].PreferToStayAtHome, [Table >10].MoreMemoryProblems, [Table 10].WonderfulToBeAlive, [Table >10].FeelingWorthless, [Table 10].FullOfEnergy, [Table 10].Hopeless, [Table >10].MostPeopleAreBetterOffThanMe, [Table 10].Total, Table2.Depressed, >Table8.DepressionManagement >FROM ((Table1 INNER JOIN [Table 10] ON Table1.UniqueID=[Table 10].UniqueID) >INNER JOIN Table2 ON Table1.UniqueID=Table2.UniqueID) INNER JOIN Table8 ON >Table1.UniqueID=Table8.UniqueID; This is not a MakeTable query, which would have an INTO clause (the new table name). But that's ok, it doesn't need to be. >When I go to design a simple report using the report wizard, which includes >all the fields from this make table in the report I get the following error: > >"Compile error. In query expression >'IIf(IsNumeric(tblStyles.StyleName),AppLoadString(tblSyleName),tbleStyles.Styles.SyleName)'. >I get this twice. Then I get the error: "Invalid use of Null." I would guess that either StyleName is NULL and is therefore causing an error, or that the function appLoadString is expecting some value other than a tablename. Where is AppLoadString defined, and what parameters does it expect? Also, the syntax "tbleStyles.Styles.SyleName" is almost surely wrong. If the table is named tblStyles (without the "e") you need to fix that; and what comes after the period should be just a fieldname, not "Styles.Sylename". I also note that you're joining the (badly denormalized, incorrectly structured) Table10 to Table1, and thence to Table2 and Table8; but you're not using any fields from Table1 other than the UniqueID. Is this intentional? You could just join Table1 directly to Table2 and Table8 provided those tables contain data for that UniqueID. >This one is a bit over my head. Any ideas from anyone?? Any help is greatly >appreciated. Thanks!! -- John W. Vinson/MVP
From: Marsha on 17 Jul 2008 19:24 Okay, before launching into a much more complicated explanation of my registry than necessary. I'll ask one question and see if this works. You're right that I grabbed the wrong sql statement I have two different depression queries designed. One is a make table one is not. Given the first part of your explanation. Can you design a Report from a make-table at all? If not, I'll try the easy solution first, and then come backif that doesn't work to try to sort out all this sql mess. "John W. Vinson/MVP" wrote: > On Thu, 17 Jul 2008 14:56:05 -0700, Marsha > <Marsha(a)discussions.microsoft.com> wrote: > > >I am trying create a simple report off of a make-table query that I have. > > You can't. A Report can be based on a Table or on a Select Query (the > latter is much more common); it's very rarely necessary to use a > MakeTable query at all. > > >The > >query itself works fine and creates the table. This is the SQL for the > >Depression Query: > > > >SELECT DISTINCT Table1.UniqueID, [Table 10].SatisfiedWithLife, [Table > >10].DroppedActivitiesAndInterest, [Table 10].LifeIsEmpty, [Table > >10].OftenBored, [Table 10].InGoodSpirits, [Table > >10].AfraidSomethingBadWillHappen, [Table 10].HappyMostOfTheTime, [Table > >10].FeelHelpless, [Table 10].PreferToStayAtHome, [Table > >10].MoreMemoryProblems, [Table 10].WonderfulToBeAlive, [Table > >10].FeelingWorthless, [Table 10].FullOfEnergy, [Table 10].Hopeless, [Table > >10].MostPeopleAreBetterOffThanMe, [Table 10].Total, Table2.Depressed, > >Table8.DepressionManagement > >FROM ((Table1 INNER JOIN [Table 10] ON Table1.UniqueID=[Table 10].UniqueID) > >INNER JOIN Table2 ON Table1.UniqueID=Table2.UniqueID) INNER JOIN Table8 ON > >Table1.UniqueID=Table8.UniqueID; > > This is not a MakeTable query, which would have an INTO clause (the > new table name). But that's ok, it doesn't need to be. > > >When I go to design a simple report using the report wizard, which includes > >all the fields from this make table in the report I get the following error: > > > >"Compile error. In query expression > >'IIf(IsNumeric(tblStyles.StyleName),AppLoadString(tblSyleName),tbleStyles.Styles.SyleName)'. > >I get this twice. Then I get the error: "Invalid use of Null." > > I would guess that either StyleName is NULL and is therefore causing > an error, or that the function appLoadString is expecting some value > other than a tablename. Where is AppLoadString defined, and what > parameters does it expect? > > Also, the syntax "tbleStyles.Styles.SyleName" is almost surely wrong. > If the table is named tblStyles (without the "e") you need to fix > that; and what comes after the period should be just a fieldname, not > "Styles.Sylename". > > I also note that you're joining the (badly denormalized, incorrectly > structured) Table10 to Table1, and thence to Table2 and Table8; but > you're not using any fields from Table1 other than the UniqueID. Is > this intentional? You could just join Table1 directly to Table2 and > Table8 provided those tables contain data for that UniqueID. > > >This one is a bit over my head. Any ideas from anyone?? Any help is greatly > >appreciated. Thanks!! > > -- > > John W. Vinson/MVP >
From: John W. Vinson/MVP on 17 Jul 2008 20:19 On Thu, 17 Jul 2008 16:24:01 -0700, Marsha <Marsha(a)discussions.microsoft.com> wrote: >Okay, before launching into a much more complicated explanation of my >registry than necessary. I'll ask one question and see if this works. You're >right that I grabbed the wrong sql statement I have two different depression >queries designed. One is a make table one is not. Given the first part of >your explanation. Can you design a Report from a make-table at all? No. A Select query returns records, creates a recordset, and can be used as the Recordsource of a form or a report. A MakeTable query performs an action; it does not return records or create a recordset. What you can do (though it's not usually either necessary nor a good idea) is to do two steps - run a MakeTable query to create a new (redundant, bloating, un-indexed, unrelated) table, and then base a Report on that table. Generally it would be just as easy to change the MakeTable query back into a Select query, and base your report on THAT. The select query returns the same records, the same fields, the same data; creating a new table simply adds another expensive step! > If not, >I'll try the easy solution first, and then come backif that doesn't work to >try to sort out all this sql mess. Since I'm not sure what "the easy solution" might be I am not sure I can help... -- John W. Vinson/MVP
From: Marsha on 18 Jul 2008 14:34 Well, I thought the "easy solution" might be just endeavoring to make the report from a select query rather than a make table query, since that seemed to be what might be causing the problem, since I wasn't sure how I might change the error message itself. However, I just created a brand new select query just using Table 10 and Table 8 and that did not work either. I got the exact same error message. Anyway, I thought I would try this first before launching into everything else... I did just take a look at the design view of table 10 and realize that I had failed to identify the Unique ID of Table 10 as my primary key when it is identified in all of my other tables, which may be contributing to the problem. However, now when I try to do this, it will not allow me too because of the index? Though my db may seem poorly designed and frustrating to communicate about there is a reason its somewhat messy. It's designed based off of a patient survey. Each table corresponds to a survey form page and is directly exported from a form reader into the db to correspond to the exact questions on that survey page. These are all temporary tables that are then uploaded to a national registry. I use a series of make-table and append queries to extract data from the temporary tables in order to build a local "registry" from these temporary tables, in order to establish a permanent registry. I'm always open to suggestions as to how this process may be improved upon. But yes, it's a bit convoluted as I have to toss out identified pt data and then deidentify in order to store data and keep everything in line with specific survey pages.... Back to the question at hand....so I keep getting the error off either type of query select or make table. Regardless of how I structure the query. Do you think this is due to having forgotten to put the primary key marker on the unique ID when I built this table? Or do I need to go back to look at the original SQL code. If it's the Unique ID that's most likely interferring...then how do I get it to allow me to set it as my unique ID when it's throwing up an error about how it can't do that due to all the indexing? THANKS!!! All your help IS appreicated :) :) "John W. Vinson/MVP" wrote: > On Thu, 17 Jul 2008 16:24:01 -0700, Marsha > <Marsha(a)discussions.microsoft.com> wrote: > > >Okay, before launching into a much more complicated explanation of my > >registry than necessary. I'll ask one question and see if this works. You're > >right that I grabbed the wrong sql statement I have two different depression > >queries designed. One is a make table one is not. Given the first part of > >your explanation. Can you design a Report from a make-table at all? > > No. A Select query returns records, creates a recordset, and can be > used as the Recordsource of a form or a report. A MakeTable query > performs an action; it does not return records or create a recordset. > What you can do (though it's not usually either necessary nor a good > idea) is to do two steps - run a MakeTable query to create a new > (redundant, bloating, un-indexed, unrelated) table, and then base a > Report on that table. > > Generally it would be just as easy to change the MakeTable query back > into a Select query, and base your report on THAT. The select query > returns the same records, the same fields, the same data; creating a > new table simply adds another expensive step! > > > If not, > >I'll try the easy solution first, and then come backif that doesn't work to > >try to sort out all this sql mess. > > Since I'm not sure what "the easy solution" might be I am not sure I > can help... > > -- > > John W. Vinson/MVP >
|
Pages: 1 Prev: Access 2000 ver 9.0.6926 versus 9.0.8948 Next: Ihr Schreiben vom 12.Juli 2008 |