Hi All,

How do I query a database field of type attachment?

For eg:

I have a database field of type attachment and I want to run a query in
excel that identifies columns with no attachments and some other criterias
and populates it.

here is the query I have so far:

If rst.fields("7") Is Null and rst.fields("5") = "Chicago" Then
Me.File.Value = "NO"
End If

Hope I made it clear

Thanks in advance

You have two choices.

1) Use a SQL statement that gets only the records that meet your
requirements by filtering the Database.

2) move through the recordset like the code below

With rst
.movefirst
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Loop


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167796

Microsoft Office Help

Thanks for your help joel,

I understand the choices you gave me, But I am not getting the query wright
with tha attachment field in it. It doesnt give me the right results when I
use it in Excel

If rst.fields("7") Is Null and rst.fields("5") = "Chicago" Then
Me.File.Value = "NO"
End If

I tried creating the query in access and running it and it works fine, But
when I do it in excel it doesnt show me the right results.
Do I need to user any specific property of the attachment field in the query
when I am testing if its null? such as rst.fields("7.FileURL") ,
rst.fields("7.FileType") etc?

Basically I am having issues with running the query with attachement field
in the query.

Thanks in advance

"joel" wrote:


You have two choices.

1) Use a SQL statement that gets only the records that meet your
requirements by filtering the Database.

2) move through the recordset like the code below

With rst
.movefirst
Do While Not .EOF
Debug.Print , .Fields(0), .Fields(1)
.MoveNext
Loop


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167796

Microsoft Office Help

.

You can easily get the query syntax in Excel. Do the following

1) From : worksheet menu

Data - Import External Data - New Database Query

2) Select Ms Access Database browse for access file

3) Select the table and columns (if you don't see the table then go
into options and select all the boxes)

3) Press Next and sleect any filtering you want

4) Press Next to get to last box with finish button

5) Press button that say to View Data or edit query in Microsoft Query

6) Press Finish and the Query editor wil come up. there is a SQL
button that will have the the proper syntax of you SQL. You can also
make changes to the SQL and you will get errror messages letting you
know if you made any errors. Sample of you data will also appear in the
window so you know if you got the correct data.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167796

Microsoft Office Help

I tried doing that, But the query generator in excel is giving me an error
when I try generating query with access 2007 that is accdb format. and I
cannot do this by converting it to access 2002-2003 which is mdb format
because that version of access didnt allow attachments.


"joel" wrote:


You can easily get the query syntax in Excel. Do the following

1) From : worksheet menu

Data - Import External Data - New Database Query

2) Select Ms Access Database browse for access file

3) Select the table and columns (if you don't see the table then go
into options and select all the boxes)

3) Press Next and sleect any filtering you want

4) Press Next to get to last box with finish button

5) Press button that say to View Data or edit query in Microsoft Query

6) Press Finish and the Query editor wil come up. there is a SQL
button that will have the the proper syntax of you SQL. You can also
make changes to the SQL and you will get errror messages letting you
know if you made any errors. Sample of you data will also appear in the
window so you know if you got the correct data.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167796

Microsoft Office Help

.