This is the query I have in the RecordSource property of the Form:
"SELECT *
FROM (REPORT_CLIENT INNER JOIN HIST_MIS_CDS ON REPORT_CLIENT.ID =
HIST_MIS_CDS.ID) INNER JOIN HIST_CODE_TRANSLATION ON REPORT_CLIENT.ID =
HIST_CODE_TRANSLATION.ID;"
Running this gives the error:
"ODBC-connection to GMIS_TEST.WORLD failed"
Any suggestions? I don't know what GMIS_TEST.WORLD is.
:
Thanks. I did create a query that joins the 3 tables on the ID field,
but
when I run it, I get the error:
"ODBC-connection to GMIS_TEST.WORLD failed"
I don't get this error when the RecordSource property only points to
that
single REPORT_CLIENT table. I get this error when I join the
HIST_MIS_CDS
and HIST_CODE_TRANSLATION tables. All tables are in the same database,
so
I
don't know why the error should occur.
Thanks again!
:
You will need to join the tables. (SELECT * FROM SomeTable INNER
JOIN
SomeOtherTable ON SomeTable.SomeField = SomeOtherTable.SomeField).
As I
do
not know how the tables are related, I can not say what the join
should
be.
--
Brendan Reynolds
Access MVP
Great. Thanks!
How do I create a query form's record source that will include
these
fields?
Would it be a regular Jet-SQL query, like "SELECT * from
REPORT_CLIENT,
field1 from HIST_MIS_CDS, field2 from HIST_CODE_TRANSLATION"?
:
Your expression is attempting to refer to fields that are not in
the
form's
record source. In order to refer to fields in the tables
'HIST_MIS_CDS'
and
'HIST_CODE_TRANSLATION' your form's record source would need to
be a
query
that included those tables. Alternatively, you might be able to
use
the
DLookup function.
The only fields that you can refer to directly in expressions
(without
using
domain aggregate functions such as DLookup or custom code) are
fields
that
are included in the record source of the form.
--
Brendan Reynolds
Access MVP
The Record Source property of the form references an Access
table
(called
REPORT_CLIENT). This table is attached to an Oracle table of
the
same
name.
The Control Source values of the other textboxes in the report
are
simply
fields in the REPORT_CLIENT table.
Your help is greatly apreciated. Thanks.
:
What is the value of the RecordSource property of the form?
--
Brendan Reynolds
Access MVP
I did that (adding square brackets around table and field
names
and
leaving
out the table names) but get the same error.
If I give a function without a fieldname (eg., Trim$(),
etc.)
it
works
fine.
It is referencing fields that causes the problem (even
=tablename!fieldname
gives the same error).
Any other suggestions? Do I need to add the calculated field
to
the
table
the recordsource is referencing? I cannot do that because it
is
an
attached
table and, hence, cannot be changed.
Thanks.
:
Try adding square brackets around the table names and field
names.
In
the
example below I've put spaces either side of the square
brackets
because
they were difficult to see otherwise, but this is just for
illustration,
don't add those extra spaces in the real expression ...
=IIf( [ HIST_MIS_CDS ] ! [ TYPGESS ] ="","",IIf( [
HIST_MIS_CDS ]
!
[
TYPGESS ] <> [ HIST_CODE_TRANSLATION ] ! [
SRC_CODE_DESC ]
,"X","" ))
Alternatively, if your form's record source does not
include
fields
with
the
same name from different tables, you may be able to leave
out
the
table
names altogether and just use the field names ...
=IIf( [TYPGESS] ="","",IIf( [TYPGESS] <> [SRC_CODE_DESC]
,"X","" ))
--
Brendan Reynolds
Access MVP
I need to implement the following logic in a report column
in
Access
97:
IF hist_mis_cds!typgess = "", then calculated field= ""
ELSE
IF hist_mis_cds!typgess <> hist_code_translation!
src_code_desc
then calculated field= "X"
ELSE calculated field= ""
I have implemented the following query in Expression
Builder
for
the
calculated field:
=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!TYPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X",""
))
When I click on Form View, I get "#Name?" in the
calculated
field.
Any ideas? Thanks.
