Viewing and changing SQL

[aziz rasul]

When I use the following code to extract the SQL statement of a query

Set qdf = CurrentDb.QueryDefs("qryTemp1C")
strRecordSource = qdf.SQL
Set qdf = Nothing

The SQL statement doesn't match the SQL when I open it in the database window??

For example I get

INSERT INTO tblReasonCodeVolumes ( category, [Account not billing 0-6 mths], [Account not billing 6mths +], [Agg/ De Agg - Meter - Gas], [Agg/ De Agg - Site - Elec], [Agg/ De Agg - Site - Gas], [Billing frequency SAP], [Domestic transfer price amendment], [Dual Billing], [Firm reads not used], [Letter/email request], [Meter /Supply location query], [Meter Exchange (not updated/ incorrect)], [Meter -Status/faulty/mis match- Elec], [Meter -Status/faulty/mis match- Gas], [No account], [Post Gas Emergency], [Rebilling>6months required], [Sales price not applied], [Site visit raised- for rebill only], [Supply address update], [Transposed Readings] )
SELECT qryTemp1B.[Activity type/Category], qryTemp1B.[Account not billing 0-6 mths], qryTemp1B.[Account not billing 6mths +], qryTemp1B.[Agg/ De Agg - Meter - Gas], qryTemp1B.[Agg/ De Agg - Site - Elec], qryTemp1B.[Agg/ De Agg - Site - Gas], qryTemp1B.[Billing frequency SAP], qryTemp1B.[Domestic transfer price amendment], qryTemp1B.[Dual Billing], qryTemp1B.[Firm reads not used], qryTemp1B.[Letter/email request], qryTemp1B.[Meter /Supply location query], qryTemp1B.[Meter Exchange (not updated/ incorrect)], qryTemp1B.[Meter -Status/faulty/mis match- Elec], qryTemp1B.[Meter -Status/faulty/mis match- Gas], qryTemp1B.[No account], qryTemp1B.[Post Gas Emergency], qryTemp1B.[Rebilling>6months required], qryTemp1B.[Sales price not applied], qryTemp1B.[Site visit raised- for rebill only], qryTemp1B.[Supply address update], qryTemp1B.[Transposed Readings]
FROM qryTemp1B;

but from the SQL window I get

INSERT INTO tblReasonCodeVolumes ( category, [Account not billing 0-6 mths], [Account not billing 6mths +], [Agg/ De Agg - Meter - Gas], [Agg/ De Agg - Site - Elec], [Agg/ De Agg - Site - Gas], [Billing frequency SAP], [Domestic transfer price amendment], [Dual Billing], [Firm reads not used], [Letter/email request], [Meter /Supply location query], [Meter Exchange (not updated/ incorrect)], [Meter -Status/faulty/mis match- Elec], [Meter -Status/faulty/mis match- Gas], [No account], [Post Gas Emergency], [Rebilling>6months required], [Sales price not applied], [Site visit raised- for rebill only], [Supply address update], [Transposed Readings] )
SELECT qryTemp1B.[Activity type/Category], qryTemp1B.[Account not billing 0-6 mths], qryTemp1B.[Account not billing 6mths +], qryTemp1B.[Agg/ De Agg - Meter - Gas], qryTemp1B.[Agg/ De Agg - Site - Elec], qryTemp1B.[Agg/ De Agg - Site - Gas], qryTemp1B.[Billing frequency SAP], qryTemp1B.[Domestic transfer price amendment], qryTemp1B.[Dual Billing], qryTemp1B.[Firm reads not used], qryTemp1B.[Letter/email request], qryTemp1B.[Meter /Supply location query], qryTemp1B.[Meter Exchange (not updated/ incorrect)], qryTemp1B.[Meter -Status/faulty/mis match- Elec], qryTemp1B.[Meter -Status/faulty/mis match- Gas], qryTemp1B.[No account], qryTemp1B.[Post Gas Emergency] AS Expr1, qryTemp1B.[Rebilling>6months required], qryTemp1B.[Sales price not applied], qryTemp1B.[Site visit raised- for rebill only], qryTemp1B.[Supply address update], qryTemp1B.[Transposed Readings] AS Expr2
FROM qryTemp1B;

How do I get the latter SQL statement by using VBA?

In essence I want to change the SQL statement so that I can remove the unrecognised fields, in this case it happens to be

[Post Gas Emergency]
[Transposed Readings]

but may be other fields in the future.

Please note I am aware that field names should ideally not have spaces but this is beyond my control and toooo long to explain.

 

[LPurvis]

The problem is almost certainly that this really is the SQL definition of your QueryDef.
When Access opens a query in design view, it re-parses it. The QBE is a wonderful thing - SQL Server more or less copied it conceptually for View creation back in SQL Server 7. But it works as all computer related tasks - algorithms.
It has rules for making a SQL statement from the graphical interface of the tables layout. It does brilliantly - but the price originally paid is interferance.
If you write a SQL statement then Access parses it - which can result in reformatting. But dare you view the QBE grid then it applies those algorithms.

In other words - once you open the query, it then appears as you see. But in its previous saved state, you read its definition correctly.
Reparsing query definitions is something I'd like to have seen as optional for some time now. (For example passthroughs are left entirely unmolested and unparsed... I'm not asking for that - but the option to not have queries reformatted upon viewing the defintion. Perhaps a "Disallow QBE view" property.)

Cheers.