Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I want to use an IIF statement in my access query, but Excel will not allow me to import the query data because it doesn't recognize an IIF. Can you suggest an alternative? 2003, BTW.
    thanks
    christine

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm not sure how you are trying to import the query data into Excel, but if you export it to Excel from Access it should work just fine. Simply right-click on your saved query, and choose Export and specify an Excel file time and it should get you what you are after. And the IIF statment won't show up in your data - I do this regularly.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I use the import data function while in excel. Most of my users do not use access. All of my reports are created in excel. Therefore, I'm still stuck with the IIF statement not working. Do you have any other suggestions? Thanks again.
    thanks
    christine

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Are you sure it's the IIF that's the problem? We only have Excel and Access 2000 at work, but I've successfully got Data->Get External Data to work in Excel with an Access query containing an IIF.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Aaaahhhhhhhaaaaaa!!!!!

    Thank you for catching that. I was so fixed on the IIF statement that I didn't cross validate my remaining formulas. I found that it was linked to these 2:

    GRP_TRIM: Trim(StrReverse([Full Name]))

    GRP: StrReverse(Trim(Left([GRP_TRIM],InStr([GRP_TRIM]," ")-1)))


    I'm doing that because i have a field in [Full Name] where we have manipulated the data. we had nowhere else to put a "type" so we jury-rigged the name field to show it. any idea what to do here to give me a field that will reflect the type(grp) shown at the end of the name? thanks!!!!

    John S. Doe VENDOR
    John S. Doe CLIENT
    John S. Doe EMPLOYEE
    John S. Doe 12/31/2011
    thanks
    christine

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've found it. It's the StrReverse formula that is not translating to Excel very well. Is there an alternative to the problem shown above? As always, thanks!
    thanks
    christine

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    What is the StrReverse function trying to do? It's not a built in function.

    Can you give an example of the contents of the field and what you want it to look like in the output.

    You may be able to get what you want using InStrRev([GRP_TRIM]," ") to get the position of the last space and then use Mid() to reassemple the bits you want.

    if your [Full Name] is "John S. Doe VENDOR"

    Mid([Full Name], InStrRev([Full Name], " ") + 1)

    will result in "VENDOR"

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    StrReverse actually gives you the string backwards - see StrReverse Function - Access .... I think the approach you suggest should work.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    StrReverse and StrRev are my actual problem. those expressions aren't recognized when i'm in excel pull in my data from access. I basically need create fields that would reflect the following for the [FULL NAME] fields shown below. The problem is that this is a free-form text field we are manipulating. I was trying to do a string reverse and take into account any typos or spaces that i know the team will mess up during setup. by doing a reverse, i thought if i put the "type" information first, i could possibly jury-rig it futher to take into account fat-fingering the data. since StrReverse doesn't play well with excel, is there any other suggestions that you might have?

    the formula previously suggested: Mid([Full Name], InStrRev([Full Name], " ") + 1) returned blanks. it's right on the tip of the brain, but i just can't get over the hump. thanks!


    VENDOR
    CLIENT
    EMPLOYEE
    12/31/2011


    John S. Doe VENDOR
    John S. Doe CLIENT
    John S. Doe EMPLOYEE
    John S. Doe 12/31/2011
    thanks
    christine

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I think I see the issue - the Mid as Kent typed it only has the starting character, and doesn't tell you how many to get, so it returns blanks. The function should look like
    Mid([Full Name], InStrRev([Full Name], " ") + 1,25)
    The 25 is just a stab at what the maximum length the string following the name might be. You could get cute and calculate it using the Len() and a second InStrRev() function, but the mid will take whatever length you put in there and return only the number of characters that exist.
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The problem is that StrReverse, StrRev, and InStrRev do not translate to Excel. So when I have those expressions in my query, excel doesn't know how to read them. It fails in getting the data out. hhhhhmmmm. i'm really stumped on this one. i think i might just have to leave it without pulling out those fields and deliver it down to excel that way. then, within excel, i'll write formulas there to pull out the "type" .....that is, unless someone else can think of anything. thanks all for trying!!!!
    thanks
    christine

Posting Permissions

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