Cyherus-- This is about the PICTURE problem I had before. The query you suggested works fine. Thanks a lot! But now instead of pulling data from two tables I want to pull pictures from 3 or maybe 4 tables. Is this possible?
SELECT PHOTO.Picture, PICTURE1.Picture, DATA.Group, DATA.After_Change, DATA.EngineType, LOC_MAP3.Location, DATA.PartNomen, DATA.変更前, DATA.[Repair No], DATA.[Tool No], DATA.[GJ No], DATA.[RE No], DATA.Weight, DATA.[Fit Check], DATA.[Open Date], DATA.[Close Date], DATA.固資No, LOC_MAP3.Location, DATA.Remark, IIf([PICTURE1].[Picture],[PICTURE1].[Picture],[PHOTO].[Picture]) AS PICT
FROM (PICTURE1 RIGHT JOIN (PHOTO RIGHT JOIN DATA ON PHOTO.[Tool No] = DATA.[Tool No]) ON PICTURE1.[Tool No] = DATA.[Tool No]) LEFT JOIN LOC_MAP3 ON DATA.[Tool No] = LOC_MAP3.[Tool No]
WHERE (((DATA.[Tool No]) Like [Forms]![LOC_DETAIL]![Tool No]));
yes. you can do a nested IIF..
eg: 2 tables: PICTURE, PICTURE1, PICTURE2
IIf([PICTURE].[Picture],[PICTURE].[Picture],IIF([PICTURE1].[Picture],[PICTURE1].[Picture],[PICTURE2].[Picture])) AS PICT
in short, replace another IIF statement in the false criteria to perform another comparison.. this can go on... and on..
That works!! Thanks
Now I have two new issues
1) Before I tried out all the IIF stuff and when I had only one picture table, I could drag and drop pictures on the form without any issues. I can't do that anymore. I have some Tool Nos' that do not have any pictures. So if the user want to update that tool no's record he should be able to drag and drop pics from his desktop.
2) Also, If a user enters a totally new tool number and adds a pic (if the drag and drop works) what table is that picture going to be added to since I have 3 PICTURE tables. I know the tool no will be added to the MASTER table but what about the pic?
dmesh, ans is you can't. my method will not work if you want to update or add records into the query.. this query is not 'updateable'..
you should be able to work around this though. you can create a button for users to add pictures. once click, a form that is linked to the respective picture table is loaded and filtered to the particular record (toolno).. then you can drag and drop as usual.. remember, difference now is that you are adding directly into the tables.. not query..
as for which picture table to open, you have to decide how this can be determine.. eg: from the tool no?