Results 1 to 13 of 13
  1. #1
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295

    Querying database with attachment field

    Good Morning All.



    can anyone assist in explain how can a query be done to return only records that contains attachments in the attachment field?

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    off the cuff; try - Not is Null or possibly >0 as criteria

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295
    Thanks for the assistance but I have tried both suggestion before and they do not work.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This does not help the discussion
    they do not work.
    .
    What did you try?
    What was the result?
    Was there an error--what number?
    Show readers what you tried? Show us the input and your expected output.

  6. #6
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295
    Thanks for the link, but I had visited it before however it is not what I am seeking. I want to return the list of records which contains the attachments but I want it done in the query section with out using VBA.

  7. #7
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295
    I agree. Sorry.

    When I enter Is Null all records returns including the one with no attachments
    When I enter >0 no records appears

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    George:

    I just created a database with a table (Table1).

    Fields
    EntryID table_name table_description field_name field_description ordinal_position data_type length default
    10 Table1
    ID
    0 Long 4
    11 Table1
    mname
    1 Text 255
    12 Table1
    Att
    2
    4

    I populated record 1 and record 3 with Jpgs.

    In this simply query
    Code:
    SELECT DISTINCT Table1.ID, Table1.mname
    FROM Table1
    WHERE (((Table1.Att.FileName)>" "));
    Results in

    Code:
    ID mname
    1 jack
    3 joe
    which I think is what you want.

    An attachment field has 3 components (I have just learned)
    FileData
    FileName
    FileType.

    I queried on FileName > " " and got the expected hits.

    Good luck.

  9. #9
    George is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    295
    Thanks Orange for you assistance. However, unfortunately thecode did not work as you suggested. I can’t think why. Nevertheless I found yourinformation to be a profound educational exercise because it opened, immensely,my understanding of attachments in this context. From it I have been able tosolve the problem but in a different way which you and others may findinteresting:
    1. As you pointed out an attachment carries 3subfields or components, namely: FileData, FileName and FileType
    2. In the query window the Field name with theattachment data type can be added or draggedto the grid at the bottom half of the window
    3. Also the Subfields and be dragged or addedindividually. Note, however, that when they are selected individually all 4fields are highlighted, but never-the-less only the one the cursor is on wouldactually go to the grid
    4. case 2above is where you code is not responding
    5. In case 3 above the simple code that works islike so: >””. So if FileName isthe selected field, when the query is run, the names of the files would belisted. The same goes for the other 2 fields.

  10. #10
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    a query with the attachment.FileData field having the criteria: Not Is Null

    will return those records with an attachment. my test works fine.

  11. #11
    BrucePerry is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    9
    Just to add to this thread in a little more detail:
    From your query go to the design view.
    One of you bottom field is the "Attachment" Field you are doing a query on. Mine is called "Reference Pic".
    If I click on "Reference pic" there is a pull down. The field you want is "Reference Pic.FileData" or your field ".FileData"
    Now when you do a Is Null or Is Not Null then the Criteria works. Just Plane "Reference Pic" doesn't work.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly does "DOES NOT WORK" mean?

    Please show us an example ---starting situation, code and result.

  13. #13
    BrucePerry is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    9
    Click image for larger version. 

Name:	Fields.jpg 
Views:	13 
Size:	18.7 KB 
ID:	24594
    If you can see the image (I could not get it to enlarge) The first field will work because it is looking at the file date if there is none then Is Null is true
    if there is data then Is Null is false; however If you use the second Field Reference Pic the Is Null is False because (I think) it is looking at the "Attachment-Paper Clip"
    which all the rows would have. Thus Is Null would never be True. But once you add actual image data and it is present in the row. Then data populates in the form of
    the stuff you typically get with an image such as image dimension, pixels, file name, image type, etc. otherwise there is no data.
    Last edited by BrucePerry; 05-12-2016 at 06:27 AM. Reason: Clearity

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Attachment field in a Spit Database
    By ksmith in forum Access
    Replies: 10
    Last Post: 12-03-2014, 07:20 AM
  2. Replies: 3
    Last Post: 10-13-2014, 05:48 PM
  3. Replies: 1
    Last Post: 09-04-2014, 11:10 AM
  4. Replies: 2
    Last Post: 08-11-2014, 12:57 PM
  5. Replies: 5
    Last Post: 11-21-2013, 01:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums