Access XP weirdness ...

[JustMe]

I don't know if this has anything to do with AccessXP running on Terminal
Services with Access97/2000 also installed, but here is one example of a
query that does not work any longer:

SELECT DLookUp("MajorC ategory","AutoT ypes","[autoType]='1'") AS
MajorCategory, PreOwned.PaintC olor
FROM PreOwned;

When I try to run the above query I get the following error:

UNKNOWN FUNCTION NAME

If I execute the exact same dlookup from the immediate window, the proper
data is returned with no error.

Any suggestions? I have very little hair left on my head after this!!!

Thanks,
--Terry

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003

[Steve Jorgensen]

I just recently was called in to fix a similar problem at a client site
with Access 2000. It seems, machines with some of the newest updates from
Microsoft can no longer execute certain built-in functions from within
queries or calculated controls (things that use the "Expression Service").
In my case, the original author had been using the Dir$ function from a
query.

Like you, I tried all the usual fixes, going so far as to try it in a new,
blank database created on the problem machine. Finally, I ended up simply
deciding this was a "feature" of a new update from Microsoft, and working
around it by writing my own user-defined function to call Dir$. I suspect
your situation may be the same issue.

On Sat, 25 Oct 2003 07:58:11 -0600, "JustMe" <te******@saskt el.net> wrote:

Hi Douglas ...

Thanks for your suggestions. Unfortunately still a no-go. I've tried
everything with no success.

Any other ideas? Anyone?

--Terry
"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:tj******* ******@news04.b loor.is.net.cab le.rogers.com.. .

Here's my standard blurb on fixing reference problems:

On the machine(s) where it's not working, open any code module (or open

the

Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar.

Examine

all of the selected references.

If any of the selected references have "MISSING:" in front of them,

unselect

them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back

out

of the dialog, then go back in and unselect the reference you just added.

If

that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"JustMe" <te******@saskt el.net> wrote in message
news:vp******** ****@corp.super news.com...

> Hi Wayne ...
>
> I've already tried this. All references are identical to my other

machine.

> None are missing.
>
> Any other suggestions?
> --Terry
> "Wayne Morgan" <co************ *************** @hotmail.com> wrote in

message

