Query column keeps reappearing after delete

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
T

Tony Girgenti

When i do this:
"DoCmd.TransferText acExportDelim, , strcQueryName, strFile"

I get a column of ExtPrice that i do not want in the output file. If i go
to query design and delete the column, then run the transfertext, the output
file contains the field that i deleted and the query design shows the field
again even though i deleted it.

How do i keep that field from appearing the output file ?

Any help appreciated.

Tony
 
Tony Girgenti said:
When i do this:
"DoCmd.TransferText acExportDelim, , strcQueryName, strFile"

I get a column of ExtPrice that i do not want in the output file. If i go
to query design and delete the column, then run the transfertext, the output
file contains the field that i deleted and the query design shows the field
again even though i deleted it.

How do i keep that field from appearing the output file ?
Click to expand...
Hi Tony,

In query designer, try clicking on Properties icon
in top menu and change "Output All Fields"
to "No"

I didn't test, but have good hunch that will
solve your problem.

Good luck,

Gary Walter
 
Hi Gary.

I fanally got around to looking at that. It is already set to 'no'

Any other ideas ?

Thanks,
Tony
 
Hi Tony,
Any other ideas ?
Click to expand...

Just the "usual"
1) What version of Access (and ServicePack)?
2)What version of Windows (and ServicePack)?
3) What version of MDAC?
4) Ran Compact and Repair?
5) What is the SQL stmt for your query?
Select it in query designer SQL mode,
copy to clipboard, then paste in reply.
6) And to verify we are on same page:

You cut the field from your query.
You verified "Output All Fields" set to No.
Then *saved* the query and closed it.
Ran DoCmd.... in code.
Then opened the saved query back up
and the field was back in the query?

What happens when you remove the field,
save and close the query,
then open it back up again w/o running DoCmd...?
 
Hello Gary. Thanks for helping with this.
I'd like to answer all of your questions.

1)Microsoft Access 2002(10.4302.4219)SP-2

2)Microsoft Windows Professional version 2002 service pack 1

3)You'll have to tell me where to find this

4)Several times at different points of testing

5)SELECT Trim(customer.name) AS TrimNam, Trim(customer.address1) AS
TrimAdrs_1, Trim(customer.address2) AS TrimAdrs_2, Trim(customer.city) AS
TrimCity, Trim(customer.state) AS TrimState, Trim(customer.zipcode) AS
TrimZip_cod, Trim(customer.emailaddress) AS TrimEmail_adrs,
Trim(customer.customernumber) AS TrimNum, tixhistlin.ItemNumber AS
itemnumber, Max(tixhisthdr.PostingDate) AS postdate,
Sum(tixhistlin.ExtendedPrice) AS ExtPrice
FROM (customer INNER JOIN tixhisthdr ON customer.CustomerNumber =
tixhisthdr.CustomerNumber) INNER JOIN tixhistlin ON tixhisthdr.TicketNumber
= tixhistlin.TicketNumber
GROUP BY Trim(customer.name), Trim(customer.address1),
Trim(customer.address2), Trim(customer.city), Trim(customer.state),
Trim(customer.zipcode), Trim(customer.emailaddress),
Trim(customer.customernumber), tixhistlin.ItemNumber
ORDER BY Trim(customer.name);

6)You are absolutely correct on this one

Thanks Gary
Have a Happy New Year

Tony
 
Sorry Gary, I forgot to answer this question
What happens when you remove the field,
save and close the query,
then open it back up again w/o running DoCmd...?
Click to expand...

When i do this, the field does not reappear. It only reappears if i run the
DoCmd.OpenReport.

Tony
 
Hi Tony,

okay.......

I take it from all the "TRIM's"
that you are dealing with one or
more ODBC tables?

If so, what kind of ODBC and
which table(s)?

And if so, is it possible to create a "View"
in the ODBC db so you do not have to
use VB functions on the fields..plus maybe
the view will return only the fields you want?
And maybe there is some setting in DNS?

Are these heterogeneous Joins?
(one or more Access tables joining
one or more ODBC tables)

My limited experience with ODBC dbs
(other than SQL Server) has lead to my
feeling that it is always best to get what
I want from the db into an Access table
(no heterogeneous joins, no VB functions),
then "do something."

For example, if only table "customer" is ODBC
linked table, I probably would create a table in
Access to store what I want to pull from
"customer" with an append query, "massage it,"
then use that temp table in your query below.

I'm reasonably sure that you won't get
this behavior with Access-only tables
(I tested in Access2000 and Access2002).

I even linked to a view in SQL Server 2000
and did not get this behavior (but I did not
use any VB functions, nor attempt to include
Access tables in the query).

Also, I forgot to ask which field you are
deleting. Is the SQL you gave, the one before
the delete, or after you run the transfer.
Could you provide the "other one" also,
and identify which is which?

Also, just to make sure, have you updated
Jet 4.0 to SP8? I doubt this will help, but...
I work in a Win2000pro/Access200x world,
and just went to Windows Update and installed
SP8 from "non-critical" updates.

As far as MDAC, at

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

there is a link to "Component Checker" tool
which checks up to 2.7 (not 2.8?).
I'm not saying MDAC has anything to do
with your dilemna, and their warning about
testing newer versions in a test environment
is worth heeding!


Sorry if not much "exact help."

Gary Walter
 
Back
Top