IIF stmt does not allow query from Excel

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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”

Reply | Quote

StrReverse actually gives you the string backwards – see StrReverse Function – Access …. I think the approach you suggest should work.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!!!!

Reply | Quote