> news:O%******** **********@news svr16.news.prod igy.com...
> > If it works fine on other machines then it sounds like a References
> problem. Open a code
> > window (Alt+F11) and go to Tools|Reference s. Compare them with amachine > that does work.
> > Also, make sure none of them are listed as MISSING.
> >
> > --
> > Wayne Morgan
> > Microsoft Access MVP
> >
> >
> > "JustMe" <te******@saskt el.net> wrote in message
> news:vp******** ****@corp.super news.com...
> > > By the way, the exact same query works fine in Access 97 on the same
> > > machine.
> > >
> > > Terry
> > > "JustMe" <te******@saskt el.net> wrote in message
> > > news:vp******** ****@corp.super news.com...
> > > > Hi Wayne ...
> > > >
> > > > Thanks for your reply. I tried surrounding ALL the fields with
> brackets
> > > > with no success. I still get the UNKNOWN FUNCTION NAME error.
> > > >
> > > > Any other suggestions? Anyone?
> > > >
> > > > The same query works fine on other machines running AccessXP.I've > tried
> > > > uninstalling/reinstalling XP. I've tried everything I can thinkof. > > > >
> > > > Help!
> > > >
> > > > "Wayne Morgan" <co************ *************** @hotmail.com> wrotein > > > message
> > > > news:a8******** ***********@new ssvr33.news.pro digy.com...
> > > > > Even without a space in the name, I think it wants bracketsaround > the
> > > > field name.
> > > > >
> > > > > DLookUp("[MajorCategory]", "Auto Types", [auto Type]='1'")
> > > > >
> > > > > --
> > > > > Wayne Morgan
> > > > > Microsoft Access MVP
> > > > >
> > > > >
> > > > > "JustMe" <te******@saskt el.net> wrote in message
> > > > news:vp******** ****@corp.super news.com...
> > > > > > I don't know if this has anything to do with AccessXP runningon > > > > Terminal
> > > > > > Services with Access97/2000 also installed, but here is one
> example of
> > > a
> > > > > > query that does not work any longer:
> > > > > >
> > > > > > SELECT DLookUp("MajorC ategory","AutoT ypes","[autoType]='1'")AS > > > > > > MajorCategory, PreOwned.PaintC olor
> > > > > > FROM PreOwned;
> > > > > >
> > > > > > When I try to run the above query I get the following error:
> > > > > >
> > > > > > UNKNOWN FUNCTION NAME
> > > > > >
> > > > > > If I execute the exact same dlookup from the immediate window,

the

> > > > proper
> > > > > > data is returned with no error.
> > > > > >
> > > > > > Any suggestions? I have very little hair left on my headafter > > > this!!!
> > > > > >
> > > > > > Thanks,
> > > > > > --Terry
> > > > > >
> > > > > >
> > > > > >
> > > > > > ---
> > > > > > Outgoing mail is certified Virus Free.
> > > > > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > > > > Version: 6.0.516 / Virus Database: 313 - Release Date:9/2/2003 > > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > > ---
> > > > Outgoing mail is certified Virus Free.
> > > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > > Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003
> > > >
> > > >
> > >
> > >
> > > ---
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003
> > >
> > >
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003
>
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/3/2003

[Tom van Stiphout]

On Sat, 25 Oct 2003 16:05:33 GMT, "Wayne Morgan"
<co************ *************** @hotmail.com> wrote:

Wayne, bracketing field names or table names is not needed (although
harmless) when they are single words without funny characters.
-Tom.

Let's go back a step, you said you surrounded ALL the field with brackets as a try. It
doesn't like brackets around the table's name, did you bracket that one also?

[David W. Fenton]

no****@nospam.n ospam (Steve Jorgensen) wrote in
<r2************ *************** *****@4ax.com>:

I just recently was called in to fix a similar problem at a client
site with Access 2000. It seems, machines with some of the newest
updates from Microsoft can no longer execute certain built-in
functions from within queries or calculated controls (things that
use the "Expression Service"). In my case, the original author had
been using the Dir$ function from a query.

Like you, I tried all the usual fixes, going so far as to try it
in a new, blank database created on the problem machine. Finally,
I ended up simply deciding this was a "feature" of a new update
from Microsoft, and working around it by writing my own
user-defined function to call Dir$. I suspect your situation may
be the same issue.

Would that be Jet 4 SP7? One of the things it does is do something
to the security of Jet that disallows certain kinds of VBA calls
from SQL. At least, that was my understanding when it initially
came out.

Has *anyone* found SP7 to be a good thing? I know it causes
problems with replication, for one, and many people have rolled
back to SP6.

Is there anythign that SP7 fixes that is essential?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

te******@saskte l.net (JustMe) wrote in
<vp************ @corp.supernews .com>:

I don't know if this has anything to do with AccessXP running on
Terminal Services with Access97/2000 also installed, but here is
one example of a query that does not work any longer:

SELECT DLookUp("MajorC ategory","AutoT ypes","[autoType]='1'") AS
MajorCategor y, PreOwned.PaintC olor
FROM PreOwned;

When I try to run the above query I get the following error:

UNKNOWN FUNCTION NAME

If I execute the exact same dlookup from the immediate window, the
proper data is returned with no error.

Any suggestions? I have very little hair left on my head after
this!!!

Er, why not do this with SQL?

SELECT AutoTypes.Major Category, PreOwned.PaintC olor
FROM AutoTypes, PreOwned
WHERE AutoTypes.AutoT ype = '1'

Of course, why not hardwire the result in the first place? If you
know that the relevant AutoType is 1, don't you know what the
MajorCategory is? Or is AutoType stored in PreOwned? If so, then
you could join the two tables.

Something looks wrong here -- I can't think of a case where it
makes sense to use a DLookup in a query, myself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tom van Stiphout]

On Sat, 25 Oct 2003 21:09:57 GMT, dX********@bway .net.invalid (David
W. Fenton) wrote:

SP8 just came out. I quickly read the what's new section, and it
seemed to fix some SP7 bugs.
-Tom.

no****@nospam. nospam (Steve Jorgensen) wrote in
<r2*********** *************** ******@4ax.com> :

I just recently was called in to fix a similar problem at a client
site with Access 2000. It seems, machines with some of the newest
updates from Microsoft can no longer execute certain built-in
functions from within queries or calculated controls (things that
use the "Expression Service"). In my case, the original author had
been using the Dir$ function from a query.

Like you, I tried all the usual fixes, going so far as to try it
in a new, blank database created on the problem machine. Finally,
I ended up simply deciding this was a "feature" of a new update
from Microsoft, and working around it by writing my own
user-defined function to call Dir$. I suspect your situation may
be the same issue.

Would that be Jet 4 SP7? One of the things it does is do something
to the security of Jet that disallows certain kinds of VBA calls
from SQL. At least, that was my understanding when it initially
came out.

