Dir function in 2007

[Hadi]

Hello Experts,

I have a query that finds my excel monthly reports and stores the path as a
hyperlink in the table. I am using a DIR function that has been working for
me in 2000. Since our IT guys upgraded to office 2007 and everytime I run
the query I get a message saying undefined function "DIR" in expression. I
am not sure if that's a 2007 issue or what. I did not change anything in my
database design or code. here is the code I use for the DIR function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] & "\" &
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\" & "*" &
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you can get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

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)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

 

[Hadi]

No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that the
document was developed and match the selected references?

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you can get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

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)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the path as
a
hyperlink in the table. I am using a DIR function that has been working
for
me in 2000. Since our IT guys upgraded to office 2007 and everytime I run
the query I get a message saying undefined function "DIR" in expression.
I
am not sure if that's a 2007 issue or what. I did not change anything in
my
database design or code. here is the code I use for the DIR function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] & "\" &
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" &
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\" & "*" &
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>

No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that the
document was developed and match the selected references?

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you can get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file version
of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

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)

For far more than you could ever want to know about this problem, check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the path
as
a
hyperlink in the table. I am using a DIR function that has been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and everytime I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change anything
in
my
database design or code. here is the code I use for the DIR function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] & "\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\" & "*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Hadi]

I went to another machine that has access 2000 and the query runs fine. The
references on that machine are the same except for one. The machine with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>

No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that the
document was developed and match the selected references?

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you can get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file version
of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

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)

For far more than you could ever want to know about this problem, check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the path
as
a
hyperlink in the table. I am using a DIR function that has been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and everytime I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change anything
in
my
database design or code. here is the code I use for the DIR function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] & "\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\" & "*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

Still sounds like a References problem on the PC that is using ACCESS 2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>

I went to another machine that has access 2000 and the query runs fine.
The
references on that machine are the same except for one. The machine with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>

No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you can
get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file
version
of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

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)

For far more than you could ever want to know about this problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the
path
as
a
hyperlink in the table. I am using a DIR function that has been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and everytime
I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change
anything
in
my
database design or code. here is the code I use for the DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] &
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\" &
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

Additionally, because you're using ACCESS 2007, is the database file in a
Trusted Folder on your PC (you need to identify Trusted Folder in ACCESS
2007)? And have you enabled / trusted to run all macro actions and VBA code?
--

Ken Snell
<MS ACCESS MVP>

Still sounds like a References problem on the PC that is using ACCESS 2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>

I went to another machine that has access 2000 and the query runs fine.
The
references on that machine are the same except for one. The machine with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you can
get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file
version
of
certain files between the machine where the application was
developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

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)

For far more than you could ever want to know about this problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the
path
as
a
hyperlink in the table. I am using a DIR function that has been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and
everytime I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change
anything
in
my
database design or code. here is the code I use for the DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] &
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\" &
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Hadi]

I am trying to point the reference to the 9.0 library but not having too much
success.

in the Douglas posting, what else I need to do other what you've already
told me which basically refreshing the reference list

Still sounds like a References problem on the PC that is using ACCESS 2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>

I went to another machine that has access 2000 and the query runs fine.
The
references on that machine are the same except for one. The machine with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you can
get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file
version
of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

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)

For far more than you could ever want to know about this problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the
path
as
a
hyperlink in the table. I am using a DIR function that has been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and everytime
I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change
anything
in
my
database design or code. here is the code I use for the DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] &
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\" &
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will only use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007 database
file? Did you try unselecting one, closing the database file, opening the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>

I am trying to point the reference to the 9.0 library but not having too
much
success.

in the Douglas posting, what else I need to do other what you've already
told me which basically refreshing the reference list

Still sounds like a References problem on the PC that is using ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>

I went to another machine that has access 2000 and the query runs fine.
The
references on that machine are the same except for one. The machine
with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you
can
get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file
version
of
certain files between the machine where the application was
developed,
and
where it's being run (or the file missing completely from the
target
machine). Such differences are common when new software is
installed.

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)

For far more than you could ever want to know about this problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the
path
as
a
hyperlink in the table. I am using a DIR function that has been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and
everytime
I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change
anything
in
my
database design or code. here is the code I use for the DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Hadi]

Several times. I looked at the object browser window and I can see the dir
function.
I've tried this wish several machines that run Access 2007. some are on
Vista and some on XP. the result is the same

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will only use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007 database
file? Did you try unselecting one, closing the database file, opening the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>

