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

[Preying Mentis]

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

 

[Rick Brandt]

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,

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

 

[Dirk Goldgar]

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

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]

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

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;"

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.