IIF Function problems

[amber17]

Hi,

I am a novice on access so I apologize now if this is a stupid question. I have just has my PC reformatted and Access 97 reinstalled, however now I can no longer log into our work database. I get the following error:
Run-time error '3075':

Function isn't available in expressions in query expression 'IIf([expr1]=7,Left([SMSC],4)+"0"+Mid([SMSC]),5,1))'

I am a bit confused as I am referencing the same libraries as my colleague, who can access the database.It seems that either the IIF, Left, Right or Mid function is not being recognized on my application but I have no idea how you would go about installing these as I presumed they are standard functions that come shipped with Access. The only difference between my PC and my colleagues is that I have a later version of the Microsoft Outlook Object Library, mine is 11 whilst everyone else's is 10, not sure if this is relevant.

Any help would be appreciated, I am a bit reluctant to modify the query as it is being referenced in alot of reports, and I am almost certain the problem is with my machine as apposed to the query.

 

[PHV]

Does the DB compiles properly ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

I am not familiar with Access 97. However, this is how I would answer your question if you were using Access 2003.

1. Open an .mdb file in design view. Choose the "modules" section.

2. Press the "new" button. A new window will appear with a new menu bar will appear. If you were a hard-core programmer, you could use this window to write visual basic code. But do not worry, I won't ask you to write any code.

3. On the new menu bar, open the "tools" menu. Choose "references". A long list of items will appear. The checked items on the list are active on your copy of Access. These items add extra capabilities to Access. Write down the exact list of items that are checked.

4. Repeat steps 1-3 with your co-worker's PC.

5. Compare the two lists. If your co-worker has an active item that you do not have, then consider activating that item on your copy of Access.

 

[amber17]

Hi

The Database compiles properly on everyone else's machine just not mine.

As for the references thing I have done this already and the only reference that differs from my colleague is that I have Microsoft Outlook object Library 11.0 where as heres is version 10.

 

[TomCologne]

Is the red parenthesis just a typo in your post?

IIf([expr1]=7,Left([SMSC],4)+"0"+Mid([SMSC][b][COLOR=red])[/color][/b],5,1))

If not, try:

IIf([expr1]=7,Left([SMSC],4)+"0"+Mid([SMSC],5,1))

TomCologne

 

[amber17]

Hi Tom,

Yeah sorry it is just a typo should read :
IIf([expr1]=7,Left([SMSC],4)+"0"+Mid([SMSC],5,1)).

I think I need to find a away of my access 97 to recognize these functions. I modified the query and ran the following IIF([expr1]=7,Left([SMSC],4)), and got the same error so I don't think it is the syntax in the query.

 

[PHV]

So, the DB doesn't compile properly on your machine !
Any chance you could explain us what happen ?
Any error message ? Any highlighted line of code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

Does this database get data from an external source? If so, then maybe your new computer does not have access to that source. I understand that the error message indicates a problem with a function, but error messages can be misleading.

 

[amber17]

Hi All,

Just to let you know, I finally figured out what was causing the error,Steve your right the error messages are misleading. I begged the IT department to revert my outlook back to Outlook XP and this fixed the problem. I suspect it was due to me using Microsoft outlook Object Library 11 and my colleagues using Microsoft outlook Object Library 10. Once they reinstalled outlook I could log in without any problems. Thanks to everyone for their suggestions.

 

[OhioSteve]

great!