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.

 

[CraigDolphin]

maybe the space in
HIST_MIS_CDS!T YPGESS
should be removed...
HIST_MIS_CDS!TYPGESS

 

[mkarim]

No, there is no space in the actual query:

=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!TYPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X",""))

 

[boblarson]

Try checking to see if your report control names are the same as your field names. If they are, then change the control names to something else (for example if the field name is "Location" and the control is named "Location" (which Microsoft does by default when using the Form and Reports Wizards) change the report control to something like txtLocation.

Also, if used in a query:

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

add brackets to change to:

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

 

[CraigDolphin]

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

Sure looks like one to me

 

[mkarim]

I am getting the same error in the following query in the Expression Builder:

=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!TYPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X",""))

The queries don't have a space in Expression Builder. Somehow, when I paste it here, a space seems to appear.

I did change the name of the control to a different name than the field and added square brackets, but get the same error:

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

When I have a query without a reference to a fieldname, it works fine. Once I reference any fieldname (reference even one field), I get that error. Something with how I am referencing fields in the query - I go to the table in Expression Builder and select a fieldname, so there is no typo or space.

 

[boblarson]

When you use an IIF in the query, you need to enclose the field names (and table if you reference a table) in square brackets, as shown in my previous post.

 

[neileg]

I'm not sure about the bang operator (!) in these formulae. Plus there seems to be some brakets missing. I would have expected the syntax to look like this:

=IIf([HIST_MIS_CDS].[TYPGESS]="","",IIf([HIST_MIS_CDS].[TYPGESS]<>[HIST_CODE_TRANSLATION].[SRC_CODE_DESC],"X","" ))

 

[CraigDolphin]

I created a dummy db (A2K) with two tables named:
HIST_MIS_CDS:
-autokey (PK)
-TYPGESS: text
-foreignkeyfield (FK)

HIST_CODE_TRANSLATION
-keyfield (PK)
-SRC_CODE_DESC: text

The two tables are related via the PK of HIST_CODE_TRANSLATION and the FK of HIST_MIS_CDS

Using the report wizard, I created a report with SRC_CODE_DESC and TYPGESS in the detail section. The source of my report is:

SELECT [HIST_CODE_TRANSLATION].[SRC_CODE_DESC], [HIST_MIS_CDS].[typgess] FROM HIST_CODE_TRANSLATION INNER JOIN HIST_MIS_CDS ON [HIST_CODE_TRANSLATION].[keyfield]=[HIST_MIS_CDS].[foreignkeyfield];

I added an unbound textbox control to the detail section of the report and set the control source to:

=IIf(IsNull([TYPGESS]),"",IIf([TYPGESS]<>[SRC_CODE_DESC],"X",""))

this produces an empty string if TYPGESS is null or if TYPGESS = SRC_CODE_DESC, and an X if the two fields are not equal.

Have attached the example db.

So, if your db doesn't accept the statement then perhaps the problem is not in the iif statement itself but some other extrinsic factor (report source not including one of your fields maybe?)

Hope it helps.

 

[mkarim]

The Form's RecordSource property was pointing to a table (REPORT_CLIENT) that did 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.

 

[CraigDolphin]

No idea, sorry. Can you make a separate query that provides the fields you need, and then bind the form to that query?

 

[mkarim]

Thanks, but I figured it out. The REPORT_CLIENT is attached to an Oracle table of the same name and I am now using a PL/SQL UPDATE statement to write results to the Oracle table and simply open the Access table.

Thanks for your response.

 

[CraigDolphin]

Glad you resolved the problem