none
What am I doing wrong? RRS feed

  • Question

  • Expr2: ([LAST CLS BAL]<100,"<$100.00" Or [LAST CLS BAL] Between 100 And 499.99,"$100.00-$499.99" Or [LAST CLS BAL] Between 500 And 999.99,"$500.00-$999.99" Or [LAST CLS BAL] Between 1000 And 1999.99,"$1,000.00-$1,999.99" Or [LAST CLS BAL] Between 2000 And 2999.99,"$2,000.00-$2,999.99" Or [LAST CLS BAL] Between 3000 And 4999.99,"$3,000.00-$4,999.99" Or [LAST CLS BAL]>=5000,">=$5,000","ERR")

    Thursday, November 19, 2015 3:39 PM

Answers

  • more readable than nested IIF is switch function:

    nz(
       switch(
              [LAST CLS BAL]<100,"<$100.00",
              [LAST CLS BAL] Between 100 And 499.99,"$100.00-$499.99",
              [LAST CLS BAL] Between 500 And 999.99,"$500.00-$999.99",
              [LAST CLS BAL] Between 1000 And 1999.99,"$1,000.00-$1,999.99",
              [LAST CLS BAL] Between 2000 And 2999.99,"$2,000.00-$2,999.99",
              [LAST CLS BAL] Between 3000 And 4999.99,"$3,000.00-$4,999.99",
              [LAST CLS BAL]>5000,">$5,000.00"
       )
    ,"err") 
         as expr2


    Michał

    • Proposed as answer by David_JunFeng Friday, November 20, 2015 2:56 AM
    • Marked as answer by David_JunFeng Wednesday, December 2, 2015 8:54 AM
    Thursday, November 19, 2015 7:20 PM

All replies

  • At position 79 is a point missing....

    Thursday, November 19, 2015 3:48 PM
  • Try the following:

    ClsStatus: IIf([LAST CLS BAL]<100,"<$100.00",IIf([LAST CLS BAL] Between 100 And 499.99,"$100.00-$499.99",IIf([LAST CLS BAL] Between 500 And 999.99,"$500.00-$999.99",IIf([LAST CLS BAL] Between 1000 And 1999.99,"$1,000.00-$1,999.99",IIf([LAST CLS BAL] Between 2000 And 2999.99,"$2,000.00-$2,999.99",IIf([LAST CLS BAL] Between 3000 And 4999.99,"$3,000.00-$4,999.99","ERR"))))))

    Thursday, November 19, 2015 4:00 PM
  • This is the error message it gives me.....Syntax error (comma) in query expression
    Thursday, November 19, 2015 4:01 PM
  • This is the error message it gives me.....Syntax error (comma) in query expression

    That's because you are designing your SQL incorrectly.  The example I provided for you will handle your requirement.
    Thursday, November 19, 2015 4:32 PM
  • Thank you!  It does run using this but it puts every line into the <$100.00 category no matter what the last cls bal shows...
    Thursday, November 19, 2015 5:47 PM
  • Post your SQL statement and then post a screen shot of your results.
    Thursday, November 19, 2015 6:25 PM
  • Expr2: IIf([LAST CLS BAL]<100,"<$100.00",IIf([LAST CLS BAL] Between 100 And 499.99,"$100.00-$499.99",IIf([LAST CLS BAL] Between 500 And 999.99,"$500.00-$999.99",IIf([LAST CLS BAL] Between 1000 And 1999.99,"$1,000.00-$1,999.99",IIf([LAST CLS BAL] Between 2000 And 2999.99,"$2,000.00-$2,999.99",IIf([LAST CLS BAL] Between 3000 And 4999.99,"$3,000.00-$4,999.99",IIf([LAST CLS BAL]>5000,">$5,000.00","ERR")))))))

    It is asking me to enter a parameter value (wont let me add screenshot on here), If I enter 0 for parameter value it runs and gives me <$100.00 for every line.....if I just hit enter (no value) it puts ERR on every line

    Thursday, November 19, 2015 6:36 PM
  • I asked that you post your SQL, not just the expression that we've developed here.  Open your query in SQL view and copy the SQL statement, then paste it here.

    To post a screen shot, use the Windows snipping tool to copy your screen and save it as a jpeg image.  Then click on the last button above your new post that looks like a sun over the mountains.  A dialog will be displayed for you to select your file jpeg for upload.  Click the + at the top left...  the rest should be intuitive enough.

    Thursday, November 19, 2015 6:45 PM
  • INSERT INTO [tbl_06-June_payments_details] ( CLS_MMYY, ACCT_NBR, PMT_AMT, RCY, CONTR_LVL, LAST_CLS_BAL, ORGINAL_RCY3_MONTH, BAL_RANGE )
    SELECT CDSADMIN_FACT_RECOVERY_MONTH.CLS_MMYY, CDSADMIN_FACT_RECOVERY_MONTH.ACCT_NBR, CDSADMIN_FACT_RECOVERY_MONTH.PMT_AMT, CDSADMIN_FACT_RECOVERY_MONTH.RCY, CDSADMIN_FACT_RECOVERY_MONTH.CONTR_LVL, CDSADMIN_FACT_RECOVERY_MONTH.LAST_CLS_BAL, "June" AS Expr1, IIf([LAST CLS BAL]<100,"<$100.00",IIf([LAST CLS BAL] Between 100 And 499.99,"$100.00-$499.99",IIf([LAST CLS BAL] Between 500 And 999.99,"$500.00-$999.99",IIf([LAST CLS BAL] Between 1000 And 1999.99,"$1,000.00-$1,999.99",IIf([LAST CLS BAL] Between 2000 And 2999.99,"$2,000.00-$2,999.99",IIf([LAST CLS BAL] Between 3000 And 4999.99,"$3,000.00-$4,999.99",IIf([LAST CLS BAL]>5000,">$5,000.00","ERR"))))))) AS Expr2
    FROM CDSADMIN_FACT_RECOVERY_MONTH INNER JOIN [tbl_06-June_90+] ON CDSADMIN_FACT_RECOVERY_MONTH.ACCT_NBR = [tbl_06-June_90+].ACCT_NBR
    WHERE (((CDSADMIN_FACT_RECOVERY_MONTH.CLS_MMYY)=201510));

    I know how to add a jpeg, however until my account can be verified they are not allowing me to attach anything here.....

    • Edited by rwasara Thursday, November 19, 2015 7:04 PM
    Thursday, November 19, 2015 6:57 PM
  • more readable than nested IIF is switch function:

    nz(
       switch(
              [LAST CLS BAL]<100,"<$100.00",
              [LAST CLS BAL] Between 100 And 499.99,"$100.00-$499.99",
              [LAST CLS BAL] Between 500 And 999.99,"$500.00-$999.99",
              [LAST CLS BAL] Between 1000 And 1999.99,"$1,000.00-$1,999.99",
              [LAST CLS BAL] Between 2000 And 2999.99,"$2,000.00-$2,999.99",
              [LAST CLS BAL] Between 3000 And 4999.99,"$3,000.00-$4,999.99",
              [LAST CLS BAL]>5000,">$5,000.00"
       )
    ,"err") 
         as expr2


    Michał

    • Proposed as answer by David_JunFeng Friday, November 20, 2015 2:56 AM
    • Marked as answer by David_JunFeng Wednesday, December 2, 2015 8:54 AM
    Thursday, November 19, 2015 7:20 PM
  • Thanks everyone for all your help, I finally figured it out!!!
    Thursday, November 19, 2015 7:41 PM