Return all Fields Except Memo Fields

[Guest]

I need to export a wide table to Excel, but do not want to include the memo
fields in the export. (They are unnecessary and don't look good in an
spreadsheet cell.) Is there a way to write a query to return all fields
except the memo fields?

My current query is just a simple SELECT * statement, but the table has too
many fields to write a practical query capturing all the fields except the
memo fields.

Alternatively, would it be possible to make a copy of the results of the
query, and modify the copy of the results (deleting the memo fields or the
contents of the memo fields in the copy only, so that the memo fields in the
original table are not affected)?

 

[John Spencer]

You have two choices.

SELECT *

Or
SELECT <<LIst all the fields you want returned>>

What you can do is the query grid is to double click on the table title (that
highlights all the field names and then click and drag that entire selection to
the grid. You can then find and delete the memo fields from the grid.

 

[Guest]

Thanks for the quick reply; I can see how to apply it and your thoughts are
helpful.

The query is actually used in a VBA module, and, although I could execute
the querydef object using VBA, I would still be at risk of not picking up
fields that are added to the table in the future, unless I remember to update
the query. Do you have any thoughts on making the query more robust?

 

[John Spencer]

If you are executing the query from VBA you can build the query string on
using VBA.

-- Grab the table and add the fields to the select clause, skipping any that
are memo type.

UNTESTED AIR CODE snippet follows. This should give you an idea on getting
started.

Dim strFieldList as string
Dim strSQL as String
Dim tblAny as DAO.TableDef
Dim dbAny as DAO.Database

Set DBany = Currentdb()
Set tblAny = dbany.tabledefs("Table Name")

StrSQL = " FROM [TableName] WHERE x=y and f=2" 'This would be everything
after the Select clause
For iLoop = 0 to tblAny.Fields.Count -1
If tblAny.Fields(iLoop).Type <> 12 then
strFieldList = strFieldList & ", [" & tblAny.Fields(iLoop).Name & "]"
end if
Next iLoop

strFieldList = mid(strFieldList,2) 'Trim off leading comma
strFieldList = "SELECT " & strFieldList

StrSql = StrFieldList & " " & strSQL

Debug.print StrSQL

Thanks for the quick reply; I can see how to apply it and your thoughts
are
helpful.

The query is actually used in a VBA module, and, although I could execute
the querydef object using VBA, I would still be at risk of not picking up
fields that are added to the table in the future, unless I remember to
update
the query. Do you have any thoughts on making the query more robust?

 

[Guest]

Thanks! Your response is exactly what I need!
--
Greg

If you are executing the query from VBA you can build the query string on
using VBA.

-- Grab the table and add the fields to the select clause, skipping any that
are memo type.

UNTESTED AIR CODE snippet follows. This should give you an idea on getting
started.

Dim strFieldList as string
Dim strSQL as String
Dim tblAny as DAO.TableDef
Dim dbAny as DAO.Database

Set DBany = Currentdb()
Set tblAny = dbany.tabledefs("Table Name")

StrSQL = " FROM [TableName] WHERE x=y and f=2" 'This would be everything
after the Select clause
For iLoop = 0 to tblAny.Fields.Count -1
If tblAny.Fields(iLoop).Type <> 12 then
strFieldList = strFieldList & ", [" & tblAny.Fields(iLoop).Name & "]"
end if
Next iLoop

strFieldList = mid(strFieldList,2) 'Trim off leading comma
strFieldList = "SELECT " & strFieldList

StrSql = StrFieldList & " " & strSQL

Debug.print StrSQL