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
7 2277
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 - SELECT TableName.Field*
-
FROM TableName
But I don't think it will work.
Why can't you just build your query with the appropriate fields?
Remaniak
You could try - SELECT TableName.Field*
-
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?
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.
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... - Dim intNumberOfFields As Integer, FieldName As String
-
Dim Counter As Integer, arrFieldNames() As String, T As Integer
-
-
'How many Fields are in the Table?
-
intNumberOfFields = CurrentDb.TableDefs("Table1").Fields.Count
-
ReDim arrFieldNames(intNumberOfFields - 1) 'will hold all Field Names
-
-
T = 0
-
-
For Counter = 0 To intNumberOfFields - 1
-
FieldName = CurrentDb.TableDefs("Table1").Fields(Counter).Name
-
'Extract only Field Names that begin with field
-
If UCase$(Left$(FieldName, 5)) = "FIELD" Then
-
arrFieldNames(T) = FieldName 'arrFieldNames() holds only Fields with name Field*
-
T = T + 1
-
End If
-
Next Counter
-
-
Dim MyDB As Database, MyQuery As QueryDef
-
Set MyDB = CurrentDb()
-
-
'Build the SQL String from arrFieldNames
-
Set MyQuery = MyDB.CreateQueryDef("FieldOnly", <SQLString>)
'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... - Dim intNumberOfFields As Integer, FieldName As String
-
Dim Counter As Integer, arrFieldNames() As String, T As Integer
-
-
'How many Fields are in the Table?
-
intNumberOfFields = CurrentDb.TableDefs("Table1").Fields.Count
-
ReDim arrFieldNames(intNumberOfFields - 1) 'will hold all Field Names
-
-
T = 0
-
-
For Counter = 0 To intNumberOfFields - 1
-
FieldName = CurrentDb.TableDefs("Table1").Fields(Counter).Name
-
'Extract only Field Names that begin with field
-
If UCase$(Left$(FieldName, 5)) = "FIELD" Then
-
arrFieldNames(T) = FieldName 'arrFieldNames() holds only Fields with name Field*
-
T = T + 1
-
End If
-
Next Counter
-
-
Dim MyDB As Database, MyQuery As QueryDef
-
Set MyDB = CurrentDb()
-
-
'Build the SQL String from arrFieldNames
-
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?
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.
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
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??
...
|
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...
|
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...
|
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*...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |