Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am recreating Report from Crystal.
    Crystal had this formula entered and I had adapted it for the Access and entered into Control Source of the text box.
    However it gives me numbers instead of Names.

    Can you see why? I must have this formula for each sub report. Is there another way to use it?

    =IIf([Full/Part Time]="F" And [Transfer]=0 And [New Hire]=0,([LAST NAME] & "," & [FIRST NAME] & "-" & [SUB A])) Or IIf([Transfer]=0 And [New Hire]=0,[LAST NAME] & "," & [FIRST NAME] & " (" & [FULL/PART TIME] & ") " & "- " & [SUB A]) Or IIf([New Hire]=0,"~" & [LAST NAME] & "," & [FIRST NAME] & "-" & [Prior SUB] & " to " & [SUB A],"+" & [LAST NAME] & "," & [FIRST NAME] & " (" & [FULL/PART TIME] & ") " & "- " & [SUB A])

    ***************************8


    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The maximum length of an expression varies depending on the verison of Access you are using. See specifications in the Help files for details about your version. One way to solve the issue is to put that expression as a field in the query that the subreport is based on. Try that and see if it solves your problem.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Access 2003.

    Where do I enter in a query? Thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='767663' date='26-Mar-2009 21:44']I am using Access 2003.

    Where do I enter in a query? Thanks[/quote]
    I think WendellB means to enter the formula in the field of a query instead of entering it in the textbox on the form.

    I don't understand the formula. Can you explain in English words with you are trying to achieve ?
    Can you post a stripped version of the db with only the tables and a few record with fictive data ?
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would love to - however I do not have an example - this is what is happening...

    I took the formula from Crystal Report and I want to re-do it for Access Report.

    What I am trying to do is following:

    =IIf([Full/Part Time]="F" And [Transfer]=0 And [New Hire]=0
    *display Name with SUB A Joe, Doe - ERT
    ([LAST NAME] & "," & [FIRST NAME] & "-" & [SUB A]))

    IIf([Transfer]=0 And [New Hire]=0
    *display Name with FT or PT and SUB A like Joe, Doe (F) - ERT
    [LAST NAME] & "," & [FIRST NAME] & " (" & [FULL/PART TIME] & ") " & "- " & [SUB A])

    Or IIf([New Hire]=0
    *display Name with tilda in front ~ Joe, Doe - ERT to PRT
    "~" & [LAST NAME] & "," & [FIRST NAME] & "-" & [Prior SUB] & " to " & [SUB A],"+" & [LAST NAME] & "," & [FIRST NAME] & " (" & [FULL/PART TIME] & ") " & "- " & [SUB A])

    else
    +" & [LAST NAME] & "," & [FIRST NAME] & " (" & [FULL/PART TIME] & ") " & "- " & [SUB A])
    *display Name like + Joe, Doe (F) - PRT
    __________________________________________________ ______

    If this was not 100% accurate - below is Crystal's formula:

    if {TABLE1.FULL/PART TIME} = "F" and {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
    then
    {TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME} & "-" & {TABLE1.SUB LOB}
    else
    if {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
    then
    {TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME}& " (" & {TABLE1.FULL/PART TIME} &") "& "- " & {TABLE1.SUB LOB}
    else
    if {TABLE1.New Hire} = 0
    then
    "~" & {TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME} & "-" & {TABLE1.Prior SUB} & " to " & {TABLE1.SUB LOB}
    else
    "+" &{TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME} & " (" & {TABLE1.FULL/PART TIME} &") "& "- " & {TABLE1.SUB LOB}

    *********THANKS SO MUCH!!!

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='MS_fan' post='767663' date='26-Mar-2009 14:44']I am using Access 2003.

    Where do I enter in a query? Thanks[/quote]

    Each of your subreports must have a data source - either Table1 or an embedded or saved query. If it's a table click the builder button and create a query that has all of the fields of Table1. Then paste the following into an empty column at the end of the query:

    MyControl: IIf([Full/Part Time]="F" And [Transfer]=0 And [New Hire]=0,([LAST NAME] & "," & [FIRST NAME] & "-" & [SUB A])) Or IIf([Transfer]=0 And [New Hire]=0,[LAST NAME] & "," & [FIRST NAME] & " (" & [FULL/PART TIME] & ") " & "- " & [SUB A]) Or IIf([New Hire]=0,"~" & [LAST NAME] & "," & [FIRST NAME] & "-" & [Prior SUB] & " to " & [SUB A],"+" & [LAST NAME] & "," & [FIRST NAME] & " (" & [FULL/PART TIME] & ") " & "- " & [SUB A])

    Then run the query and you should see the data that will be created by the query. Then set the Data Source for the control on the subreport to be MyControl
    Wendell

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='767676' date='26-Mar-2009 22:40'][/quote]
    This is an attempt to rewrite the formula with IIF's.
    I have break the formula in different columns to make it easy to write the formula
    Can you check if Query1 is what you want ?
    [attachment=83093:db2.zip]
    Attached Files Attached Files
    Francois

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You people are awesome! I can't thank you enough. THANKS A MILLION for your help!

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have one more task to complete that is strange.

    I need to color those names
    those with tilda in front should be pink, those with plus sign - purple. Is it doable? Thanks

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='767765' date='27-Mar-2009 15:48']those with tilda in front should be pink, those with plus sign - purple. Is it doable? Thanks[/quote]
    This is possible in a form or report, with conditional formatting for the textbox for Result
    [attachment=83104:condformat.jpg]
    Attached Images Attached Images
    Francois

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, it is awesome!

    I want to report a slight problem.

    The original query from Crystal looked like following:

    if {TABLE1.FULL/PART TIME} = "F" and {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
    then
    {TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME} & "-" &{TABLE1.SUB LOB}

    else if
    {TABLE1.Transfer} = 0 and {TABLE1.New Hire} = 0
    then
    {TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME}& " (" & {TABLE1.FULL/PART TIME} &") "& "- " & {TABLE1.SUB LOB}

    else if
    {TABLE1.New Hire} = 0 then
    "~" & {TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME} & "-" & {TABLE1.Prior SUB} & " to " & {TABLE1.SUB LOB}

    else
    "+" &{TABLE1.LAST NAME}& ","& {TABLE1.FIRST NAME} & " (" & {TABLE1.FULL/PART TIME} &") "& "- " &{TABLE1.SUB LOB}

    ************************************************** **********************

    When we re-did it for the Access we lost else if and else and now I am getting records that are both - tilda and plus etc...

    When I am using OR - it makes my names into -1 for some reason.

    I think Crystal gets each part as a remander of the previous.
    Like a cieve effect. It extracts following portion from left over data after first portion was extracted.
    Am I confusing you completely? Sorry.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='767809' date='27-Mar-2009 19:14']Am I confusing you completely?[/quote]
    Yes I'm confused.
    Nested IIf statements are the same as an If...Then...Else If...Then...Else...End If
    I don't understand why you want to use Or. You can't put an Or between the different IIf statements. You can only use Or in the condition part of the IIf's.
    and now I am getting records that are both - tilda and plus etc
    Can you provide sample data where the formula goes wrong ?
    Francois

  13. #13
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My apology, I was wrong. I forgot some conditions I had entered manually and it made your formula seems off.

    How do I enter into conditional formatting that
    If Name has + - color it green? What do I chose - Expression Is field or Field Value Is?

    Thanks so much and good week to all!!!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Doesn't Francois' reply Post 767771 higher up in this topic answer that?

  15. #15
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, it did, exactly! Thanks so much.

Posting Permissions

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