I am trying to point the reference to the 9.0 library but not having too
much
success.

in the Douglas posting, what else I need to do other what you've already
told me which basically refreshing the reference list

Still sounds like a References problem on the PC that is using ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>




I went to another machine that has access 2000 and the query runs fine.
The
references on that machine are the same except for one. The machine
with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems a bit
complicated. would it help if I can find the original machine that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you
can
get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file
version
of
certain files between the machine where the application was
developed,
and
where it's being run (or the file missing completely from the
target
machine). Such differences are common when new software is
installed.

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)

For far more than you could ever want to know about this problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores the
path
as
a
hyperlink in the table. I am using a DIR function that has been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and
everytime
I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change
anything
in
my
database design or code. here is the code I use for the DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

Did you try the other items I suggested in an earlier reply:

Additionally, because you're using ACCESS 2007, is the database file in a
Trusted Folder on your PC (you need to identify Trusted Folder in ACCESS
2007)? And have you enabled / trusted to run all macro actions and VBA code?
--

Ken Snell
<MS ACCESS MVP>

Several times. I looked at the object browser window and I can see the dir
function.
I've tried this wish several machines that run Access 2007. some are on
Vista and some on XP. the result is the same

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will only use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007
database
file? Did you try unselecting one, closing the database file, opening the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>

I am trying to point the reference to the 9.0 library but not having too
much
success.

in the Douglas posting, what else I need to do other what you've
already
told me which basically refreshing the reference list



:

Still sounds like a References problem on the PC that is using ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>




I went to another machine that has access 2000 and the query runs
fine.
The
references on that machine are the same except for one. The machine
with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems a
bit
complicated. would it help if I can find the original machine
that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you
can
get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file
version
of
certain files between the machine where the application was
developed,
and
where it's being run (or the file missing completely from the
target
machine). Such differences are common when new software is
installed.

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)

For far more than you could ever want to know about this
problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores
the
path
as
a
hyperlink in the table. I am using a DIR function that has
been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and
everytime
I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change
anything
in
my
database design or code. here is the code I use for the DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" &
[PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" &
"\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Hadi]

Yes I have. I tried the the folder setting idea but also didnt work. same msg
over and over.

Did you try the other items I suggested in an earlier reply:

Additionally, because you're using ACCESS 2007, is the database file in a
Trusted Folder on your PC (you need to identify Trusted Folder in ACCESS
2007)? And have you enabled / trusted to run all macro actions and VBA code?
--

Ken Snell
<MS ACCESS MVP>

Several times. I looked at the object browser window and I can see the dir
function.
I've tried this wish several machines that run Access 2007. some are on
Vista and some on XP. the result is the same

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will only use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007
database
file? Did you try unselecting one, closing the database file, opening the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>


I am trying to point the reference to the 9.0 library but not having too
much
success.

in the Douglas posting, what else I need to do other what you've
already
told me which basically refreshing the reference list



:

Still sounds like a References problem on the PC that is using ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>




I went to another machine that has access 2000 and the query runs
fine.
The
references on that machine are the same except for one. The machine
with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems a
bit
complicated. would it help if I can find the original machine
that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele, MVP):

If any of the database's references are messed up on a PC -- you
can
get
that error.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file
version
of
certain files between the machine where the application was
developed,
and
where it's being run (or the file missing completely from the
target
machine). Such differences are common when new software is
installed.

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)

For far more than you could ever want to know about this
problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and stores
the
path
as
a
hyperlink in the table. I am using a DIR function that has
been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and
everytime
I
run
the query I get a message saying undefined function "DIR" in
expression.
I
am not sure if that's a 2007 issue or what. I did not change
anything
in
my
database design or code. here is the code I use for the DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\" &
[PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" &
"\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

Try importing the database's objects into a new .mdb file and then try the
query.

--

Ken Snell
<MS ACCESS MVP>

Yes I have. I tried the the folder setting idea but also didnt work. same
msg
over and over.

Did you try the other items I suggested in an earlier reply:

Additionally, because you're using ACCESS 2007, is the database file in a
Trusted Folder on your PC (you need to identify Trusted Folder in ACCESS
2007)? And have you enabled / trusted to run all macro actions and VBA
code?
--

Ken Snell
<MS ACCESS MVP>

Several times. I looked at the object browser window and I can see the
dir
function.
I've tried this wish several machines that run Access 2007. some are on
Vista and some on XP. the result is the same

:

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will only
use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007
database
file? Did you try unselecting one, closing the database file, opening
the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>


I am trying to point the reference to the 9.0 library but not having
too
much
success.

in the Douglas posting, what else I need to do other what you've
already
told me which basically refreshing the reference list



:

Still sounds like a References problem on the PC that is using
ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>




I went to another machine that has access 2000 and the query runs
fine.
The
references on that machine are the same except for one. The
machine
with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems
a
bit
complicated. would it help if I can find the original machine
that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele,
MVP):

