473,322 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
7 2277
MMcCarthy
14,534 Expert Mod 8TB
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
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
'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
32,556 Expert Mod 16PB
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
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

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

Similar topics

2
by: John Davis | last post by:
I want to know what's the differences between Request.Form("Field Name") and Request.QueryString("Field Name") OR they function exactly the same, which is to return the value of the field?? ...
1
by: Charlie | last post by:
Hi: I'm using the HTML File Field control as a file picker for uploading files to a SQl Server image field. When page posts back to initiate upload, if file is small (under about two megs) all...
0
by: pmarisole | last post by:
I am using the following code to split/join values in a multi-select field. It is combining all the values in All the records into one long string in each record in recordset. Example: I have a...
2
by: PW | last post by:
Hi, What the heck is that supposed to mean? I am getting this error on a "Me.Requery" line in a subroutine on a form, but only when I select something from a combo/dropdown box. The *exact*...
9
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
3
by: Metplant | last post by:
I have almost no experience in ACCESS so am trying to get the system functioning with minimal operator input. Currently I have managed to modify a database generated by a SCADA system using a...
10
Shakss2
by: Shakss2 | last post by:
I have a form, where I can activate the search and replace dialogbox when pressing a button. My problem is that the dialogbox opens with the default that it should search for full match only,...
4
by: dougmeece | last post by:
Hello everyone, I have created a search button but I receive the message "Microsoft Access can't fine the field "|" referred to in your expression" when I try to use it. I have looked online for...
4
kcdoell
by: kcdoell | last post by:
Hello: I have the following afterupdate event: Private Sub GWP_AfterUpdate() 'Updates the Total calculation in the control "SumGWP" on the quick reference 'table that is located on the form...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.