Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: SQL expression parameter

    Looking for some help with SQL expression builder...this is an Access 2000 file

    I have written 5 expressions in my query, 2 expressions are using data from another expresion....design view:
    1.) PROFIT: ([INVAMT]-[TAX])-([COST]+[OC])
    2.) OC: IIf([FRTINV]=1,0,IIf([FRTINV]=0 And [INVAMT]<50,0,IIf([FRTINV]=0 And [INVAMT]<201,2,IIf([FRTINV]=0 And [INVAMT]<601,4,IIf([FRTINV]=0 And [INVAMT]<1001,6,IIf([FRTINV]=0 And [INVAMT]<2501,15,IIf([FRTINV]=0 And [INVAMT]<5001,30,IIf([FRTINV]=0 And [INVAMT]<10001,60,IIf([FRTINV]=0 And [INVAMT]<25001,90,120)))))))))

    this is how it appears in SQL:
    SELECT UQ3OIL.CustID, UQ3OIL.tblCust31212_ACCT, UQ3OIL.AcctName, UQ3OIL.Terms, UQ3OIL.TaxAcct, UQ3OIL.FreightAcct, UQ3OIL.Salesrep1, UQ3OIL.Salesrep2, UQ3OIL.Unapplied, UQ3OIL.Split, UQ3OIL.CUSTPO, UQ3OIL.NENO, UQ3OIL.INVDATE, UQ3OIL.INVNO, UQ3OIL.INVAMT, UQ3OIL.SHIPDATE, UQ3OIL.COST, UQ3OIL.COSTOPEN, UQ3OIL.FRT, UQ3OIL.TAX, UQ3OIL.RESP, UQ3OIL.SPLITINV, UQ3OIL.SLMN2, UQ3OIL.SLMN3, UQ3OIL.FRTINV, UQ3OIL.AdjID, UQ3OIL.CMNO, UQ3OIL.AINVNO, UQ3OIL.ADJDATE, UQ3OIL.ADJUSTMENT, UQ3OIL.COSTADJ, UQ3OIL.ADJNOTE, UQ3OIL.PID, UQ3OIL.PDATE, UQ3OIL.PAYMENT, UQ3OIL.PINVNO, UQ3OIL.BAL, Date()-[INVDATE] AS AGE, [INVDATE]+[Terms] AS DUE, IIf([FRTINV]=1,0,IIf([FRTINV]=0 And [INVAMT]<50,0,IIf([FRTINV]=0 And [INVAMT]<201,2,IIf([FRTINV]=0 And [INVAMT]<601,4,IIf([FRTINV]=0 And [INVAMT]<1001,6,IIf([FRTINV]=0 And [INVAMT]<2501,15,IIf([FRTINV]=0 And [INVAMT]<5001,30,IIf([FRTINV]=0 And [INVAMT]<10001,60,IIf([FRTINV]=0 And [INVAMT]<25001,90,120))))))))) AS OC, ([INVAMT]-[TAX])-([COST]+[OC]) AS PROFIT, IIf([SPLITINV]=0,[PROFIT]*0.43,IIf([SPLITINV]=1,[PROFIT]*0.215,IIf([SPLITINV]=2,[PROFIT]*0.1433))) AS COMM
    FROM UQ3OIL
    GROUP BY UQ3OIL.CustID, UQ3OIL.tblCust31212_ACCT, UQ3OIL.AcctName, UQ3OIL.Terms, UQ3OIL.TaxAcct, UQ3OIL.FreightAcct, UQ3OIL.Salesrep1, UQ3OIL.Salesrep2, UQ3OIL.Unapplied, UQ3OIL.Split, UQ3OIL.CUSTPO, UQ3OIL.NENO, UQ3OIL.INVDATE, UQ3OIL.INVNO, UQ3OIL.INVAMT, UQ3OIL.SHIPDATE, UQ3OIL.COST, UQ3OIL.COSTOPEN, UQ3OIL.FRT, UQ3OIL.TAX, UQ3OIL.RESP, UQ3OIL.SPLITINV, UQ3OIL.SLMN2, UQ3OIL.SLMN3, UQ3OIL.FRTINV, UQ3OIL.AdjID, UQ3OIL.CMNO, UQ3OIL.AINVNO, UQ3OIL.ADJDATE, UQ3OIL.ADJUSTMENT, UQ3OIL.COSTADJ, UQ3OIL.ADJNOTE, UQ3OIL.PID, UQ3OIL.PDATE, UQ3OIL.PAYMENT, UQ3OIL.PINVNO, UQ3OIL.BAL, Date()-[INVDATE], [INVDATE]+[Terms], IIf([FRTINV]=1,0,IIf([FRTINV]=0 And [INVAMT]<50,0,IIf([FRTINV]=0 And [INVAMT]<201,2,IIf([FRTINV]=0 And [INVAMT]<601,4,IIf([FRTINV]=0 And [INVAMT]<1001,6,IIf([FRTINV]=0 And [INVAMT]<2501,15,IIf([FRTINV]=0 And [INVAMT]<5001,30,IIf([FRTINV]=0 And [INVAMT]<10001,60,IIf([FRTINV]=0 And [INVAMT]<25001,90,120))))))))), ([INVAMT]-[TAX])-([COST]+[OC]), IIf([SPLITINV]=0,[PROFIT]*0.43,IIf([SPLITINV]=1,[PROFIT]*0.215,IIf([SPLITINV]=2,[PROFIT]*0.1433)));

    I am getting parameters for OC and PROFIT everytime I run the query. Entering through returrns calculations correct, so it knows what I want - I just think I wrote it incorrect. I think it's because those 2 names I gave (before the colon) I am using in the expression as a field in brackets [].

    The report that this query is used in also prompts these parameters, and if I enter through there, the PROFIT is in the underlying query but doesn't appear in the report. I am checking field properties in there - and wasn't sure if it just has to do with what's incorrect in the query.

    Anyone can help why I'm getting parametered here?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In many cases, Access' T-SQL doesn't like when you have calculated fields, and other fields that call on those calculated fields, in the same query. Access won't store the values first and use them the way you want.

    The best way around this is to have your calculated fields (OC and PROFIT) in a separate sub-query, including all the other fields in the underlying datasource that do NOT include OC and PROFIT in their calculations. Save the sub-query. In a new query, add your original datasource and your sub-query, making JOINs as necessary. In the QBE grid, include '*' (all) from the sub-query, and only the fields from the original datasource that include the OC and PROFIT fields in their calculations.

    Should work (I know I had to get around the same problem many times),

    Sam

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    hmm - I can't get this to work. I'm so confused. Access gets me sometimes!

    My original data source is a union query, I made a select query from the union query to do the 5 expressions. I am using it as a subquery, with every field and expressions.

    Next I make a new query - with the subquery and the union query. All of the fields are exactly the same except my expressions. I take all '*' from the sub-query.........what do I need from the original? The fields I used to create OC and PROFIT?

    When I tried that I was still prompted and it duplicated itself hundreds of thousands of times. Not sure how to join since they are the exact same. I may need to break this down more - UQ to selqry twice? One has exp the other doesn't?

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Sorry to say, you misinterpreted my words entirely. Please review my posting and follow the instructions exactly as I wrote them.

    Sam

Posting Permissions

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