Re: Filenames to a field

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 03/09/05 

Date: Wed, 9 Mar 2005 10:48:17 -0500

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>
"OveB" <ob.lhlm@telia.com> wrote in message 
news:ZADXd.132072$dP1.470924@newsc.telia.net...
> 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]" <kthsneisllis9@ncoomcastt.renaetl> skrev i meddelandet 
> news:euluJiAJFHA.3928@TK2MSFTNGP09.phx.gbl...
>> 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" <ob.lhlm@telia.com> wrote in message 
>> news:VdlXd.132016$dP1.470657@newsc.telia.net...
>>>
>>> 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]" <kthsneisllis9@ncoomcastt.renaetl> skrev i meddelandet 
>>> news:%23FkXL3$IFHA.1948@TK2MSFTNGP14.phx.gbl...
>>>> 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>
>>>>
>>>> "OveB" <ob.lhlm@telia.com> wrote in message 
>>>> news:likXd.132011$dP1.470968@newsc.telia.net...
>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>