Error: "Function is not available in expressions in query expression"

[deko]

After developing an MDB in Access 2003 on WS03, then making it into an MDE
and deploying it on a WinXP box with Access 2003 installed, I get this
error:

Function is not available in expressions in query expression
[below is full query that appears in part in the error message]
SELECT IIf(IsNull([Address1]),"",[Address1] & Chr(13) & Char(10)) &
IIf(IsNull([Address]),"",[Address] & Char(13) & Char(10)) &
IIf(IsNull([Address]),"",[Address] & Char(13) & Char(10)) &
IIf(IsNull([City]),"",] & IIf(IsNull([State]),"",", ") &
] & " " & [Zip]) & Char(13) & Char(10) &
IIf(IsNull([Country]),"",]) AS txtAddress, Add_ID
FROM bladders
WHERE Entity_ID=Wryer ("form","Entity _ID")
ORDER BY Primary;

I searched the MS Knowledgebase, but found nothing. From what I've
discovered from other resources on the web, this may have something to do
with either the version of the Expression Service DLL, or an MDAC service
pack. I tried installing the latest JET service pack, but that did not help.
I also tried copying the expsrv.dll from the development box to the
deployment box, but that did not help. I'm wondering if this has anything to
do with other (seemingly unrelated) references. Here's a list of references
on the development box:
?ReferenceInfo
Reference: VBA
Location: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6 \VBE6.DLL
Reference: Access
Location: C:\Program Files\Microsoft Office\OFFICE11 \MSACC.OLB
Reference: stdole
Location: C:\WINDOWS\syst em32\stdole2.ta b
Reference: DAO
Location: C:\Program Files\Common Files\Microsoft Shared\DAO\DAO3 60.DLL
Reference: ADODB
Location: C:\Program Files\Common Files\System\ad o\msado25.tlb
Reference: Excel
Location: C:\Program Files\Microsoft Office\OFFICE11 \EXCEL.EXE
Reference: Office
Location: C:\Program Files\Common Files\Microsoft Shared\OFFICE11 \MSO.DLL
Reference: Outlook
Location: C:\Program Files\Microsoft Office\OFFICE11 \msoutl.olb
Reference: Word
Location: C:\Program Files\Microsoft Office\OFFICE11 \MSWORD.OLB

In an MDE, my understanding is that all of these are supposed to stick,
regardless of the client. If something is missing, the reference will appear
as MISSING: "whatever".

The reason I format the address in a query rather than in VBA is speed. As a
user scrolls through records in the database, each record's address appears
in a subform. Making this query the RecordSource of the subform is much
faster than using DLookup for the elements of the address. I'd hate to have
to rework the query, and would much rather figure out what's missing on the
target box so if I hit other deployment snags I'll know where to look.

[deko]

> I'm wondering if this has anything to

do with other (seemingly unrelated) references.

That was in fact the problem. I found a broken reference - Outlook - on the
client box. Outlook was not installed with the Office installation. Hard
to see how this is related to the expression service. Nevertheless, once
the broken reference was fixed, the error went away.

[Salad]

deko wrote:

After developing an MDB in Access 2003 on WS03, then making it into an MDE
and deploying it on a WinXP box with Access 2003 installed, I get this
error:

Function is not available in expressions in query expression
[below is full query that appears in part in the error message]
SELECT IIf(IsNull([Address1]),"",[Address1] & Chr(13) & Char(10)) &
IIf(IsNull([Address]),"",[Address] & Char(13) & Char(10)) &
IIf(IsNull([Address]),"",[Address] & Char(13) & Char(10)) &
IIf(IsNull([City]),"",] & IIf(IsNull([State]),"",", ") &
What is that ] doing ] & " " & [Zip]) & Char(13) & Char(10) & What is that ] doing IIf(IsNull([Country]),"",]) AS txtAddress, Add_ID What is that ] doing? FROM bladders
WHERE Entity_ID=Wryer ("form","Entity _ID")
ORDER BY Primary;

Is your query statement a mistake in typing? Is the ] a new syntax in
A2003?

It's hard to say what function is not working. What happens if you
remove the Where clause? Is Wryer() the problem...or it it the orphan
]'s that are causing the problem?

[deko]

> It's hard to say what function is not working. What happens if you

remove the Where clause? Is Wryer() the problem...or it it the orphan
]'s that are causing the problem?

Thanks for the reply

It turned out to be a broken reference to Outlook - the typos were a result
of spell check....

the QryPrm("frm0"," Entity_ID") function works okay when it's spelled
right...

[Terry Kreft]

The problem with broken references is that they cause errors apparently
related to other libraries, this only happens though when the references
collection has to be searched because a non-library specific call has ben
made.

It's important to check and fix broken references as early as possible in
code and before you do that disambiguate calls.
--
Terry Kreft
MVP Microsoft Access
"deko" <de**@hotmail.c om> wrote in message
news:Sv******** *********@newss vr27.news.prodi gy.com...

I'm wondering if this has anything to
do with other (seemingly unrelated) references.
That was in fact the problem. I found a broken reference - Outlook - on

the client box. Outlook was not installed with the Office installation. Hard
to see how this is related to the expression service. Nevertheless, once
the broken reference was fixed, the error went away.

[Salad]

deko wrote:

It's hard to say what function is not working. What happens if you
remove the Where clause? Is Wryer() the problem...or it it the orphan
]'s that are causing the problem?

Thanks for the reply

It turned out to be a broken reference to Outlook - the typos were a result
of spell check....

the QryPrm("frm0"," Entity_ID") function works okay when it's spelled
right...

I still wonder about those extra ] brackets.