Create Hyperlink Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am building an Access database that will hyperlink to monthly reports for
about 200 projects. The monthly reports are in an excel format and stored on
a common drive on the network and I have about 3 months worth of reports that
I need to link the db to. so that’s about 600 hyperlinks that I have to
initially create and then 200 each month.

I have the tables, queries, and reports built and trying to find an
efficient way to insert the hyperlinks instead of clicking on each record and
pointing to the right file. All of the excel reports have a common naming
convention that starts with the project # and ends with the report’s month.

I am relativly new to access and dont know much about VBA code but pretty
sure there is a way and probably a simple one that will make this happen


Thank you in advance for you help
 
Most likely what you seek to do can be done via an update query if you can
provide a generic way for the query to "build" the desired hyperlink path
string. Does the table's record contain all the info that would allow you
build the EXCEL file's filename?
 
Thanks for the reply. basically the filename looks like this
123456_11111_MonYY

Where 12345 is the project ID or No. and the 11111 is the program no. that
the project comes under. The project name can be flexible to make it easier
on the query to find the files.

the table has the following fields (Project No, Project Name, Jan07,
Feb07,Mar07, Apr07,....., Dec07). obviously the Jan07 TO Dec07 are the
hyperlink fields. I have a few other tables with the Project Managers' info,
program info, etc.

thanks agian
 
It appears that you can get the data you need from the table's records.

Is the field where you want to put the filename a hyperlink field, or a text
field?

Assuming that it's a text field, make a copy of your database as a backup,
then create and run this update query in the database (replace generic names
with real table and field names, and with real string for path to folder
where files are kept):

UPDATE TableName
SET Jan07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07",
Feb07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07",
Mar07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Mar07",
Apr07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Apr07",
May07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "May07",
Jun07LinkTextField =
[Project No] & "_" & [Project Name] &
"_" & "Jun07",
Jul07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jul07",
Aug07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Aug07",
Sep07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Sep07",
Oct07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Oct07",
Nov07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Nov07",
Dec07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Dec07";


Your table's structure is not normalized, by the way, when you have a
different field for each month/year combination.


If the field is a hyperlink, then you'll probably need to surround the path
& filename info with # characters; e.g.,

UPDATE TableName
SET Jan07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07#",
Feb07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07#",
(etc.)
 
Ken, this is great. it works perfectly.

Now, if I want to orgnize my xls files on the network drive by the Project
Manager. how would the code changes. do I have to write a bunch of IF
statements where if PM field in the table = xyz then look in folder xyz and
so on. but the if statement will be pretty long since i have to write 18
nested statements for my 18 PMs???

Ken Snell (MVP) said:
It appears that you can get the data you need from the table's records.

Is the field where you want to put the filename a hyperlink field, or a text
field?

Assuming that it's a text field, make a copy of your database as a backup,
then create and run this update query in the database (replace generic names
with real table and field names, and with real string for path to folder
where files are kept):

UPDATE TableName
SET Jan07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07",
Feb07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07",
Mar07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Mar07",
Apr07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Apr07",
May07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "May07",
Jun07LinkTextField =
[Project No] & "_" & [Project Name] &
"_" & "Jun07",
Jul07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jul07",
Aug07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Aug07",
Sep07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Sep07",
Oct07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Oct07",
Nov07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Nov07",
Dec07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Dec07";


Your table's structure is not normalized, by the way, when you have a
different field for each month/year combination.


If the field is a hyperlink, then you'll probably need to surround the path
& filename info with # characters; e.g.,

UPDATE TableName
SET Jan07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07#",
Feb07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07#",
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
Thanks for the reply. basically the filename looks like this
123456_11111_MonYY

Where 12345 is the project ID or No. and the 11111 is the program no. that
the project comes under. The project name can be flexible to make it
easier
on the query to find the files.

the table has the following fields (Project No, Project Name, Jan07,
Feb07,Mar07, Apr07,....., Dec07). obviously the Jan07 TO Dec07 are the
hyperlink fields. I have a few other tables with the Project Managers'
info,
program info, etc.

thanks agian
Click to expand...
Click to expand...
 
also, anyway the code can check to see if the excel file exists or not and if
does not then the hyperlink field stays blank. that way I know who's updating
and who's not everymonth.

I appreciate it

Ken Snell (MVP) said:
It appears that you can get the data you need from the table's records.

Is the field where you want to put the filename a hyperlink field, or a text
field?

Assuming that it's a text field, make a copy of your database as a backup,
then create and run this update query in the database (replace generic names
with real table and field names, and with real string for path to folder
where files are kept):

UPDATE TableName
SET Jan07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07",
Feb07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07",
Mar07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Mar07",
Apr07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Apr07",
May07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "May07",
Jun07LinkTextField =
[Project No] & "_" & [Project Name] &
"_" & "Jun07",
Jul07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jul07",
Aug07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Aug07",
Sep07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Sep07",
Oct07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Oct07",
Nov07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Nov07",
Dec07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Dec07";


Your table's structure is not normalized, by the way, when you have a
different field for each month/year combination.


If the field is a hyperlink, then you'll probably need to surround the path
& filename info with # characters; e.g.,

UPDATE TableName
SET Jan07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07#",
Feb07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07#",
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
Thanks for the reply. basically the filename looks like this
123456_11111_MonYY

Where 12345 is the project ID or No. and the 11111 is the program no. that
the project comes under. The project name can be flexible to make it
easier
on the query to find the files.

the table has the following fields (Project No, Project Name, Jan07,
Feb07,Mar07, Apr07,....., Dec07). obviously the Jan07 TO Dec07 are the
hyperlink fields. I have a few other tables with the Project Managers'
info,
program info, etc.

thanks agian
Click to expand...
Click to expand...
 
Using my first example query (and just using a few lines from it to give you
an idea for how to do what you seek):

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07", Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07", Null),

etc.etc.

--

Ken Snell
<MS ACCESS MVP>




Hadi said:
Ken, this is great. it works perfectly.

Now, if I want to orgnize my xls files on the network drive by the Project
Manager. how would the code changes. do I have to write a bunch of IF
statements where if PM field in the table = xyz then look in folder xyz
and
so on. but the if statement will be pretty long since i have to write 18
nested statements for my 18 PMs???

Ken Snell (MVP) said:
It appears that you can get the data you need from the table's records.

Is the field where you want to put the filename a hyperlink field, or a
text
field?

Assuming that it's a text field, make a copy of your database as a
backup,
then create and run this update query in the database (replace generic
names
with real table and field names, and with real string for path to folder
where files are kept):

UPDATE TableName
SET Jan07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07",
Feb07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07",
Mar07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Mar07",
Apr07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Apr07",
May07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "May07",
Jun07LinkTextField =
[Project No] & "_" & [Project Name] &
"_" & "Jun07",
Jul07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jul07",
Aug07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Aug07",
Sep07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Sep07",
Oct07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Oct07",
Nov07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Nov07",
Dec07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Dec07";


Your table's structure is not normalized, by the way, when you have a
different field for each month/year combination.


If the field is a hyperlink, then you'll probably need to surround the
path
& filename info with # characters; e.g.,

UPDATE TableName
SET Jan07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07#",
Feb07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07#",
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
Thanks for the reply. basically the filename looks like this
123456_11111_MonYY

Where 12345 is the project ID or No. and the 11111 is the program no.
that
the project comes under. The project name can be flexible to make it
easier
on the query to find the files.

the table has the following fields (Project No, Project Name, Jan07,
Feb07,Mar07, Apr07,....., Dec07). obviously the Jan07 TO Dec07 are the
hyperlink fields. I have a few other tables with the Project Managers'
info,
program info, etc.

thanks agian

:

Most likely what you seek to do can be done via an update query if you
can
provide a generic way for the query to "build" the desired hyperlink
path
string. Does the table's record contain all the info that would allow
you
build the EXCEL file's filename?

--

Ken Snell
<MS ACCESS MVP>

I am building an Access database that will hyperlink to monthly
reports
for
about 200 projects. The monthly reports are in an excel format and
stored
on
a common drive on the network and I have about 3 months worth of
reports
that
I need to link the db to. so that's about 600 hyperlinks that I have
to
initially create and then 200 each month.

I have the tables, queries, and reports built and trying to find an
efficient way to insert the hyperlinks instead of clicking on each
record
and
pointing to the right file. All of the excel reports have a common
naming
convention that starts with the project # and ends with the report's
month.

I am relativly new to access and dont know much about VBA code but
pretty
sure there is a way and probably a simple one that will make this
happen


Thank you in advance for you help
Click to expand...
Click to expand...
Click to expand...
 
This works like a champ. amazing

You mentioned that my table structure was not normalized. what do you
recommend?

Ken Snell (MVP) said:
Using my first example query (and just using a few lines from it to give you
an idea for how to do what you seek):

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07", Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07", Null),

etc.etc.

--

Ken Snell
<MS ACCESS MVP>




Hadi said:
Ken, this is great. it works perfectly.

Now, if I want to orgnize my xls files on the network drive by the Project
Manager. how would the code changes. do I have to write a bunch of IF
statements where if PM field in the table = xyz then look in folder xyz
and
so on. but the if statement will be pretty long since i have to write 18
nested statements for my 18 PMs???

Ken Snell (MVP) said:
It appears that you can get the data you need from the table's records.

Is the field where you want to put the filename a hyperlink field, or a
text
field?

Assuming that it's a text field, make a copy of your database as a
backup,
then create and run this update query in the database (replace generic
names
with real table and field names, and with real string for path to folder
where files are kept):

UPDATE TableName
SET Jan07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07",
Feb07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07",
Mar07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Mar07",
Apr07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Apr07",
May07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "May07",
Jun07LinkTextField =
[Project No] & "_" & [Project Name] &
"_" & "Jun07",
Jul07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jul07",
Aug07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Aug07",
Sep07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Sep07",
Oct07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Oct07",
Nov07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Nov07",
Dec07LinkTextField =
"PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Dec07";


Your table's structure is not normalized, by the way, when you have a
different field for each month/year combination.


If the field is a hyperlink, then you'll probably need to surround the
path
& filename info with # characters; e.g.,

UPDATE TableName
SET Jan07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Jan07#",
Feb07LinkTextField =
"#PathToFolder\" & [Project No] & "_" & [Project Name] &
"_" & "Feb07#",
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Thanks for the reply. basically the filename looks like this
123456_11111_MonYY

Where 12345 is the project ID or No. and the 11111 is the program no.
that
the project comes under. The project name can be flexible to make it
easier
on the query to find the files.

the table has the following fields (Project No, Project Name, Jan07,
Feb07,Mar07, Apr07,....., Dec07). obviously the Jan07 TO Dec07 are the
hyperlink fields. I have a few other tables with the Project Managers'
info,
program info, etc.

thanks agian

:

Most likely what you seek to do can be done via an update query if you
can
provide a generic way for the query to "build" the desired hyperlink
path
string. Does the table's record contain all the info that would allow
you
build the EXCEL file's filename?

--

Ken Snell
<MS ACCESS MVP>

I am building an Access database that will hyperlink to monthly
reports
for
about 200 projects. The monthly reports are in an excel format and
stored
on
a common drive on the network and I have about 3 months worth of
reports
that
I need to link the db to. so that's about 600 hyperlinks that I have
to
initially create and then 200 each month.

I have the tables, queries, and reports built and trying to find an
efficient way to insert the hyperlinks instead of clicking on each
record
and
pointing to the right file. All of the excel reports have a common
naming
convention that starts with the project # and ends with the report's
month.

I am relativly new to access and dont know much about VBA code but
pretty
sure there is a way and probably a simple one that will make this
happen


Thank you in advance for you help
Click to expand...
Click to expand...
Click to expand...
 
Let me give an example. Instead of having a separate field for each
month/year combination, you should have a separate table that has a separate
record for each unique combination of Program Manager, Month, and Year. The
Program Manager field would be related back to your current table's Program
Manager field so that the data are related. Then you could use a continuous
forms view to show the related records and allow user to scroll to desired
record. This way, you never have to add more fields for each new month/year
combination; just a new record.

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
This works like a champ. amazing

You mentioned that my table structure was not normalized. what do you
recommend?

:
Click to expand...

< snipped >
 
the team I work with want to add a short descritption (like a project name)
after the 07 part of the file name. anyway I can have the code ignore that
part and just check up to the 07 part? I have a project name field in my
tables but I know that it will be hard to keep the name consistent with one
on the table. I think it will give me problems. I just would like for the
code to ignore that part all together.

very much appreciated
 
Not understanding exactly what you want to do? Which code needs to ignore
the project name? We didn't discuss any code in this post so far?
 
Sorry I just meant the little IF statement you helped me develop. I guess
what am looking for a code or sth to search the (lets say) March 2007 folder
on the network drive and fills the table with the report's path based on the
project id only. That way the users can add a discription to the end of the
excel files name if they want to without affecting me. They can have some
flexibility in the way they name the excel file. So as long as the file name
starts with the project id. I might not even need the Mon_YY part since the
excel files will be stored on a seperate folder each month.

I hope am being some what clear
 
Oh OK --

It's possible to slightly modify the IIf expression to ignore the Project
Name info in the file name when deciding if the file exists (this is done by
adding a wild card character to the Dir function's argument). But you'll
need to give me some specific examples of what the actual path/filename
string needs to be in these cases because I am not sure about what result
you want to be put in the field by the query. Give examples of what file
would exist in a folder (show the whole path) when a file should be "found",
and also give examples of what would exist/not exist when a file should not
be found.
 
Ok let me try. here is an example of a location of one report for a Project
Manager named Francisco Becerra for one of his projects for the month of
March:

S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\Becerra,Francisco\2007\3-March 2007\103500_15377_Mar_07.xls. The
code I have now would work for the way the xls file is named BUT if Francisco
wants to add the project name after the 07 part which will make it
103500_15377_Mar_07_Box Springs Feeder.xls. How would we modify the code to
be able to return the full path of the file to the hyperlink field so I can
open it. let me try to summarize

1- my table has the following fields Project ID,Program ID, Project
Manager(PM),Jan07,Feb07....
2-Each Project ID record should have a link to the xls monthly report
located on the network drive (path as shown above)
3-I want the query to find the xls monthly reports posted on the S: drive
and returns the path to the Hyperlink fields in the table so once i click on
it, it opens the report.
4-The If statement I have now will take care of all of the above assuming
that the xls name is [Project ID]_[Program ID]_Mon_YY.xls but if somebody
adds something after the YY (as shown above) then the code i have now will
not work.
5-I was hoping that the query will find these reports based the Project ID
only so whether somebody names the excel file [Project ID].xls or [Project
ID]_[Program ID]_Mon_YY_ProjectName.xls and still saves it under "PM\Month"
folder then the query will still be able to find the report.
6-If the xls is found then the Hyperlink field for this month will store the
path. The display text can be sth short like "Click Report" or "Rpt_Mar07" or
sth like that.
7-If the xls file is missing then the hyperlink field for that month stays
blank so i know who's not updating his report
8-if you think that we need to write a macro code or sth like that please
let me know how to use it since am fairly new to Access


I hope this makes a little clear

very much appreciated

Hadi
 
Haven't tested this fully, but let's try this. Using my first example query
(and just using a few lines from it to give you an idea for how to do what
you seek):

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*", Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*", Null),

etc.etc.
 
Thanks Ken. I think we're half way there. the first part works which is the
test to find the file but the storing part is not working. when it stores the
path to the file it actually includes the "*" part. it looks like this
S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\Becerra,Francisco\2007\3-March 2007\103374_15377_Mar07*. It's not
reading the "*" as a wildcard character.

