Access is changing my SQL

[bernie10]

Hey everyone,
Access appears to be intermittedly rewriting one of my queries. I write the query one way and it works fine for like half a day (yielding the correct results), but then for no apparent reason, I'll open the query and it will be changed, yielding a completely different set of records. Perhaps I have made some mistake in my SQL (I used the Access query design window to create the query for the most part)? I hate to ask somebody to wade through all this SQL, but I would really appreciate the effort. The part Access is changing is in the WHERE part.

So here's is my original SQL:

SELECT DISTINCT Transformers.Manufacturer, Transformers.ManufacturerDesignID, Transformers.CommodityType, Transformers.[Unit Price], Transformers.[Load Loss], Transformers.[No Load Loss], Transformers.[Date Added], Transformers.Comments, Transformers.[Transformer Category], Transformers.[Primary Voltage], Transformers.[Secondary Voltage], Transformers.KVA, Transformers.Other, Transformers.[GPC Com Num], Transformers.[APC Com Num], Transformers.[MPC Com Num], Transformers.[Gulf Com Num], Transformers.[SAV Com Num], Transformers.[SIIS Com Num], Transformers.[A Factor], Transformers.[B Factor], Transformers.TOC, CorrespondingValues.[Corresponding TOC], Transformers.[Hard Dollar TOC], Transformers.[Distance from Origin], CorrespondingValues.[Lowest Distance from Origin], [Distance from Origin]-[Lowest Distance from Origin] AS Difference, [TOC]-[Corresponding TOC] AS TOCDiff, [Unit Price]-[Corresponding Unit Price] AS PriceDiff, IIf(([Unit Price]-[Corresponding Unit Price])<>0,([TOC]-[Corresponding TOC])/([Unit Price]-[Corresponding Unit Price]),0) AS Ratio
FROM CorrespondingValues INNER JOIN Transformers ON CorrespondingValues.CommodityType = Transformers.CommodityType
WHERE (((Transformers.[Transformer Category])=[Forms]![Evaluate]![TransTypeQuery]) AND (([Unit Price]-[Corresponding Unit Price])<=0) AND ((IIf(([Unit Price]-[Corresponding Unit Price])<>0,([TOC]-[Corresponding TOC])/([Unit Price]-[Corresponding Unit Price]),0))>=-1.02))
ORDER BY [Distance from Origin]-[Lowest Distance from Origin];


And here is what Access appears to be changing it to:

SELECT DISTINCT Transformers.Manufacturer, Transformers.ManufacturerDesignID, Transformers.CommodityType, Transformers.[Unit Price], Transformers.[Load Loss], Transformers.[No Load Loss], Transformers.[Date Added], Transformers.Comments, Transformers.[Transformer Category], Transformers.[Primary Voltage], Transformers.[Secondary Voltage], Transformers.KVA, Transformers.Other, Transformers.[GPC Com Num], Transformers.[APC Com Num], Transformers.[MPC Com Num], Transformers.[Gulf Com Num], Transformers.[SAV Com Num], Transformers.[SIIS Com Num], Transformers.[A Factor], Transformers.[B Factor], Transformers.TOC, CorrespondingValues.[Corresponding TOC], Transformers.[Hard Dollar TOC], Transformers.[Distance from Origin], CorrespondingValues.[Lowest Distance from Origin], [Distance from Origin]-[Lowest Distance from Origin] AS Difference, [TOC]-[Corresponding TOC] AS TOCDiff, [Unit Price]-[Corresponding Unit Price] AS PriceDiff, IIf(([Unit Price]-[Corresponding Unit Price])<>0,([TOC]-[Corresponding TOC])/([Unit Price]-[Corresponding Unit Price]),0) AS Ratio
FROM CorrespondingValues INNER JOIN Transformers ON CorrespondingValues.CommodityType = Transformers.CommodityType
WHERE (((Transformers.[Transformer Category])=[Forms]![Evaluate]![TransTypeQuery]) AND (([Unit Price]-[Unit Price])<=0) AND ((IIf(([Unit Price]-[Unit Price])<>0,([TOC]-[TOC])/([Unit Price]-[Unit Price]),0))>=-1.02))
ORDER BY [Distance from Origin]-[Lowest Distance from Origin];

Anybody have any ideas?

Thanks in advance,
Collen

 

[bernie10]

Can anybody at least offer some trouble shooting suggestions on this, even if there aren't any apparent problems in my SQL? I'm really at a loss as I've never seen something like this before. Any thoughts or suggestions would really be appreciated.

Collen

 

[Golom]

If its happening as you say then you've probably found a bug in Access.

I know it's not easy to change column names in a production environment but you might try renaming columns to eliminate the column names with spaces in the name (at least for those columns in the WHERE clause). If you need to see the names with spaces then alias the column name in the table with the "contains spaces" equivalent.

Just as a test, run a make-table to some new table name and rename the columns in the new table then run the query off the new table.

 

[ProgramError]

could 'corresponding' be a keyword??? Hence its removal. Try changing the field name to something else or put in an underscore instead of a space.


Program Error
Programmers do it one finger at a time!

 

[TheAceMan1]

How are ya bernie10 . . . . .

The key for me here is that [blue]no errors are are raised[/blue] when the query is [blue]compiled[/blue]. Nor do they occur at [blue]runtime[/blue]. Certainly sounds like a bug . . . . [blue]I'd try copying the origional SQL into a new query and see what happens[/blue] (when you open the new query in design view, [blue]don't select any tables![/blue]).

See Ya! . . . . . .

 

[bernie10]

Thanks for the responses.

I'm thinking it looks like a bug as well. I took my query and broke it down into two consequitve queries instead, and so far Access hasn't changed it. Maybe I worked around the bug?

Thanks again for all the help!

Collen

 

[faxpay]

bernie10,

Can I suggest that you put the SQL statement into the db with the CREATE PROCEDURE method. It will not appear as a query object but is stored internally in the database. This might keep the statement from changing.

See the CREATE PROCEDURE statement in Microsoft Access help.

faxpay, Tom

 

[bernie10]

I'll have to try that, faxpay. Thanks for the suggestion.