Query/Report Error

[Marsha]

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!!

 

[John W. Vinson/MVP]

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.

 

[Marsha]

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.

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]

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...

 

[Marsha]

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