Results 1 to 11 of 11
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    punctuation after format

    All; using access 2016..I have a report I need to revise for a user. The report uses nested and multiple IIf statements. One in particular ends with a number format as a percent: ex.
    Code:
    which was" & " " & Format([rPrice],"Percent")) & ". " &
    which = 3.65% But the beginning of the IIF statements does not have a price and just ends with a "." and the last part of the state ends with a period also but the way it's written applys to the whole statement. Thus; it looks like the "." is off centered. Ex:

    Code:
    IIf([bstat]="Increase","Please note the pending increase.","Please note the pending decrease of" & " " & Format([decrAmt],"Percent")) & " " & IIf([btat]="Increase"," ","from the last decrease which was" & " " & Format([decrAmt],"Percent")) & ". " &
    I get the following for the true part:
    Code:
    "Please note the pending increase  .
    and the following for the false part:
    Code:
    "Please note the pending decrease of 3.65% from the last decrease which was 3.85%.
    I tried several different ways and I can't get the "." correct after the true part to put the "." right after the word increase. Any ideas?? Hope I didn't make this confusing. Thanks

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is this 2nd IIf condition supposed to be btat or bstat? You have btat in the line above.

    Looks like if your first condition is true it should put the period after the word as that is how you have it in that text string. Also seems that from your output, it is doing that 2nd IIf statement and adding the space somehow.

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    suggest initially changing the true "." for another item such as > and see what effect that has

    If it doesn't pick up the > then the issue if further down the line

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Code:
    IIf([bstat]="Increase","Please note the pending increase","Please note the pending decrease of" & " " & Format([decrAmt],"Percent")) & (IIf([bstat]="Increase",""," from the last decrease which was" & " " & Format([decrAmt],"Percent")) & ". ")

    This may be it?

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    It tells me I have too many ) and when I eliminate it; it tells me I'm missing it..smh

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I just tried this and did not get an error.

    Code:
       IIf([bstat]="Increase","Please note the pending increase.","Please note the pending decrease of " & Format([decrAmt],"Percent")) 
    & (IIf([bstat]="Increase",""," from the last decrease which was " & Format([decrAmt],"Percent")) & ". ")
    I deleted a couple of extra "&".... added the space inside the preceding string.

    The open/close parenthesis's before the second IIF() are not needed (in RED above):
    Code:
      IIf([bstat]="Increase","Please note the pending  increase.","Please note the pending decrease of " &  Format([decrAmt],"Percent")) 
    & IIf([bstat]="Increase",""," from the last decrease which was " & Format([decrAmt],"Percent")) & ". "
    The easiest way to ensure the correct paring of parenthesis is to count the number of open parenthesis and the number of close parenthesis.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    IIf([bstat]="Increase","Please note the pending increase.","Please note the pending decrease of " & Format([decrAmt],"Percent")) & IIf([bstat]="Increase",""," from the last decrease which was " & Format([decrAmt],"Percent")) & ". "

    I might be missing something but seems something is not right with the logic of the 2nd IIf([bstat]="Increase","". If the first condition [bstat] does not = "Increase" and you move to the else part starting with "Please note the pending decrease", then the code further on that checks if [bstat] = Increase again will never get triggered. So why have it in there at all I guess is my question. Lot of syntax so maybe I am missing something and it is needed.

    Also in your code when it is pending decrease, your have Format([decrAmt],"Percent")) & "from the last decrease which was" & Format([decrAmt],"Percent")) which is the same field or value? So how are those different values in your output line if they are the same field?



  8. #8
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Quote Originally Posted by Bulzie View Post
    IIf([bstat]="Increase","Please note the pending increase.","Please note the pending decrease of " & Format([decrAmt],"Percent")) & IIf([bstat]="Increase",""," from the last decrease which was " & Format([decrAmt],"Percent")) & ". "

    I might be missing something but seems something is not right with the logic of the 2nd IIf([bstat]="Increase","". If the first condition [bstat] does not = "Increase" and you move to the else part starting with "Please note the pending decrease", then the code further on that checks if [bstat] = Increase again will never get triggered. So why have it in there at all I guess is my question. Lot of syntax so maybe I am missing something and it is needed.

    Also in your code when it is pending decrease, your have Format([decrAmt],"Percent")) & "from the last decrease which was" & Format([decrAmt],"Percent")) which is the same field or value? So how are those different values in your output line if they are the same field?


    You are correct. The second [decrAmt] should be [PdecrAmt]. Just type but logic works fine. Thanks

  9. #9
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Steve; tried this but I'm getting two ".." after "Pending Increase." If I don't put the "." It will have a space.

  10. #10
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I don't think I can get around this other than to put another IIF statement to accommodate the "." in the false part.

  11. #11
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Got it...I still had too many spaces and I was able to take out the "." in the True part. thanks for all your help!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-10-2017, 11:03 PM
  2. Replies: 4
    Last Post: 02-06-2017, 09:23 PM
  3. VBA environment Does't support some Punctuation marks
    By KH Ahmed Bara in forum Programming
    Replies: 3
    Last Post: 05-18-2016, 09:02 AM
  4. Replies: 5
    Last Post: 10-19-2014, 09:52 AM
  5. Punctuation blowup
    By davisgwe in forum Access
    Replies: 2
    Last Post: 09-15-2011, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums