Filenames to a field

[OveB]

Hi,
I have a simple db with some 8000 records and an external folder with
filenames. I wish to get these names into a Pathfield in the main table so
that for each Record# the appropriate filename is fed in the new Pathfileld.
The files are images with names like xyz????.jpg where ???? i the same as
the Record#. Some records have no corresponding image. How to get this? I
would appreciate a detailded answer as my experience with Access is little.
Ove

 

[Ken Snell [MVP]]

Can you "create" the correct jpg file name from the information that is in
the database table? Or do you need to actually know the file name of the jpg
file? Let us know; how to approach this problem depends upon your reply.

 

[OveB]

Ken,
The filename is C:\Hembygd\Bilder\soh(NR).jpg where NR is the item# in the
NR field in Maintable. Example soh0123.jpg. The new field should come into
the same table and the .mdb is in the Hembygd folder./Ove

 

[Ken Snell [MVP]]

Then you should be able to run an update query to put the correct filename
into the table's field. Here is a generic SQL statement; replace my generic
names with your correct names:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh(" & [NR] & ").jpg";

--

Ken Snell
<MS ACCESS MVP>

 

[OveB]

Ken,
I have tried different things for several hours but I do not get it OK. I
always get a 0 for all records in the field.

I have from the database window opened a new query, chosen my table (comes
on line 2) and my new created empty field (on line 1). Then I chose Update
type of query from an icon to the left of ! in the toolbox and Update query
apperas in the name bar of the pane. I now put your expression on line 3 and
pushed ! in the toolbox for Run. As I felt a bit unsure about your line I
also tried to change it to ...soh" & [NR] & ".jpg" but without success.

Could you be kind enough to tell me what I have made wrong.

I also try to help other small museums nearby on a non-profit basis and one
of them has [NR] with plain integers, thus without leading zeros ( as 1, 2,
10 ...) but the images have names like soh0001.jpg. How to deal with this?

I should perhaps also mention that my aim is to be able to select all
records having an image and thus sort out those without, but I have not been
able to find any method as the only evidence is the content of a folder. But
it is, as I understand now, not necessary to get the actual file name in my
field, it should do with only Yes/No or 1/0. Perhaps is this easier?

Regards/Ove

Then you should be able to run an update query to put the correct filename
into the table's field. Here is a generic SQL statement; replace my
generic names with your correct names:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh(" & [NR] & ").jpg";

--

Ken Snell
<MS ACCESS MVP>

Ken,
The filename is C:\Hembygd\Bilder\soh(NR).jpg where NR is the item# in
the NR field in Maintable. Example soh0123.jpg. The new field should come
into the same table and the .mdb is in the Hembygd folder./Ove

 

[Ken Snell [MVP]]

Here is how you can pad a number with leading zeroes (in this case, a total
of four digits in the final number -- so 1 becomes 0001) by using Format
function:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh" & Format([NR], "0000") & ".jpg";


The way you would create this query using the design view for the query is
this:

1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on toolbar).
5. Put this expression in the "Update To:" cell under the field name that is
on the grid:
"C:\Hembygd\Bilder\soh" & Format([NR], "0000") & ".jpg"
6. Run the query.


Note that this query will not verify that a jpg file actually exists before
putting the path and filename into the field. If you want to put the
filename into the field *only if* the file actually exists, go to the next
empty column on the grid. Type this expression into the "Field:" cell in
that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
Then, in the "Where:" cell in this column, type this expression:
<>""
Then run the query. This will not insert a path and filename where the jpg
file does not exist.


--

Ken Snell
<MS ACCESS MVP>

Ken,
I have tried different things for several hours but I do not get it OK. I
always get a 0 for all records in the field.

I have from the database window opened a new query, chosen my table (comes
on line 2) and my new created empty field (on line 1). Then I chose Update
type of query from an icon to the left of ! in the toolbox and Update
query apperas in the name bar of the pane. I now put your expression on
line 3 and pushed ! in the toolbox for Run. As I felt a bit unsure about
your line I also tried to change it to ...soh" & [NR] & ".jpg" but
without success.