Ken Snell (MVP) said:
Haven't tested this fully, but let's try this. Using my first example query
(and just using a few lines from it to give you an idea for how to do what
you seek):

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*", Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*", Null),

etc.etc.

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
Ok let me try. here is an example of a location of one report for a
Project
Manager named Francisco Becerra for one of his projects for the month of
March:

S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\Becerra,Francisco\2007\3-March 2007\103500_15377_Mar_07.xls. The
code I have now would work for the way the xls file is named BUT if
Francisco
wants to add the project name after the 07 part which will make it
103500_15377_Mar_07_Box Springs Feeder.xls. How would we modify the code
to
be able to return the full path of the file to the hyperlink field so I
can
open it. let me try to summarize

1- my table has the following fields Project ID,Program ID, Project
Manager(PM),Jan07,Feb07....
2-Each Project ID record should have a link to the xls monthly report
located on the network drive (path as shown above)
3-I want the query to find the xls monthly reports posted on the S: drive
and returns the path to the Hyperlink fields in the table so once i click
on
it, it opens the report.
4-The If statement I have now will take care of all of the above assuming
that the xls name is [Project ID]_[Program ID]_Mon_YY.xls but if somebody
adds something after the YY (as shown above) then the code i have now will
not work.
5-I was hoping that the query will find these reports based the Project ID
only so whether somebody names the excel file [Project ID].xls or [Project
ID]_[Program ID]_Mon_YY_ProjectName.xls and still saves it under
"PM\Month"
folder then the query will still be able to find the report.
6-If the xls is found then the Hyperlink field for this month will store
the
path. The display text can be sth short like "Click Report" or "Rpt_Mar07"
or
sth like that.
7-If the xls file is missing then the hyperlink field for that month stays
blank so i know who's not updating his report
8-if you think that we need to write a macro code or sth like that please
let me know how to use it since am fairly new to Access


I hope this makes a little clear

very much appreciated

Hadi
Click to expand...
Click to expand...
 
Sorry - try this:

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*")<>"",
Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*"), Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*")<>"",
Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*"), Null),

etc.etc.

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
Thanks Ken. I think we're half way there. the first part works which is
the
test to find the file but the storing part is not working. when it stores
the
path to the file it actually includes the "*" part. it looks like this
S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\Becerra,Francisco\2007\3-March 2007\103374_15377_Mar07*. It's not
reading the "*" as a wildcard character.

Ken Snell (MVP) said:
Haven't tested this fully, but let's try this. Using my first example
query
(and just using a few lines from it to give you an idea for how to do
what
you seek):

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*", Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*", Null),

etc.etc.

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
Ok let me try. here is an example of a location of one report for a
Project
Manager named Francisco Becerra for one of his projects for the month
of
March:

S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\Becerra,Francisco\2007\3-March 2007\103500_15377_Mar_07.xls.
The
code I have now would work for the way the xls file is named BUT if
Francisco
wants to add the project name after the 07 part which will make it
103500_15377_Mar_07_Box Springs Feeder.xls. How would we modify the
code
to
be able to return the full path of the file to the hyperlink field so I
can
open it. let me try to summarize

1- my table has the following fields Project ID,Program ID, Project
Manager(PM),Jan07,Feb07....
2-Each Project ID record should have a link to the xls monthly report
located on the network drive (path as shown above)
3-I want the query to find the xls monthly reports posted on the S:
drive
and returns the path to the Hyperlink fields in the table so once i
click
on
it, it opens the report.
4-The If statement I have now will take care of all of the above
assuming
that the xls name is [Project ID]_[Program ID]_Mon_YY.xls but if
somebody
adds something after the YY (as shown above) then the code i have now
will
not work.
5-I was hoping that the query will find these reports based the Project
ID
only so whether somebody names the excel file [Project ID].xls or
[Project
ID]_[Program ID]_Mon_YY_ProjectName.xls and still saves it under
"PM\Month"
folder then the query will still be able to find the report.
6-If the xls is found then the Hyperlink field for this month will
store
the
path. The display text can be sth short like "Click Report" or
"Rpt_Mar07"
or
sth like that.
7-If the xls file is missing then the hyperlink field for that month
stays
blank so i know who's not updating his report
8-if you think that we need to write a macro code or sth like that
please
let me know how to use it since am fairly new to Access


I hope this makes a little clear

very much appreciated

Hadi
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Ken. I finally got but I had to slightly modify the IIF. The Dir
function only returns the file name not the whole path so I just concatenated
the Dir result (which is the file name) with the path up to that point. here
is my final result

IIf(Dir("MyNetworkPath\ "& [ProjectID] & "*")<>"",("Link#"MyNetworkPath") &
Dir("MyNetworkPath & "\" & [ProjectID] & "*"),Null)

Thank you much for your help. I appreciate it very much

Ken Snell (MVP) said:
Sorry - try this:

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*")<>"",
Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*"), Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*")<>"",
Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*"), Null),

etc.etc.

--

Ken Snell
<MS ACCESS MVP>


Hadi said:
Thanks Ken. I think we're half way there. the first part works which is
the
test to find the file but the storing part is not working. when it stores
the
path to the file it actually includes the "*" part. it looks like this
S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\Becerra,Francisco\2007\3-March 2007\103374_15377_Mar07*. It's not
reading the "*" as a wildcard character.

Ken Snell (MVP) said:
Haven't tested this fully, but let's try this. Using my first example
query
(and just using a few lines from it to give you an idea for how to do
what
you seek):

UPDATE TableName
SET Jan07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Jan07*", Null),
Feb07LinkTextField =
IIf(Dir("PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*")<>"",
"PathToFolder\" & [PM] & "\" &
[Project No] & "_" & [Project Name] &
"_" & "Feb07*", Null),

etc.etc.

--

Ken Snell
<MS ACCESS MVP>


Ok let me try. here is an example of a location of one report for a
Project
Manager named Francisco Becerra for one of his projects for the month
of
March:

S:\!DISTRIBUTION SYSTEM PM TEAM\MONTHLY
REPORTS\Becerra,Francisco\2007\3-March 2007\103500_15377_Mar_07.xls.
The
code I have now would work for the way the xls file is named BUT if
Francisco
wants to add the project name after the 07 part which will make it
103500_15377_Mar_07_Box Springs Feeder.xls. How would we modify the
code
to
be able to return the full path of the file to the hyperlink field so I
can
open it. let me try to summarize

1- my table has the following fields Project ID,Program ID, Project
Manager(PM),Jan07,Feb07....
2-Each Project ID record should have a link to the xls monthly report
located on the network drive (path as shown above)
3-I want the query to find the xls monthly reports posted on the S:
drive
and returns the path to the Hyperlink fields in the table so once i
click
on
it, it opens the report.
4-The If statement I have now will take care of all of the above
assuming
that the xls name is [Project ID]_[Program ID]_Mon_YY.xls but if
somebody
adds something after the YY (as shown above) then the code i have now
will
not work.
5-I was hoping that the query will find these reports based the Project
ID
only so whether somebody names the excel file [Project ID].xls or
[Project
ID]_[Program ID]_Mon_YY_ProjectName.xls and still saves it under
"PM\Month"
folder then the query will still be able to find the report.
6-If the xls is found then the Hyperlink field for this month will
store
the
path. The display text can be sth short like "Click Report" or
"Rpt_Mar07"
or
sth like that.
7-If the xls file is missing then the hyperlink field for that month
stays
blank so i know who's not updating his report
8-if you think that we need to write a macro code or sth like that
please
let me know how to use it since am fairly new to Access


I hope this makes a little clear

very much appreciated

Hadi
Click to expand...
Click to expand...
Click to expand...
 
Yep, sorry.... I was rushing in my reply between other work assignments, and
overlooked that fact. Glad you got it to work.
 
Back
Top