How can I Exclude a Field From a Select * Query?

message from Preying Mentis on 13 May 2004

I have a table that has over a hundred fields (about 150). I am trying
to select all the fields but one (149).

I am doing this through code and am not using the Access Query builder
due to variables being concatenated into the SQL string.

Is there a SQL keyword out there that I can use to exclude a certain
field, but return all the others?

I do NOT want to, unless forced to, create a query like this:

Select Field1, Field2, Filed3, Filed4, Field5, etc..... From Table1

It will be VERY tedious and cumbersome.

Please tell me there is a way I can create a query simular to this:

Select *, NOT Field149 From Table1

Thank you for any support you can give.

In dire need and programming agony,

Preying Mentis

Dirk Goldgar replied to Preying Mentis on 13 May 2004

"Preying Mentis" <preyingmentis@hotmail.com> wrote in message
news:cffb21cb.0405131453.3766bbe4@posting.google.com

I don't believe there's any way to do that in SQL, though I have
sometimes wished for it. If you're building a SQL statement in code,
you could have a function that looks at the TableDef and returns a list
of all fields except one you specify. Something like:

'----- start of function code (AIR CODE) -----
Function fncListAllFieldsExcept( _
TableName As String, _
Optional ExceptField As String) _
As String

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim strFieldName As String
Dim strFieldList As String
Dim intField As Integer

Set db = CurrentDb
Set td = db.TableDefs(TableName)

With td
For intField = 0 To .Fields.Count
strFieldName = .Fields(intField).Name
If strFieldName <> ExceptField Then
strFieldList = strFieldList & ", [" & strFieldName & "]"
End If
Next intField
End With

If Len(strFieldList) > 0 Then
fncListAllFieldsExcept = Mid$(strFieldList, 3)
End If

Exit_Point:
Set td = Nothing
Set db = Nothing
Exit Function

Err_Handler:
MsgBox _
"Error : " & Err.Number & ": " & Err.Description, _
vbExclamation, _
"Error in Function fncListAllFieldsExcept"

Resume Exit_Point

End Function
'----- end of function code -----

Then you might call this in your code that builds the SELECT statement:

Dim strSQL As String

strSQL = _
"SELECT " & _
fncListAllFieldsExcept("MyTable", "BadField") & _
" FROM MyTable;"

Preying Mentis replied to Dirk Goldgar on 18 May 2004

Thank You all who have posted a reply.

I guess there is not any type of exclude keyword, so I used Dirks
advice. Thanks Dirk.

I even included capability to exclude more than one field, and it
seems to be most useful.

Stress Relieved,
Preying Mentis.

Rick Brandt replied to Preying Mentis on 13 May 2004

There is no way that I'm aware of. Why do you need to exclude it?

Dave Ruhl replied to Preying Mentis on 13 May 2004

I don't know if the command you are looking for exists.
I would just use the query builder to create a query with
your 149 fields, then you could just "SELECT * FROM
qryNew149 WHERE..."

150). I am trying
Query builder
exclude a certain
this:
From Table1
simular to this:

Archived message: How can I Exclude a Field From a Select * Query? (Microsoft Access Database)