Is Expression Invalid?

[MBP]

I previously posted an issue with my form not
displaying...I narrowed the problem down to the query and
the type of expression I am trying to use. I was able to
build forms off of the both tables I am using; but, as
soon as I added the expression it failed.

The expression is [table1]![total]/[table2]![target total].

I'm not sure if the following is a contributing factor but
here goes:

The Date on table 1 looks up all of the data from table 2,
so that in the form when I select a date, the target
totals automatically populate.

All of my other forms work- BUT - the expressions are all
If, then and not an actual calculation. I'm taking all
suggestions; multiple options will allow me to find what
works best...Help!
..

 

[Michel Walsh]

Hi,


What is the text of your query (from the SQL view) ?


Vanderghast, Access MVP

 

[Marquis Payne]

First, thanks for replying

Here is the SQL view of the query:

SELECT Quality.*, Quality.QualityKey, Quality.[Sup Name],
Quality.[Agent Name], Quality.[Pay Period], Quality.Nice,
Quality.[Self-Eval], Quality.[External Audit],
Quality.Compliance, [Target Quality].[Nice Target],
[Target Quality].[Self-Eval Target], [Target Quality].
[External Target], [Target Quality].[Compliance Target],
[Quality]![Nice]/[Target Quality]![Nice Target] AS [Nice
Score], [Quality]![Self-Eval]/[Target Quality]![Self-Eval
Target] AS [Self-Eval Score], [Quality]![External Audit]/
[Target Quality]![External Target] AS [External Score],
[Quality]![Compliance]/[Target Quality]![Compliance
Target] AS [Compliance Score], ([Nice Score]*0.67)+([Self-
Eval Score]*0.33)+([External Score])+([Compliance Score])
AS [Overall Quality], *
FROM [Target Quality] INNER JOIN Quality ON ([Target
Quality].[Pay Period] = Quality.[Sup Name]) AND ([Target
Quality].[Pay Period] = Quality.[Pay Period]);

-----Original Message-----
Hi,


What is the text of your query (from the SQL view) ?


Vanderghast, Access MVP

I previously posted an issue with my form not
displaying...I narrowed the problem down to the query and
the type of expression I am trying to use. I was able to
build forms off of the both tables I am using; but, as
soon as I added the expression it failed.

The expression is [table1]![total]/[table2]![target total].

I'm not sure if the following is a contributing factor but
here goes:

The Date on table 1 looks up all of the data from table 2,
so that in the form when I select a date, the target
totals automatically populate.

All of my other forms work- BUT - the expressions are all
If, then and not an actual calculation. I'm taking all
suggestions; multiple options will allow me to find what
works best...Help!
.

.

 

[Michel Walsh]

Hi,


I do not really see a problem, just non standard practice. Generally,
use a dot, not a bang, between the table name and the field name. Also, if
that does not solve the problem, try adding a ( ) around the ON clause:

ON (
( [Target Quality].[Pay Period] = Quality.[Sup Name])
AND
( [Target Quality].[Pay Period] = Quality.[Pay Period])
) ;



Note that this statement implies that Quality.[Sup Name] =
Quality.[Pay Period], which look strange, to me.



Vanderghast, Access MVP

First, thanks for replying

Here is the SQL view of the query:

SELECT Quality.*, Quality.QualityKey, Quality.[Sup Name],
Quality.[Agent Name], Quality.[Pay Period], Quality.Nice,
Quality.[Self-Eval], Quality.[External Audit],
Quality.Compliance, [Target Quality].[Nice Target],
[Target Quality].[Self-Eval Target], [Target Quality].
[External Target], [Target Quality].[Compliance Target],
[Quality]![Nice]/[Target Quality]![Nice Target] AS [Nice
Score], [Quality]![Self-Eval]/[Target Quality]![Self-Eval
Target] AS [Self-Eval Score], [Quality]![External Audit]/
[Target Quality]![External Target] AS [External Score],
[Quality]![Compliance]/[Target Quality]![Compliance
Target] AS [Compliance Score], ([Nice Score]*0.67)+([Self-
Eval Score]*0.33)+([External Score])+([Compliance Score])
AS [Overall Quality], *
FROM [Target Quality] INNER JOIN Quality ON ([Target
Quality].[Pay Period] = Quality.[Sup Name]) AND ([Target
Quality].[Pay Period] = Quality.[Pay Period]);

-----Original Message-----
Hi,


What is the text of your query (from the SQL view) ?


Vanderghast, Access MVP

I previously posted an issue with my form not
displaying...I narrowed the problem down to the query and
the type of expression I am trying to use. I was able to
build forms off of the both tables I am using; but, as
soon as I added the expression it failed.

The expression is [table1]![total]/[table2]![target total].

I'm not sure if the following is a contributing factor but
here goes:

The Date on table 1 looks up all of the data from table 2,
so that in the form when I select a date, the target
totals automatically populate.

All of my other forms work- BUT - the expressions are all
If, then and not an actual calculation. I'm taking all
suggestions; multiple options will allow me to find what
works best...Help!
.

.