If any of the database's references are messed up on a PC --
you
can
get
that error.

Try following these steps (from a post by Douglas Steele,
MVP):

This can be caused by differences in either the location or
file
version
of
certain files between the machine where the application was
developed,
and
where it's being run (or the file missing completely from the
target
machine). Such differences are common when new software is
installed.

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)

For far more than you could ever want to know about this
problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and
stores
the
path
as
a
hyperlink in the table. I am using a DIR function that has
been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and
everytime
I
run
the query I get a message saying undefined function "DIR"
in
expression.
I
am not sure if that's a 2007 issue or what. I did not
change
anything
in
my
database design or code. here is the code I use for the
DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\"
&
[PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007"
&
"\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Hadi]

Not working either. Is their a similar function that would do the same job.

There got to be a solution for this.

I appreciate your time

Try importing the database's objects into a new .mdb file and then try the
query.

--

Ken Snell
<MS ACCESS MVP>

Yes I have. I tried the the folder setting idea but also didnt work. same
msg
over and over.

Did you try the other items I suggested in an earlier reply:

Additionally, because you're using ACCESS 2007, is the database file in a
Trusted Folder on your PC (you need to identify Trusted Folder in ACCESS
2007)? And have you enabled / trusted to run all macro actions and VBA
code?
--

Ken Snell
<MS ACCESS MVP>


Several times. I looked at the object browser window and I can see the
dir
function.
I've tried this wish several machines that run Access 2007. some are on
Vista and some on XP. the result is the same

:

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will only
use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007
database
file? Did you try unselecting one, closing the database file, opening
the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>


I am trying to point the reference to the 9.0 library but not having
too
much
success.

in the Douglas posting, what else I need to do other what you've
already
told me which basically refreshing the reference list



:

Still sounds like a References problem on the PC that is using
ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>




I went to another machine that has access 2000 and the query runs
fine.
The
references on that machine are the same except for one. The
machine
with
access 2000 has MS Access 9.0 library and mine has 12.0 library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it seems
a
bit
complicated. would it help if I can find the original machine
that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele,
MVP):

If any of the database's references are messed up on a PC --
you
can
get
that error.

Try following these steps (from a post by Douglas Steele,
MVP):

This can be caused by differences in either the location or
file
version
of
certain files between the machine where the application was
developed,
and
where it's being run (or the file missing completely from the
target
machine). Such differences are common when new software is
installed.

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)

