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?
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?
off the cuff; try - Not is Null or possibly >0 as criteria
See this article from StackOverflow. The function and query may be a good starting point.
Good luck.
Thanks for the assistance but I have tried both suggestion before and they do not work.
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.
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.
I agree. Sorry.
When I enter Is Null all records returns including the one with no attachments
When I enter >0 no records appears
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
Results inCode:SELECT DISTINCT Table1.ID, Table1.mname FROM Table1 WHERE (((Table1.Att.FileName)>" "));
which I think is what you want.Code:
ID mname 1 jack 3 joe
An attachment field has 3 components (I have just learned)
FileData
FileName
FileType.
I queried on FileName > " " and got the expected hits.
Good luck.
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.
a query with the attachment.FileData field having the criteria: Not Is Null
will return those records with an attachment. my test works fine.
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.
What exactly does "DOES NOT WORK" mean?
Please show us an example ---starting situation, code and result.
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