Could you be kind enough to tell me what I have made wrong.

I also try to help other small museums nearby on a non-profit basis and
one of them has [NR] with plain integers, thus without leading zeros ( as
1, 2, 10 ...) but the images have names like soh0001.jpg. How to deal with
this?

I should perhaps also mention that my aim is to be able to select all
records having an image and thus sort out those without, but I have not
been able to find any method as the only evidence is the content of a
folder. But it is, as I understand now, not necessary to get the actual
file name in my field, it should do with only Yes/No or 1/0. Perhaps is
this easier?

Regards/Ove

Then you should be able to run an update query to put the correct
filename into the table's field. Here is a generic SQL statement; replace
my generic names with your correct names:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh(" & [NR] & ").jpg";

--

Ken Snell
<MS ACCESS MVP>

Ken,
The filename is C:\Hembygd\Bilder\soh(NR).jpg where NR is the item# in
the NR field in Maintable. Example soh0123.jpg. The new field should
come into the same table and the .mdb is in the Hembygd folder./Ove


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
Can you "create" the correct jpg file name from the information that is
in the database table? Or do you need to actually know the file name of
the jpg file? Let us know; how to approach this problem depends upon
your reply.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a simple db with some 8000 records and an external folder with
filenames. I wish to get these names into a Pathfield in the main
table so
that for each Record# the appropriate filename is fed in the new
Pathfileld.
The files are images with names like xyz????.jpg where ???? i the same
as
the Record#. Some records have no corresponding image. How to get
this? I
would appreciate a detailded answer as my experience with Access is
little.
Ove

 

[OveB]

Ken,
Thanks! The first part now worked OK and I got all cells filled, although I
had to replace your , by ; after [NR]. Accees refused to accept the comma.
So to the Test. I have a Swedish version of Access 2002 and thus I do not
recognize all English words. By "grid" I assume you mean the Update Query
pane I used in the first part. I put your expression into the first cell in
the next empty column, named Field. Access did not accept a comma there
either but ; worked. Next line is titled Table in Swedish but I assume that
is where you wanted me to put the next expressioin <>"" The problem is that
Access denies to accept anything else than the listed tables. This means
that many of the cells in my JPGField contain path that do not exist.
Any thought about the reason?
Ove

Here is how you can pad a number with leading zeroes (in this case, a
total of four digits in the final number -- so 1 becomes 0001) by using
Format function:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg";


The way you would create this query using the design view for the query is
this:

1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on toolbar).
5. Put this expression in the "Update To:" cell under the field name that
is on the grid:
"C:\Hembygd\Bilder\soh" & Format([NR], "0000") & ".jpg"
6. Run the query.


Note that this query will not verify that a jpg file actually exists
before putting the path and filename into the field. If you want to put
the filename into the field *only if* the file actually exists, go to the
next empty column on the grid. Type this expression into the "Field:" cell
in that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
Then, in the "Where:" cell in this column, type this expression:
<>""
Then run the query. This will not insert a path and filename where the jpg
file does not exist.


--

Ken Snell
<MS ACCESS MVP>

Ken,
I have tried different things for several hours but I do not get it OK. I
always get a 0 for all records in the field.

I have from the database window opened a new query, chosen my table
(comes on line 2) and my new created empty field (on line 1). Then I
chose Update type of query from an icon to the left of ! in the toolbox
and Update query apperas in the name bar of the pane. I now put your
expression on line 3 and pushed ! in the toolbox for Run. As I felt a bit
unsure about your line I also tried to change it to ...soh" & [NR] &
".jpg" but without success.

Could you be kind enough to tell me what I have made wrong.

I also try to help other small museums nearby on a non-profit basis and
one of them has [NR] with plain integers, thus without leading zeros (
as 1, 2, 10 ...) but the images have names like soh0001.jpg. How to deal
with this?

I should perhaps also mention that my aim is to be able to select all
records having an image and thus sort out those without, but I have not
been able to find any method as the only evidence is the content of a
folder. But it is, as I understand now, not necessary to get the actual
file name in my field, it should do with only Yes/No or 1/0. Perhaps is
this easier?

Regards/Ove

Then you should be able to run an update query to put the correct
filename into the table's field. Here is a generic SQL statement;
replace my generic names with your correct names:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh(" & [NR] & ").jpg";

--

Ken Snell
<MS ACCESS MVP>




Ken,
The filename is C:\Hembygd\Bilder\soh(NR).jpg where NR is the item# in
the NR field in Maintable. Example soh0123.jpg. The new field should
come into the same table and the .mdb is in the Hembygd folder./Ove


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet Can you "create" the correct jpg file name from the information that
is in the database table? Or do you need to actually know the file
name of the jpg file? Let us know; how to approach this problem
depends upon your reply.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a simple db with some 8000 records and an external folder with
filenames. I wish to get these names into a Pathfield in the main
table so
that for each Record# the appropriate filename is fed in the new
Pathfileld.
The files are images with names like xyz????.jpg where ???? i the
same as
the Record#. Some records have no corresponding image. How to get
this? I
would appreciate a detailded answer as my experience with Access is
little.
Ove

 

[Ken Snell [MVP]]

Answers inline:

--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks! The first part now worked OK and I got all cells filled, although
I had to replace your , by ; after [NR]. Accees refused to accept the
comma.
So to the Test. I have a Swedish version of Access 2002 and thus I do not
recognize all English words.

Yes, your version of ACCESS uses ; instead of , as the separator character.

By "grid" I assume you mean the Update Query pane I used in the first
part.
Yes.


I put your expression into the first cell in the next empty column, named
Field. Access did not accept a comma there either but ; worked.

Yes, use ; instead of , in your version of ACCESS.

Next line is titled Table in Swedish but I assume that is where you wanted
me to put the next expressioin <>""

No. You need to put the
<>""
expression in the fourth row (labeled "Criteria:" in my version; I
erroneously typed "Where:" in my earlier reply).

The problem is that Access denies to accept anything else than the listed
tables. This means that many of the cells in my JPGField contain path that
do not exist.
Any thought about the reason?

You ran the first query that updated all the records? And then wanted to try
the second one? Sorry, my intent for providing both was to give you a choice
for which one to run. The first puts a value in all records (as you
discovered), the second one would have put a value in the records only where
the jpg file already existed.

So, we can clean up the problem here by deleting the values in the records
where no jpg file exists.
1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on toolbar).
5. Put this expression in the "Update To:" cell under the field name that is
on the grid:
Null
6. Go to the next empty column on the grid. Type this expression into the
"Field:" cell in
that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
7. In the "Criteria:" cell in this column, type this expression:
=""
8. Run the query.

Ove

Here is how you can pad a number with leading zeroes (in this case, a
total of four digits in the final number -- so 1 becomes 0001) by using
Format function:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg";


The way you would create this query using the design view for the query
is this:

1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on
toolbar).
5. Put this expression in the "Update To:" cell under the field name that
is on the grid:
"C:\Hembygd\Bilder\soh" & Format([NR], "0000") & ".jpg"
6. Run the query.


Note that this query will not verify that a jpg file actually exists
before putting the path and filename into the field. If you want to put
the filename into the field *only if* the file actually exists, go to the
next empty column on the grid. Type this expression into the "Field:"
cell in that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
Then, in the "Where:" cell in this column, type this expression:
<>""
Then run the query. This will not insert a path and filename where the
jpg file does not exist.


--

Ken Snell
<MS ACCESS MVP>

Ken,
I have tried different things for several hours but I do not get it OK.
I always get a 0 for all records in the field.

I have from the database window opened a new query, chosen my table
(comes on line 2) and my new created empty field (on line 1). Then I
chose Update type of query from an icon to the left of ! in the toolbox
and Update query apperas in the name bar of the pane. I now put your
expression on line 3 and pushed ! in the toolbox for Run. As I felt a
bit unsure about your line I also tried to change it to ...soh" & [NR]
& ".jpg" but without success.

Could you be kind enough to tell me what I have made wrong.

I also try to help other small museums nearby on a non-profit basis and
one of them has [NR] with plain integers, thus without leading zeros
( as 1, 2, 10 ...) but the images have names like soh0001.jpg. How to
deal with this?

I should perhaps also mention that my aim is to be able to select all
records having an image and thus sort out those without, but I have not
been able to find any method as the only evidence is the content of a
folder. But it is, as I understand now, not necessary to get the actual
file name in my field, it should do with only Yes/No or 1/0. Perhaps is
this easier?

Regards/Ove




"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
Then you should be able to run an update query to put the correct
filename into the table's field. Here is a generic SQL statement;
replace my generic names with your correct names:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh(" & [NR] & ").jpg";

--

Ken Snell
<MS ACCESS MVP>




Ken,
The filename is C:\Hembygd\Bilder\soh(NR).jpg where NR is the item# in
the NR field in Maintable. Example soh0123.jpg. The new field should
come into the same table and the .mdb is in the Hembygd folder./Ove


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet Can you "create" the correct jpg file name from the information that
is in the database table? Or do you need to actually know the file
name of the jpg file? Let us know; how to approach this problem
depends upon your reply.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a simple db with some 8000 records and an external folder
with
filenames. I wish to get these names into a Pathfield in the main
table so
that for each Record# the appropriate filename is fed in the new
Pathfileld.
The files are images with names like xyz????.jpg where ???? i the
same as
the Record#. Some records have no corresponding image. How to get
this? I
would appreciate a detailded answer as my experience with Access is
little.
Ove

 

[OveB]

Ken,
Excellent! Now I have the list as desired.
I understood that the field created in step 1 should not with certainty be
usable for step 2 so I had deleted the column and created a new empty one
for every test. I have saved the query with the exp. ="" as the one to use
in the future when new records have been fed and I need to update. I hope
this is OK and what you meant.

Many, many thanks for your efforts and your patience with me and my
problems.
Ove

Answers inline:

--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks! The first part now worked OK and I got all cells filled, although
I had to replace your , by ; after [NR]. Accees refused to accept the
comma.
So to the Test. I have a Swedish version of Access 2002 and thus I do not
recognize all English words.

Yes, your version of ACCESS uses ; instead of , as the separator
character.

By "grid" I assume you mean the Update Query pane I used in the first
part.
Yes.


I put your expression into the first cell in the next empty column, named
Field. Access did not accept a comma there either but ; worked.

Yes, use ; instead of , in your version of ACCESS.

Next line is titled Table in Swedish but I assume that is where you
wanted me to put the next expressioin <>""

No. You need to put the
<>""
expression in the fourth row (labeled "Criteria:" in my version; I
erroneously typed "Where:" in my earlier reply).

The problem is that Access denies to accept anything else than the listed
tables. This means that many of the cells in my JPGField contain path
that do not exist.
Any thought about the reason?

You ran the first query that updated all the records? And then wanted to
try the second one? Sorry, my intent for providing both was to give you a
choice for which one to run. The first puts a value in all records (as you
discovered), the second one would have put a value in the records only
where the jpg file already existed.

So, we can clean up the problem here by deleting the values in the records
where no jpg file exists.
1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on toolbar).
5. Put this expression in the "Update To:" cell under the field name that
is
on the grid:
Null
6. Go to the next empty column on the grid. Type this expression into the
"Field:" cell in
that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
7. In the "Criteria:" cell in this column, type this expression:
=""
8. Run the query.

Ove

Here is how you can pad a number with leading zeroes (in this case, a
total of four digits in the final number -- so 1 becomes 0001) by using
Format function:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg";


The way you would create this query using the design view for the query
is this:

1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on
toolbar).
5. Put this expression in the "Update To:" cell under the field name
that is on the grid:
"C:\Hembygd\Bilder\soh" & Format([NR], "0000") & ".jpg"
6. Run the query.


Note that this query will not verify that a jpg file actually exists
before putting the path and filename into the field. If you want to put
the filename into the field *only if* the file actually exists, go to
the next empty column on the grid. Type this expression into the
"Field:" cell in that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
Then, in the "Where:" cell in this column, type this expression:
<>""
Then run the query. This will not insert a path and filename where the
jpg file does not exist.


--

Ken Snell
<MS ACCESS MVP>


Ken,
I have tried different things for several hours but I do not get it OK.
I always get a 0 for all records in the field.

I have from the database window opened a new query, chosen my table
(comes on line 2) and my new created empty field (on line 1). Then I
chose Update type of query from an icon to the left of ! in the toolbox
and Update query apperas in the name bar of the pane. I now put your
expression on line 3 and pushed ! in the toolbox for Run. As I felt a
bit unsure about your line I also tried to change it to ...soh" &
[NR] & ".jpg" but without success.

Could you be kind enough to tell me what I have made wrong.

I also try to help other small museums nearby on a non-profit basis and
one of them has [NR] with plain integers, thus without leading zeros
( as 1, 2, 10 ...) but the images have names like soh0001.jpg. How to
deal with this?

I should perhaps also mention that my aim is to be able to select all
records having an image and thus sort out those without, but I have not
been able to find any method as the only evidence is the content of a
folder. But it is, as I understand now, not necessary to get the actual
file name in my field, it should do with only Yes/No or 1/0. Perhaps is
this easier?

Regards/Ove




"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet Then you should be able to run an update query to put the correct
filename into the table's field. Here is a generic SQL statement;
replace my generic names with your correct names:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh(" & [NR] & ").jpg";

--

Ken Snell
<MS ACCESS MVP>




Ken,
The filename is C:\Hembygd\Bilder\soh(NR).jpg where NR is the item#
in the NR field in Maintable. Example soh0123.jpg. The new field
should come into the same table and the .mdb is in the Hembygd
folder./Ove


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet Can you "create" the correct jpg file name from the information that
is in the database table? Or do you need to actually know the file
name of the jpg file? Let us know; how to approach this problem
depends upon your reply.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a simple db with some 8000 records and an external folder
with
filenames. I wish to get these names into a Pathfield in the main
table so
that for each Record# the appropriate filename is fed in the new
Pathfileld.
The files are images with names like xyz????.jpg where ???? i the
same as
the Record#. Some records have no corresponding image. How to get
this? I
would appreciate a detailded answer as my experience with Access is
little.
Ove

 

[Ken Snell [MVP]]

I'm not exactly sure of what type of updates you'll be doing in the future,
but you should be able to modify the query as needed then. Good luck.

--

Ken Snell
<MS ACCESS MVP>

Ken,
Excellent! Now I have the list as desired.
I understood that the field created in step 1 should not with certainty be
usable for step 2 so I had deleted the column and created a new empty one
for every test. I have saved the query with the exp. ="" as the one to use
in the future when new records have been fed and I need to update. I hope
this is OK and what you meant.

Many, many thanks for your efforts and your patience with me and my
problems.
Ove

Answers inline:

--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks! The first part now worked OK and I got all cells filled,
although I had to replace your , by ; after [NR]. Accees refused to
accept the comma.
So to the Test. I have a Swedish version of Access 2002 and thus I do
not recognize all English words.

Yes, your version of ACCESS uses ; instead of , as the separator
character.

By "grid" I assume you mean the Update Query pane I used in the first
part.
Yes.


I put your expression into the first cell in the next empty column,
named Field. Access did not accept a comma there either but ; worked.

Yes, use ; instead of , in your version of ACCESS.

Next line is titled Table in Swedish but I assume that is where you
wanted me to put the next expressioin <>""

No. You need to put the
<>""
expression in the fourth row (labeled "Criteria:" in my version; I
erroneously typed "Where:" in my earlier reply).

The problem is that Access denies to accept anything else than the
listed tables. This means that many of the cells in my JPGField contain
path that do not exist.
Any thought about the reason?

You ran the first query that updated all the records? And then wanted to
try the second one? Sorry, my intent for providing both was to give you a
choice for which one to run. The first puts a value in all records (as
you discovered), the second one would have put a value in the records
only where the jpg file already existed.

So, we can clean up the problem here by deleting the values in the
records where no jpg file exists.
1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on
toolbar).
5. Put this expression in the "Update To:" cell under the field name that
is
on the grid:
Null
6. Go to the next empty column on the grid. Type this expression into the
"Field:" cell in
that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
7. In the "Criteria:" cell in this column, type this expression:
=""
8. Run the query.

Ove


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
Here is how you can pad a number with leading zeroes (in this case, a
total of four digits in the final number -- so 1 becomes 0001) by using
Format function:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg";


The way you would create this query using the design view for the query
is this:

1. Create new query in design view.
2. Select the table from the list; click Add button; close window.
3. Drag field that is to contain the JPG file names onto the grid.
4. Change the query to Update type (using the Query Type icon on
toolbar).
5. Put this expression in the "Update To:" cell under the field name
that is on the grid:
"C:\Hembygd\Bilder\soh" & Format([NR], "0000") & ".jpg"
6. Run the query.


Note that this query will not verify that a jpg file actually exists
before putting the path and filename into the field. If you want to put
the filename into the field *only if* the file actually exists, go to
the next empty column on the grid. Type this expression into the
"Field:" cell in that column:
TestFileExists: Dir("C:\Hembygd\Bilder\soh" & Format([NR], "0000") &
".jpg")
Then, in the "Where:" cell in this column, type this expression:
<>""
Then run the query. This will not insert a path and filename where the
jpg file does not exist.


--

Ken Snell
<MS ACCESS MVP>


Ken,
I have tried different things for several hours but I do not get it
OK. I always get a 0 for all records in the field.

I have from the database window opened a new query, chosen my table
(comes on line 2) and my new created empty field (on line 1). Then I
chose Update type of query from an icon to the left of ! in the
toolbox and Update query apperas in the name bar of the pane. I now
put your expression on line 3 and pushed ! in the toolbox for Run. As
I felt a bit unsure about your line I also tried to change it to
...soh" & [NR] & ".jpg" but without success.

Could you be kind enough to tell me what I have made wrong.

I also try to help other small museums nearby on a non-profit basis
and one of them has [NR] with plain integers, thus without leading
zeros ( as 1, 2, 10 ...) but the images have names like soh0001.jpg.
How to deal with this?

I should perhaps also mention that my aim is to be able to select all
records having an image and thus sort out those without, but I have
not been able to find any method as the only evidence is the content
of a folder. But it is, as I understand now, not necessary to get the
actual file name in my field, it should do with only Yes/No or 1/0.
Perhaps is this easier?

Regards/Ove




"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet Then you should be able to run an update query to put the correct
filename into the table's field. Here is a generic SQL statement;
replace my generic names with your correct names:

UPDATE Tablename
SET JPGFieldName = "C:\Hembygd\Bilder\soh(" & [NR] & ").jpg";

--

Ken Snell
<MS ACCESS MVP>




Ken,
The filename is C:\Hembygd\Bilder\soh(NR).jpg where NR is the item#
in the NR field in Maintable. Example soh0123.jpg. The new field
should come into the same table and the .mdb is in the Hembygd
folder./Ove


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet Can you "create" the correct jpg file name from the information
that is in the database table? Or do you need to actually know the
file name of the jpg file? Let us know; how to approach this
problem depends upon your reply.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a simple db with some 8000 records and an external folder
with
filenames. I wish to get these names into a Pathfield in the main
table so
that for each Record# the appropriate filename is fed in the new
Pathfileld.
The files are images with names like xyz????.jpg where ???? i the
same as
the Record#. Some records have no corresponding image. How to get
this? I
would appreciate a detailded answer as my experience with Access
is little.
Ove