Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2006
    Posts
    6

    Unanswered: Displaying calculated fields in Access report...

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

    IF hist_mis_cds!typgess = "", then calculated field= blank
    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.

  2. #2
    Join Date
    Aug 2006
    Posts
    6
    Try using

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

    'Output' is the name for the field. Do not use '='

  3. #3
    Join Date
    Sep 2006
    Posts
    6
    Thanks. I did that, but it says invalid syntax.

    Any other suggestions?

  4. #4
    Join Date
    Dec 2004
    Location
    Connecticut
    Posts
    85
    Is there a typo in your expression or is it just the way the forum parsed it?

    =IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))
    There's a space in the second spelling of HIST_MIS_CDS!TYPGESS. And , "Yes, I've made the same simple mistake, myself"

    If I'm reading it right, you check to see if the field is blank before comparing it to the other field. If it's blank you don't do anything to it, so I think you might be able to skip that part of the expression and just test it against the other field, making the expression that much simpler. I'm not saying that there isn't a valid reason to do this, but I don't see it here.

    That leaves us with:

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

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What is the name of the textbox you're using to display your calculated result? If it's the same as any of the fields referenced in your calculation, it will choke.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2006
    Posts
    6
    No, the name of the textbox is CA_MOVE. I have the query in the ControlSource of the textbox. The Form's RecordSource is a table. However, since it is an attached table, I cannot add a calculated column to it.

    Trowe, I tried the simpler query, but got the same error. It looks like it cannot find the fields I am referencing, although I selected the field anmes from the Expression Builder itself, so there is no typo.

  7. #7
    Join Date
    Sep 2006
    Posts
    6
    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.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm going to guess one of your source tables is not actually table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2006
    Posts
    6
    No, REPORT_CLIENT, HIST_MIS_CDS, HIST_CODE_TRANSLATION, etc. are simply Access tables (attached to Oracle tables of the same name).

  10. #10
    Join Date
    May 2006
    Posts
    386
    Dont mind asking, Can you tell me what exactly are you tring to use this report for? (what do you expect this to do? Because if is not producing what you are expecting, I did have similiarly the same problem sometimes ago and I think I may have a demo report of the same kind which I would send if that is what you are looking for and if I manage to find that.

  11. #11
    Join Date
    Sep 2006
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •