Images + Tables

[FlawLezZ]

Hello there

I got a questions and maybe you prof. can help me out

I got a table with all employees in it

Example

John Doil
Mina Lizo
Sholly Nopi

etc.

Its has 250 records.

I also got all the pictues of them
But the pictures have the following name

John_Doil_.jpg
Mina_Lizo_.jpg
Sholly_Nopi_.jpg

Also i still have pictues from ex employees.

I have all the filepaths of the jpg in a table.

Is there a way to link the 2 tables based on first name
Or make a extra cell and in a query take the names for the _ that takes out and put it in that cell.

Sorry for my bad english if you got question plz ask

 

[reclusivemonkey]

Do you have surname and forename in seperate fields? Its still possible to do this if you do not have it set up this way, but it will be much easier if you do.

 

[FlawLezZ]

No i got both in 1 field

But if it must i can change it to 2 serparate fields

Thx 4 reply

 

[reclusivemonkey]

It may be an idea to split them into two fields; I think its better that way. You might not get exactly the results you need first time with this, as you may have middle names, but as a start;

1. Find the first space in the name field;

FirstSpace: InStr(1,[YourNameField]," ",1)

2. Then use this number to get the Forename;

Forename: Left([YourNameField],FirstSpace - 1)

3. Then use this number again to get the surname;

Surname: Right([YourNameField],Len([YourNameField])-FirstSpace)

Then all you need to do is add these with your jpg syntax;
4. Filename;

PictureFilename: Forename+"_"+Surname+"_.jpg"

Now that will work for just a surname and forename - if you have middle names anywhere it won't work. You should be able to get most of the names this way, and correct the few ones that don't work manually. I would suggest you change your name table first with points 1-3, then use point 4 in a new query with your names split between forename and surname. Let me know how you get on.

 

[FlawLezZ]

I got now 1 table with 2 fields Forename Surname with all the records in it

Do i still have to do the first 3 points you mentioned ?

i think yes because else you cant excute point 4 right ?

But where do i have to enter these commands ?

I know at point 4 i have to enter the command in critiria right ?

 

[reclusivemonkey]

If you have now separated the names, you only need to use the code in point 4 in a query. You should now use that query inside another query which will match up the filenames with the pcitures.

 

[FlawLezZ]

also have a table for the image with 2 fields imagepath and oleobject. filled in and ready

But now how can i make the link ?

Got 2 tables now but

1 table with Forname and Surname
1 Table with Filepath and OLEFile

Filepath is D:\images\Forname_Surname_.jpg

 

[reclusivemonkey]

Create a query from your table with surname and forename. Use the code below to get the Filepath.

PictureFilename: "D:\images\"+Forename+"_"+Surname+"_.jpg"

Create another query, with the one you just made, and the table with Filepath and OLEFile. You can link the PictureFilename field from your query with the Filepath of your second table.

 

[FlawLezZ]

Thanks for your fast repsonse ill try it out this friday cause i wont be on my work atm.

Ill let you know if it works out
Thanks

 

[FlawLezZ]

Create a query from your table with surname and forename. Use the code below to get the Filepath.

PictureFilename: "D:\images\"+Forename+"_"+Surname+"_.jpg"

Create another query, with the one you just made, and the table with Filepath and OLEFile. You can link the PictureFilename field from your query with the Filepath of your second table.

I keep getting a error if i fill this code

PictureFilename: "D:\images\"+Forename+"_"+Surname+"_.jpg"

in the critiria of the query

Btw forname and surename
are voornaam en achternaam

its dutch
see attach