Results 1 to 7 of 7

Thread: syntax (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    syntax (2000)

    Hello, I am not sure how to code an expression in a query. I wish for a value to be returned in the expression field where that value is derived from certain fields on a form, that part I am ok with. However the final part of the calculation takes a value from a field in a table and that value is dependent upon two factors, one factor is the return value of a function, this return value equates to a PK ID in the table, the row for that record once the ID is determined then has two entries, say field X and field Y. The value used in the calculation will be the value in X or Y. The row is determined by the function and wether X or Y is from a combo selection on the form. Hope this makes sense.

    Thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: syntax (2000)

    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    Sorry, the application is attached. Yyou will see in query2, Expr1: [prem]*[comm]*[ehhance]*[share]*[Factor 075]. The [factor 075] part I need amending so that it reads a value from tblclawbackfactors, the value would be a value in a selected row (I have this selection working fine, it is equivalent to a part month expression in query 1) but the value would either be a figure from factor 075 or factor 100 which are fields in the table. Wether the formula I require would read the value in field factor 075 or 100 is determined by a drop combo that will be in a form. So lets say my part month returns a value of 3 and the user selects factor 075 from the combo, then using the actual figures I have in the tblclawbackfactors the value in the math would be 2.9777.

    How can i code and expression to get desired result?

    Thanks
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: syntax (2000)

    Instead of [Factor 075], use

    IIf([Forms]![Form1]![cmbFactor]="0.75",[Factor 075],[Factor 100])

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    Thanks Hans, almost there! The expression on the query that returns the date part which determines the Id from the table is
    Expr_SD+ICP. From the iif statement you gave me, how would I incorporate the expression into it so that that it maps to the ID to then be able to determine the factor 075 or factor 100 value?

    Thanks Darren.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: syntax (2000)

    Sorry, no idea what you mean. You have already linked Expr_OS_ICP to ID in Query2, haven't you?

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: syntax (2000)

    Doh thanks, having a very long day moment. Thanks again for your considered help.

    Cheers Darren.

Posting Permissions

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