Sorting Field Names in Tables being used for queries

[Guest]

Does anyone know a way to sort the field names in the table windows being
used for a Query in Design view? When constructing queries from tables that
have many fields it can be cumbersome to find the field I want. MS Query
sorts the field names alphabetically but Access does not appear to do that.

Thanks

Bob

 

[fredg]

Does anyone know a way to sort the field names in the table windows being
used for a Query in Design view? When constructing queries from tables that
have many fields it can be cumbersome to find the field I want. MS Query
sorts the field names alphabetically but Access does not appear to do that.

Thanks

Bob

The field names are listed in the query table window according to how
they are listed in the table design view. If you re-arrange the field
names in the table, that will also effect how the table will display
the columns. It shouldn't matter because you should never view the
table anyway.

Actually it really shouldn't matter how the query table list displays
the fields.
Instead of looking for the field in the table list and then
double-clicking or dragging the field to the column, simply type the
first, second, third, etc. letter of the field name you want directly
in the column's Field row drop-down.
If you have 2 or more tables, select the table name first in the table
name row, then the type the field name for that column.

 

[gls858]

Does anyone know a way to sort the field names in the table windows being
used for a Query in Design view? When constructing queries from tables that
have many fields it can be cumbersome to find the field I want. MS Query
sorts the field names alphabetically but Access does not appear to do that.

Thanks

Bob

Not sure if this will help or not, but if you just start typing
in the field box in the query it will bring up the nearest match.
Example: If you have a field names DeptCode and you type De in the
field box it should automagically appear assuming you don't have another
field start with De.

gls858

 

[Guest]

Here's one from a truly great Access developer. And no it's certainly not me!
I highly recommend a complete backup of the database first.

Public Function AlphaSortFields(sTable As String)
'created by Lambert Heenan [[email protected]]
Dim db As Database
Dim tdf As TableDef
Dim intTemp As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(sTable)
With tdf
For intTemp = 0 To .Fields.Count - 1
' set all fields' OrdinalPosition to 1
' causes Access to sort the fields alphabetically
.Fields(intTemp).OrdinalPosition = 1
Next intTemp
End With
Set tdf = Nothing
db.Close
Set db = Nothing
End Function

 

[Guest]

Nice function Jerry. Thanks for posting it.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Here's one from a truly great Access developer. And no it's certainly not me!
I highly recommend a complete backup of the database first.

Public Function AlphaSortFields(sTable As String)
'created by Lambert Heenan [[email protected]]
Dim db As Database
Dim tdf As TableDef
Dim intTemp As Integer
Set db = CurrentDb
Set tdf = db.TableDefs(sTable)
With tdf
For intTemp = 0 To .Fields.Count - 1
' set all fields' OrdinalPosition to 1
' causes Access to sort the fields alphabetically
.Fields(intTemp).OrdinalPosition = 1
Next intTemp
End With
Set tdf = Nothing
db.Close
Set db = Nothing
End Function

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Does anyone know a way to sort the field names in the table windows being
used for a Query in Design view? When constructing queries from tables that
have many fields it can be cumbersome to find the field I want. MS Query
sorts the field names alphabetically but Access does not appear to do that.

Thanks

Bob