Has *anyone* found SP7 to be a good thing? I know it causes
problems with replication, for one, and many people have rolled
back to SP6.

Is there anythign that SP7 fixes that is essential?

[JustMe]

Hi David ...

I realize the query makes no sense. It is a VERY simplified version of the
real query I'm having difficulties with. Regardless of whether it makes
sense or not, it 'should' work though right? The fact is it 'does' work on
an identical Win2K machine.

Any suggestions?
--Terry
"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .86...

te******@saskte l.net (JustMe) wrote in
<vp************ @corp.supernews .com>:

I don't know if this has anything to do with AccessXP running on
Terminal Services with Access97/2000 also installed, but here is
one example of a query that does not work any longer:

SELECT DLookUp("MajorC ategory","AutoT ypes","[autoType]='1'") AS
MajorCategor y, PreOwned.PaintC olor
FROM PreOwned;

When I try to run the above query I get the following error:

UNKNOWN FUNCTION NAME

If I execute the exact same dlookup from the immediate window, the
proper data is returned with no error.

Any suggestions? I have very little hair left on my head after
this!!!

Er, why not do this with SQL?

SELECT AutoTypes.Major Category, PreOwned.PaintC olor
FROM AutoTypes, PreOwned
WHERE AutoTypes.AutoT ype = '1'

Of course, why not hardwire the result in the first place? If you
know that the relevant AutoType is 1, don't you know what the
MajorCategory is? Or is AutoType stored in PreOwned? If so, then
you could join the two tables.

Something looks wrong here -- I can't think of a case where it
makes sense to use a DLookup in a query, myself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/1/2003

[JustMe]

Hi Tom ...

Thanks for your reply.

I've tried similiar queries in a several different databases. Regardless of
which database, I get the same NO FUNCTION error when using the DLOOKUP
function within the query.

SELECT "Test" as MajorCategory ... This query works fine.

--Terry
"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message
news:ld******** *************** *********@4ax.c om...

On Fri, 24 Oct 2003 12:14:10 -0600, "JustMe" <te******@saskt el.net>
wrote:

Very curious. Can you repeat this with a similar query in the
Northwind sample database?

I'm assuming this works fine:
SELECT "test" AS
MajorCategory, PreOwned.PaintC olor
FROM PreOwned;

-Tom.

I don't know if this has anything to do with AccessXP running on Terminal
Services with Access97/2000 also installed, but here is one example of a
query that does not work any longer:

SELECT DLookUp("MajorC ategory","AutoT ypes","[autoType]='1'") AS
MajorCategor y, PreOwned.PaintC olor
FROM PreOwned;

When I try to run the above query I get the following error:

UNKNOWN FUNCTION NAME

If I execute the exact same dlookup from the immediate window, the proper
data is returned with no error.

Any suggestions? I have very little hair left on my head after this!!!

Thanks,
--Terry

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/1/2003

[JustMe]

I tried all variations. Unfortunately this isn't the answer.

Thanks for the help anyway Wayne.

--Terry
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:hb******** *********@newss vr33.news.prodi gy.com...

Let's go back a step, you said you surrounded ALL the field with brackets as a try. It doesn't like brackets around the table's name, did you bracket that one also?
--
Wayne Morgan
Microsoft Access MVP
"JustMe" <te******@saskt el.net> wrote in message

news:vp******** ****@corp.super news.com...

Hi Wayne ...

I've already tried this. All references are identical to my other machine. None are missing.

Any other suggestions?
--Terry
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:O%******** **********@news svr16.news.prod igy.com...

If it works fine on other machines then it sounds like a References

problem. Open a code

window (Alt+F11) and go to Tools|Reference s. Compare them with a machine

that does work.

Also, make sure none of them are listed as MISSING.

--
Wayne Morgan
Microsoft Access MVP
"JustMe" <te******@saskt el.net> wrote in message

news:vp******** ****@corp.super news.com...

> By the way, the exact same query works fine in Access 97 on the same
> machine.
>
> Terry
> "JustMe" <te******@saskt el.net> wrote in message
> news:vp******** ****@corp.super news.com...
> > Hi Wayne ...
> >
> > Thanks for your reply. I tried surrounding ALL the fields with

brackets

> > with no success. I still get the UNKNOWN FUNCTION NAME error.
> >
> > Any other suggestions? Anyone?
> >
> > The same query works fine on other machines running AccessXP.

I've tried

> > uninstalling/reinstalling XP. I've tried everything I can think

of. > >
> > Help!
> >
> > "Wayne Morgan" <co************ *************** @hotmail.com> wrote in > message
> > news:a8******** ***********@new ssvr33.news.pro digy.com...
> > > Even without a space in the name, I think it wants brackets around the

> > field name.
> > >
> > > DLookUp("[MajorCategory]", "Auto Types", [auto Type]='1'")
> > >
> > > --
> > > Wayne Morgan
> > > Microsoft Access MVP
> > >
> > >
> > > "JustMe" <te******@saskt el.net> wrote in message
> > news:vp******** ****@corp.super news.com...
> > > > I don't know if this has anything to do with AccessXP running

on > > Terminal
> > > > Services with Access97/2000 also installed, but here is one

example of

> a
> > > > query that does not work any longer:
> > > >
> > > > SELECT DLookUp("MajorC ategory","AutoT ypes","[autoType]='1'") AS > > > > MajorCategory, PreOwned.PaintC olor
> > > > FROM PreOwned;
> > > >
> > > > When I try to run the above query I get the following error:
> > > >
> > > > UNKNOWN FUNCTION NAME
> > > >
> > > > If I execute the exact same dlookup from the immediate window, the > > proper
> > > > data is returned with no error.
> > > >
> > > > Any suggestions? I have very little hair left on my head after > this!!!
> > > >
> > > > Thanks,
> > > > --Terry
> > > >
> > > >
> > > >
> > > > ---
> > > > Outgoing mail is certified Virus Free.
> > > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > > Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003 > > > >
> > > >
> > >
> > >
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003
>
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/2/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/1/2003

[JustMe]

I'm going to install SP8. Stay tuned ... I'll inform everyone of the
results.

--Terry

"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message
news:om******** *************** *********@4ax.c om...

On Sat, 25 Oct 2003 21:09:57 GMT, dX********@bway .net.invalid (David
W. Fenton) wrote:

SP8 just came out. I quickly read the what's new section, and it
seemed to fix some SP7 bugs.
-Tom.

no****@nospam. nospam (Steve Jorgensen) wrote in
<r2*********** *************** ******@4ax.com> :

I just recently was called in to fix a similar problem at a client
site with Access 2000. It seems, machines with some of the newest
updates from Microsoft can no longer execute certain built-in
functions from within queries or calculated controls (things that
use the "Expression Service"). In my case, the original author had
been using the Dir$ function from a query.

Like you, I tried all the usual fixes, going so far as to try it
in a new, blank database created on the problem machine. Finally,
I ended up simply deciding this was a "feature" of a new update
from Microsoft, and working around it by writing my own
user-defined function to call Dir$. I suspect your situation may
be the same issue.

Would that be Jet 4 SP7? One of the things it does is do something
to the security of Jet that disallows certain kinds of VBA calls
from SQL. At least, that was my understanding when it initially
came out.

Has *anyone* found SP7 to be a good thing? I know it causes
problems with replication, for one, and many people have rolled
back to SP6.

Is there anythign that SP7 fixes that is essential?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/1/2003

[JustMe]

Tom, are you referring to MDAC2.8? Or JET4.0 SP8? I can't seem to find
Jet4.0SP8 if that's what you're referring to.

--Terry

"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message
news:om******** *************** *********@4ax.c om...

On Sat, 25 Oct 2003 21:09:57 GMT, dX********@bway .net.invalid (David
W. Fenton) wrote:

SP8 just came out. I quickly read the what's new section, and it
seemed to fix some SP7 bugs.
-Tom.

no****@nospam. nospam (Steve Jorgensen) wrote in
<r2*********** *************** ******@4ax.com> :

I just recently was called in to fix a similar problem at a client
site with Access 2000. It seems, machines with some of the newest
updates from Microsoft can no longer execute certain built-in
functions from within queries or calculated controls (things that
use the "Expression Service"). In my case, the original author had
been using the Dir$ function from a query.

Like you, I tried all the usual fixes, going so far as to try it
in a new, blank database created on the problem machine. Finally,
I ended up simply deciding this was a "feature" of a new update
from Microsoft, and working around it by writing my own
user-defined function to call Dir$. I suspect your situation may
be the same issue.

Would that be Jet 4 SP7? One of the things it does is do something
to the security of Jet that disallows certain kinds of VBA calls
from SQL. At least, that was my understanding when it initially
came out.

Has *anyone* found SP7 to be a good thing? I know it causes
problems with replication, for one, and many people have rolled
back to SP6.

Is there anythign that SP7 fixes that is essential?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/1/2003