By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Queries: How do I select all colums starting with the word "field"?

P: 16
Hi All,

I am using Access 2003 an I have a question on queries:
From a table with about 40 columns I would like to see only the columns with a headername that start with the word "field".

Would anyone be able to help me out?

Thanks

Remaniak
Dec 5 '06 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi All,

I am using Access 2003 an I have a question on queries:
From a table with about 40 columns I would like to see only the columns with a headername that start with the word "field".

Would anyone be able to help me out?

Thanks

Remaniak
Remaniak

You could try

Expand|Select|Wrap|Line Numbers
  1. SELECT TableName.Field*
  2. FROM TableName
But I don't think it will work.

Why can't you just build your query with the appropriate fields?
Dec 5 '06 #2

P: 16
Remaniak

You could try

Expand|Select|Wrap|Line Numbers
  1. SELECT TableName.Field*
  2. FROM TableName
But I don't think it will work.

Why can't you just build your query with the appropriate fields?
Thanks Mccarthy,

But you are right, it doesn't work. The reason I want to query for columns with certain criteria is because I will import different textfiles, with different headers each time. I do know that after having imported the textfile some of them will generate a default "field.." header.

Is there any other of doing this? Perhaps with VB?
Dec 5 '06 #3

NeoPa
Expert Mod 15k+
P: 31,616
I don't think you have easy access to meta-data via the SQL interface. I certainly don't know about it.
I think you would probably be better advised to look at pre-processing your textual data to ensure it has consistent field names instead.
Sorry i couldn't be more help.
Dec 5 '06 #4

ADezii
Expert 5K+
P: 8,669
Hi All,

I am using Access 2003 an I have a question on queries:
From a table with about 40 columns I would like to see only the columns with a headername that start with the word "field".

Would anyone be able to help me out?

Thanks

Remaniak
'Here is a rather crude Method (partial) of solving your problem. The Query will be created via code first by extracting all Field Names like Field*, store them in an Array, build an SQL String from the Array, then use the CreateQueryDef() Method of the Database Object to create the Query. If you would like the complete code, please let me know...

Expand|Select|Wrap|Line Numbers
  1. Dim intNumberOfFields As Integer, FieldName As String
  2. Dim Counter As Integer, arrFieldNames() As String, T As Integer
  3.  
  4. 'How many Fields are in the Table?
  5. intNumberOfFields = CurrentDb.TableDefs("Table1").Fields.Count
  6. ReDim arrFieldNames(intNumberOfFields - 1)  'will hold all Field Names
  7.  
  8. T = 0
  9.  
  10. For Counter = 0 To intNumberOfFields - 1
  11.   FieldName = CurrentDb.TableDefs("Table1").Fields(Counter).Name
  12.   'Extract only Field Names that begin with field
  13.   If UCase$(Left$(FieldName, 5)) = "FIELD" Then
  14.     arrFieldNames(T) = FieldName    'arrFieldNames() holds only Fields with name Field*
  15.     T = T + 1
  16.   End If
  17. Next Counter
  18.  
  19. Dim MyDB As Database, MyQuery As QueryDef
  20. Set MyDB = CurrentDb()
  21.  
  22. 'Build the SQL String from arrFieldNames
  23. Set MyQuery = MyDB.CreateQueryDef("FieldOnly", <SQLString>)
Dec 6 '06 #5

P: 16
'Here is a rather crude Method (partial) of solving your problem. The Query will be created via code first by extracting all Field Names like Field*, store them in an Array, build an SQL String from the Array, then use the CreateQueryDef() Method of the Database Object to create the Query. If you would like the complete code, please let me know...

Expand|Select|Wrap|Line Numbers
  1. Dim intNumberOfFields As Integer, FieldName As String
  2. Dim Counter As Integer, arrFieldNames() As String, T As Integer
  3.  
  4. 'How many Fields are in the Table?
  5. intNumberOfFields = CurrentDb.TableDefs("Table1").Fields.Count
  6. ReDim arrFieldNames(intNumberOfFields - 1)  'will hold all Field Names
  7.  
  8. T = 0
  9.  
  10. For Counter = 0 To intNumberOfFields - 1
  11.   FieldName = CurrentDb.TableDefs("Table1").Fields(Counter).Name
  12.   'Extract only Field Names that begin with field
  13.   If UCase$(Left$(FieldName, 5)) = "FIELD" Then
  14.     arrFieldNames(T) = FieldName    'arrFieldNames() holds only Fields with name Field*
  15.     T = T + 1
  16.   End If
  17. Next Counter
  18.  
  19. Dim MyDB As Database, MyQuery As QueryDef
  20. Set MyDB = CurrentDb()
  21.  
  22. 'Build the SQL String from arrFieldNames
  23. Set MyQuery = MyDB.CreateQueryDef("FieldOnly", <SQLString>)

Hi ADezii,

This sounds like a solution that could definitely work for my problem. I would be interested to hear the full story on this.

About the number of columns in the text file: this number will differ each time a textfile is loaded. Would that be a problem?
Dec 6 '06 #6

NeoPa
Expert Mod 15k+
P: 31,616
Remaniak,

May I suggest that you go through the code a bit first.
I'm sure ADezii (or others) will be happy to help on specific questions but it's actually quite hard and time-consuming to explain a whole chunk of code like that - just to save you the trouble.

MODERATOR.
Dec 6 '06 #7

P: 16
Remaniak,

May I suggest that you go through the code a bit first.
I'm sure ADezii (or others) will be happy to help on specific questions but it's actually quite hard and time-consuming to explain a whole chunk of code like that - just to save you the trouble.

MODERATOR.
Ok NeoPa,

I will do that first. Thanks a bunch!
Dec 6 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.