Error in Query in Expression Builder...

[mkarim]

I need to implement the following logic in a report column:

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!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))

When I click on Form View, I get "#Name?" in the calculated field.

Any ideas? Thanks.

 

[gemma-the-husky]

do you really have a space in the middle t ypgess, cause that will cause a problem. try putting square brackets round the field identifiers.

 

[mkarim]

No I don't have a space in the actual query. Somehow when I paste it here a space appears.

Yes, I have tried it with square brackets as well. In fact, whenever I use any field in Expression Builder (eg., [tablename]![fieldname]), I get an error. If I have a query without a fieldname (=Trim$(), etc.), it works fine.

 

[statsman]

You should be using square brackets to indicate field names [FieldName]
Try:

=IIf([HIST_MIS_CDS]![TYPGESS]=""),"",IIf([HIST_MIS_CDS]![T YPGESS]<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))

 

[mkarim]

I did, but got the same error.

 

[ejstefl]

Should there be a space in [T YPGESS]? You have no space the first time you use it, and a space the second time.

 

[mkarim]

There is no space when I have it in Expression Builder. Somehow, when I paste it here, a space appears.

I think the problem might be that the fields I am referencing ar not contained in the Record Source property of the Form.

 

[ejstefl]

That would definitley be a problem.

The other thing to watch out for is that when you let Access create a form for you, it names the controls the same thing as the fields. This can create confusion when you are trying to reference one or the other. The best bet is to re-name the controls with a prefix (ie, txt for text boxes, cbo for combo boxes, etc.)

 

[mkarim]

The Form's RecordSource property was pointing to a table (REPORT_CLIENT) that does not have all the fields I need to display. So I put this query 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;"

But running this gives the error:

"ODBC-connection to GMIS_TEST.WORLD failed"

Any suggestions? I don't know what GMIS_TEST.WORLD is.

Thanks.

 

[ejstefl]

I can't tell if those are tables or queries in your SQL statement, but if they are queries perhaps GMIS_TEST.WORLD is a table in one of those queries? I have no clue...

 

[mkarim]

No, REPORT_CLIENT, HIST_MIS_CDS, HIST_CODE_TRANSLATION, etc. are simply tables in Access (attached to Oracle tables of the same name).