Query without field name

  • Thread starter Thread starter Behrooz Zamirian
  • Start date Start date
B

Behrooz Zamirian

Hi;
Column name in A table that I import be changed periodically. Therfore, I
must change my query design.
How I can write a select statment only with column no. in design list of a
table.
(for exp. a statment like this: "Select Column[1], Column[3] from myTable")
 
hi,

Behrooz said:
Column name in A table that I import be changed periodically. Therfore, I
must change my query design.
How I can write a select statment only with column no. in design list of a
table.
(for exp. a statment like this: "Select Column[1], Column[3] from myTable")
Click to expand...
Use a query (:) to "normalize" your column names... You may use a
procedure for that.


mfG
--> stefan <--
 
To follow on from Stefan's answer

If you have a number field in a table and there are used as the base for
controls on a form (for example) you can use ":" to alter the name

So - you import 3 fields into a table (called for example field1 2 and 3)
You have controls on a form that are sourced on fields called "AName",
"SomeOtherName" and "AndAnotherName"

You would use this to "alter" the name so your form/report/etc can use it

SELECT TableName.Field1 AS AName, TableName.Field2 AS SomeOtherName,
TableName.Field3 AS AndAnotherName
FROM TableName;

This would alter the field name in the table but just allow it to be used by
the other "bits" of your application.

Hope this helps

--
Wayne
Manchester, England.



Stefan Hoffmann said:
hi,

Behrooz said:
Column name in A table that I import be changed periodically. Therfore, I
must change my query design.
How I can write a select statment only with column no. in design list of a
table.
(for exp. a statment like this: "Select Column[1], Column[3] from myTable")
Click to expand...
Use a query (:) to "normalize" your column names... You may use a
procedure for that.


mfG
--> stefan <--
Click to expand...
 
SDoory clicked send before I should have.,

The next step - if you want - is to use a Make Table query.

This will create a new table with the new names - but there are not many
times when you would need this if your DB was set up right.

Of course this will create a table with the wrong name so you would need to
run a delete query before



Hope this helps

--
Wayne
Manchester, England.



Stefan Hoffmann said:
hi,

Behrooz said:
Column name in A table that I import be changed periodically. Therfore, I
must change my query design.
How I can write a select statment only with column no. in design list of a
table.
(for exp. a statment like this: "Select Column[1], Column[3] from myTable")
Click to expand...
Use a query (:) to "normalize" your column names... You may use a
procedure for that.


mfG
--> stefan <--
Click to expand...
 
If the field name will change, but the columns are in the correct order, you
could build your query in code.

Public Function fnBuildSQL(TableName as string) as String

Dim tdf as dao.tabledef
Dim strSQL as string

Set tdf = currentdb.tabledefs(TableName)

strSQL = "SELECT [" & tdf.fields(1).Name & "], " _
& "[" & tdf.fields(2).Name & "], " _
& "[" & tdf.fields(3).Name & "] " _
& "FROM [" & TableName & "]"

fnBuildSQL = strSQL

End Function

Then, if you have a saved query that you want to call every time, then you
would do something like:

currentdb.querydefs("SavedQueryName").SQL = fnBuildSQL

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top