For far more than you could ever want to know about this
problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and
stores
the
path
as
a
hyperlink in the table. I am using a DIR function that has
been
working
for
me in 2000. Since our IT guys upgraded to office 2007 and
everytime
I
run
the query I get a message saying undefined function "DIR"
in
expression.
I
am not sure if that's a 2007 issue or what. I did not
change
anything
in
my
database design or code. here is the code I use for the
DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY REPORTS\"
&
[PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") & Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007"
&
"\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

You could try creating a user-defined function (a public function in a
regular module - note: the name of the regular module must be different than
the function's name) that would return the value you want. Perhaps that will
be a workaround (note: Dir should work in the query. I've asked some other
MVPs for ideas about what might be going wrong).


Public Function GetMyFileName(varPM as Variant, _
varProjectID As Variant) As String
Dim strTemp As String
strTemp = "S:\!DISTRIBUTION SYSTEM PM TEAM\" & _
"MONTHLY REPORTS\" & varPM & "\" & _
"2007\8-Augusty 2007" & "\" & "*" & varProjectID & "*"
GetMyFileName = Dir(strTemp)
Exit Function
End Function


Then change your expression in the query to this:

IIf(GetMyFileName([PM], [ProjectID])<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM
PM TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" & "\") &
GetMyFileName([PM], [ProjectID]),Null)


Above is an example. You can tweak however you need.

--

Ken Snell
<MS ACCESS MVP>

Not working either. Is their a similar function that would do the same
job.

There got to be a solution for this.

I appreciate your time

Try importing the database's objects into a new .mdb file and then try
the
query.

--

Ken Snell
<MS ACCESS MVP>

Yes I have. I tried the the folder setting idea but also didnt work.
same
msg
over and over.

:

Did you try the other items I suggested in an earlier reply:

Additionally, because you're using ACCESS 2007, is the database file
in a
Trusted Folder on your PC (you need to identify Trusted Folder in
ACCESS
2007)? And have you enabled / trusted to run all macro actions and VBA
code?
--

Ken Snell
<MS ACCESS MVP>


Several times. I looked at the object browser window and I can see
the
dir
function.
I've tried this wish several machines that run Access 2007. some are
on
Vista and some on XP. the result is the same

:

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will
only
use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007
database
file? Did you try unselecting one, closing the database file,
opening
the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>


I am trying to point the reference to the 9.0 library but not
having
too
much
success.

in the Douglas posting, what else I need to do other what you've
already
told me which basically refreshing the reference list



:

Still sounds like a References problem on the PC that is using
ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>




I went to another machine that has access 2000 and the query
runs
fine.
The
references on that machine are the same except for one. The
machine
with
access 2000 has MS Access 9.0 library and mine has 12.0
library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it
seems
a
bit
complicated. would it help if I can find the original
machine
that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele,
MVP):

If any of the database's references are messed up on a
PC --
you
can
get
that error.

Try following these steps (from a post by Douglas Steele,
MVP):

This can be caused by differences in either the location
or
file
version
of
certain files between the machine where the application
was
developed,
and
where it's being run (or the file missing completely from
the
target
machine). Such differences are common when new software is
installed.

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)

For far more than you could ever want to know about this
problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and
stores
the
path
as
a
hyperlink in the table. I am using a DIR function that
has
been
working
for
me in 2000. Since our IT guys upgraded to office 2007
and
everytime
I
run
the query I get a message saying undefined function
"DIR"
in
expression.
I
am not sure if that's a 2007 issue or what. I did not
change
anything
in
my
database design or code. here is the code I use for the
DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") &
Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August
2007"
&
"\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Ken Snell \(MVP\)]

Hadi -

Tom Wickerath (MVP) has suggested that the problem you're seeing is caused
by Sandbox mode -- a new security feature in ACCESS 2007 that is not in
2000.

See this KB article:
http://support.microsoft.com/?id=294698

A wrapper function is needed for sandbox mode -- so the suggestion I just
posted about using your own user-defined function should fix your problem.

--

Ken Snell
<MS ACCESS MVP>

