Exclude afield when using 'Select *'

[Steve S]

The following appends all 15 fields but I want to exclude Fld3.

INSERT INTO Fees
SELECT Fees.*
FROM Fees
WHERE (((Fees.ContestID)=9));


Is there a way to do this without listing out all 14 fields I do want
appended?

 

[Rick Brandt]

The following appends all 15 fields but I want to exclude Fld3.

INSERT INTO Fees
SELECT Fees.*
FROM Fees
WHERE (((Fees.ContestID)=9));


Is there a way to do this without listing out all 14 fields I do want
appended?

No.

A couple of years ago I wrote a small utility VBA function that I can use
against a query or table and it will print to the debug window all of the
field names separated with commas. Comes in very handy when you don't want
to type out all those field names manually in SQL statements.

I sometimes have to work with legacy tables on our IBM box that have over
100 fields. Definitely worth the one time effort to set up.

 

[John Spencer]

No. There is no alternative to listing the fields you want.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve S]

Thanks Rick,

Not the answer I wanted but is what I expected. good idea about the utility
but I only have to do this once in a while and using QBE grid in design view
to generate most of my SQL saves lots of time.

 

[John W. Vinson]

The following appends all 15 fields but I want to exclude Fld3.

INSERT INTO Fees
SELECT Fees.*
FROM Fees
WHERE (((Fees.ContestID)=9));


Is there a way to do this without listing out all 14 fields I do want
appended?

As Rick says, no; but there's a quick way to add all the fields.

1. Add the table to the query design window.
2. Click the first field so it's selected.
3. Hold the Shift key and the arrow down key to select all fields.
4. Drag to the grid.

 

[David W. Fenton]

As Rick says, no; but there's a quick way to add all the fields.

1. Add the table to the query design window.
2. Click the first field so it's selected.
3. Hold the Shift key and the arrow down key to select all fields.
4. Drag to the grid.

Or:

1. select the first field.
2. SHIFT-click the last field.
3. CTRL-click any field you want to exclude.
4. drag to the grid.