Omitting a field when using SELECT *

  • Thread starter Thread starter Nath
  • Start date Start date
N

Nath

Hi

I havea table that has 47 fields in it. The last field is
puerly for identification of who created the record and
hence i dont want it to be included in a query that
returns to excel.

The field is titled "Owner" and the query i am using is:

"Select * from [tbl_nathans_data];"

Is there any way of omitting the "Owner" fielsd and i dont
want this to be returned to Excel via my VBA code, putting
all the field names in a coded query would become messy.

TIA

Nath.
 
Nath, you can't "SELECT * EXCEPT SomeField", but you can build a SQL string
that drops the last field in the table:

Function MakeSql(strTable As String) As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer
Dim strOut As String
Dim lngLen As Long

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)

For i = 0 To tdf.Fields.Count - 2 'Skip the last field.
strOut = strOut & "[" & strTable & "].[" & tdf.Fields(i).Name & "],
"
Next

lngLen = Len(strOut) - 2
If lngLen > 0 Then

MakeSql = "SELECT " & Left$(strOut, lngLen) & " FROM [" & strTable &
"];"
End If

Set tdf = Nothing
Set db = Nothing
End Function
 
Hi,


no.



To select all the fields, explicitly, by name, double-click the table
name, in the top half portion of the designer, to select all the fields,
then, drag the fields (list) into the grid. Once done, remove the field you
don't want to see. That generates for you the interminable list of fields,
less the one you don't want... since there is no way using * to remove
fields.





Hoping it may help,
Vanderghast, Access MVP
 
Back
Top