You could try creating a user-defined function (a public function in a
regular module - note: the name of the regular module must be different
than the function's name) that would return the value you want. Perhaps
that will be a workaround (note: Dir should work in the query. I've asked
some other MVPs for ideas about what might be going wrong).


Public Function GetMyFileName(varPM as Variant, _
varProjectID As Variant) As String
Dim strTemp As String
strTemp = "S:\!DISTRIBUTION SYSTEM PM TEAM\" & _
"MONTHLY REPORTS\" & varPM & "\" & _
"2007\8-Augusty 2007" & "\" & "*" & varProjectID & "*"
GetMyFileName = Dir(strTemp)
Exit Function
End Function


Then change your expression in the query to this:

IIf(GetMyFileName([PM], [ProjectID])<>"",("Rpt_08-07#S:\!DISTRIBUTION
SYSTEM PM TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August 2007" &
"\") & GetMyFileName([PM], [ProjectID]),Null)


Above is an example. You can tweak however you need.

--

Ken Snell
<MS ACCESS MVP>

Not working either. Is their a similar function that would do the same
job.

There got to be a solution for this.

I appreciate your time

Try importing the database's objects into a new .mdb file and then try
the
query.

--

Ken Snell
<MS ACCESS MVP>


Yes I have. I tried the the folder setting idea but also didnt work.
same
msg
over and over.

:

Did you try the other items I suggested in an earlier reply:

Additionally, because you're using ACCESS 2007, is the database file
in a
Trusted Folder on your PC (you need to identify Trusted Folder in
ACCESS
2007)? And have you enabled / trusted to run all macro actions and
VBA
code?
--

Ken Snell
<MS ACCESS MVP>


Several times. I looked at the object browser window and I can see
the
dir
function.
I've tried this wish several machines that run Access 2007. some
are on
Vista and some on XP. the result is the same

:

You cannot point ACCESS 2007 to the ACCESS 9.0 library. It will
only
use
ACCESS 12.0 library.

Are none of the references marked as "MISSING" in your ACCESS 2007
database
file? Did you try unselecting one, closing the database file,
opening
the
file again, and reselecting that reference that you unselected?

--

Ken Snell
<MS ACCESS MVP>


I am trying to point the reference to the 9.0 library but not
having
too
much
success.

in the Douglas posting, what else I need to do other what you've
already
told me which basically refreshing the reference list



:

Still sounds like a References problem on the PC that is using
ACCESS
2007
(Access 12.0).

Which part of Douglas' instructions is giving you problems?
--

Ken Snell
<MS ACCESS MVP>




I went to another machine that has access 2000 and the query
runs
fine.
The
references on that machine are the same except for one. The
machine
with
access 2000 has MS Access 9.0 library and mine has 12.0
library.

:

That would be a good starting point.

--

Ken Snell
<MS ACCESS MVP>


No luck Ken. I am going thru the post by Douglas but it
seems
a
bit
complicated. would it help if I can find the original
machine
that
the
document was developed and match the selected references?

:

Sounds like a References problem.

Try following these steps (from a post by Douglas Steele,
MVP):

If any of the database's references are messed up on a
PC --
you
can
get
that error.

Try following these steps (from a post by Douglas Steele,
MVP):

This can be caused by differences in either the location
or
file
version
of
certain files between the machine where the application
was
developed,
and
where it's being run (or the file missing completely from
the
target
machine). Such differences are common when new software
is
installed.

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)

For far more than you could ever want to know about this
problem,
check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Hello Experts,

I have a query that finds my excel monthly reports and
stores
the
path
as
a
hyperlink in the table. I am using a DIR function that
has
been
working
for
me in 2000. Since our IT guys upgraded to office 2007
and
everytime
I
run
the query I get a message saying undefined function
"DIR"
in
expression.
I
am not sure if that's a 2007 issue or what. I did not
change
anything
in
my
database design or code. here is the code I use for
the
DIR
function

IIf(Dir("S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\"
&
[PM]
&
"\"
&
"2007\8-Augusty 2007" & "\" & "*" & [ProjectID] &
"*")<>"",("Rpt_08-07#S:\!DISTRIBUTION SYSTEM PM
TEAM\MONTHLY
REPORTS\"
&
[PM]
& "\" & "2007\8-August 2007" & "\") &
Dir("S:\!DISTRIBUTION
SYSTEM
PM
TEAM\MONTHLY REPORTS\" & [PM] & "\" & "2007\8-August
2007"
&
"\"
&
"*"
&
[ProjectID] & "*"),Null)

Thank you

 

[Tom Wickerath]

Hi Ken,

a new security feature in ACCESS 2007 that is not in
2000.

Actually, it's a security feature of JET 4, starting with service pack 3.
Hadi's PC was likely configured with Sandbox mode 0 or 2 previously, on PCs
where the query worked.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Snell \(MVP\)]

Yeah, my wording is a bit awkward.. I was just trying to compare 2000 and
2007 in terms of what would be different < g >.

 

[Hadi]

I am sorry I am not familiar with the term Sandbox mode. Is there a way to
change the settings you've mentioned about the Jet 4 or is the only way to do
it is to define the custom function?

IF yes, can you please provide me with some directin on how to go about
defining the custom function. Do I create a new module and paste the code
there or is it within the access file itself. Sorry i am weak on the
programming side of Access

Thanks alot

 

[Ken Snell \(MVP\)]

The Knowledge Base article, whose URL link I provided previously, tells you
how to make registry changes. But if you're not very comfortable with
editing the registry, I don't recommend that you do that.

As for the function suggestion I'd made, in the database window you'll see
Modules as an option for an object. You want to create a new module; name it
basFunctions. Then paste the VBA code that I provided for the function into
the opened window (Visual Basic Editor). Close the module window. Change the
query as I noted. All should work now.
--

Ken Snell
<MS ACCESS MVP>

I am sorry I am not familiar with the term Sandbox mode. Is there a way to
change the settings you've mentioned about the Jet 4 or is the only way to
do
it is to define the custom function?

IF yes, can you please provide me with some directin on how to go about
defining the custom function. Do I create a new module and paste the code
there or is it within the access file itself. Sorry i am weak on the
programming side of Access

Thanks alot