exporting with different field names

  • Thread starter Thread starter JIM H.
  • Start date Start date
J

JIM H.

Hello,

I am using the following command to export a table to an
excel file. DoCmd.TransferSpreadsheet acExport,
8, "QuerySelect", fileNamePath, False, ""
QuerySelect is a select query I defined in Access.
I need to export filed names not as they were but with new
aliases, how can I do that?
Thanks,
Jim.
 
Use a query that has calculated fields with your names in place of the
original fields.

Export the query.
 
How do I do that? I am new in Access.
-----Original Message-----
Use a query that has calculated fields with your names in place of the
original fields.

Export the query.

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
 
JIM H. said:
How do I do that? I am new in Access.
Click to expand...

Say you have a table named StudentMaster.
SM_FirstName
SM_MiddleName
SM_LastName
and so on

In the query, do the following in each column.

FirstName: SM_FirstName
MiddleName: SM_MiddleName
LastName: SM_LastName

you can also do something like this.

FullName: SM_FirstName & " " & SM_MiddleName & " " &
SM_LastName

Then when you use the query in the export, the field will have
alias field names
and not the field names from the table.

Ron
 
Hello,
Thanks for the reply. Where exactly I should do that. This
query is defined in the query tab of Access 2002. If I
type FirstName: SM_FirstName in the field area of the
query and run the query it gives me a questions 'enter the
parameter value': SM_FirstName.
What am I doing wrong?
Thanks,
Jim.
 
JIM. H. said:
Hello,
Thanks for the reply. Where exactly I should do that. This
query is defined in the query tab of Access 2002. If I
type FirstName: SM_FirstName in the field area of the
query and run the query it gives me a questions 'enter the
parameter value': SM_FirstName.
What am I doing wrong?
Thanks,
Jim.
Click to expand...

What I sent was an example. You should create a query of the
table you want to export and use the alias field names in the
new
query. This will give you different column names in the
exported
file from the column names in the Access table.

Ron
 
Ron,
I understand that, I also gave an example to show you that
if I type alias in the field area in the query definition,
it asks me a value for alias whehn I run query. I think we
are not on the same page. How do you define your query and
where do you exactly put the alias in the query
definition, can you specifically tell me?
Thanks,
Jim.
 
If you're being asked by ACCESS for the value of the "alias", then the field
name that you're using (the one to the right of the colon) may not be
spelled correctly or is not a field in any of the tables that are in the
query.
 
Hi Ken,
I think my problem is I do not know where to type : and
alias, is it SQL view or design view? Can you give me a
link that talks about this in the internet.
Thanks,
Jim.
 
Let's try an experiment to get you started.

Copy the following SQL statement (it's a generic one, so you'll be editing
in a moment):

SELECT Field1, Field2 As NewName
FROM TableName;

Now, create a new query. Click Close on the popup window that allows you to
select tables. Then, click on the dropdown arrow on the query view button on
the toolbar (top left corner), and click on SQL.

Paste the SQL statement into the window. Change TableName to the name of
your table. Change Field1 to the name of any field in that table. Change
Fieldd2 to the name of any other field in the table.

Change to datasheet view. You'll see that the name displayed at the top of
the second column will be NewName, not the name of the second field that you
put into the SQL.

Now, change to design view. You'll see in the second column on the grid in
the "Field:" cell how this "alias" is set up and how you'd do what has been
suggested elsethread.

Let us know how it works.
 
Thanks Ken and Ron;
I figured out my mistake. I was doing fieldName: alias
instead of alias: fieldName.
Thanks again. it is working fine now.
bye
Jim.
 
Back
Top