Query issue I can't resolve

[kryptkitten]

Hi All,

I have something that's really funky happening with a query that for the life of me I can't seem to fix it or at least once I do it keeps reverting back. I can't seem to replicate what's causing the problem either which is not helping the frustration level at all.

So essentially here is the query as it should be

SELECT Reports.Departments, Reports.[Report Number], Reports.[Revision Number], Reports.Name, Reports.[Effective Date], Reports.Status
FROM Reports
WHERE (((Reports.Departments)="aph") AND ((Reports.Status)="effective")) OR (((Reports.Departments)="aph/ctl") AND ((Reports.Status)="effective"))
ORDER BY Reports.Departments;

Here is what it changes to:

SELECT Reports.Departments, Reports.Departments, Reports.Departments, Reports.Departments, Reports.Departments, Reports.Departments
FROM Reports
WHERE (((Reports.Departments)="aph" And (Reports.Departments)="effective")) OR (((Reports.Departments)="aph/ctl" And (Reports.Departments)="effective"))
ORDER BY Reports.Departments;

I continually go back and change it to the first one and save it and then somehow it randomly keeps changing back to the second query. Am I dealing with a corrupt database or something?

Help please this is really aggravating!

Thanks!

Stacy

 

[ByteMyzer]

Try the following:

1) Create a new database, then import all objects (except the problem query) from the old database into your new database.
2) In your new database, manually re-create the query.

See if the problem persists.

 

[kryptkitten]

Yeah I've tried that several times and it's still a problem. So really no idea what's going on. It's why I kind of suspect it's corrupted somehow.

 

[ByteMyzer]

What happens if you import ONLY the tables into your new database, then try re-creating the query?

 

[kryptkitten]

The same issue as the first query. Something keeps changing it and I can't figure out what or why!

 

[SOS]

You may also be suffering due to your table name being named an Access Reserved Word. Reports is a reserved word and so having a table named Reports can be a problem.

First of all, make sure AutoName Correct is turned off. Then go change the name of your table to something else like RReports or something. And then try it.

 

[CraigDolphin]

Are there any vba modules that set the SQL of that query programmatically? Maybe the sql is being rewritten when you perform some other task during use of the database. ???

 

[ByteMyzer]

Try:
1) Create a new database
2) Import ONLY the tables from your old database (no queries, no forms, no reports, no NOTHING else, just TABLES)
3) Create a new Query, in SQL View (Do NOT switch to Design View)
4) Paste the SQL from the query into the SQL View, then save the query (Again, do NOT switch to Design View before saving - see where I'm going here?)

Does the query work then?

 

[kryptkitten]

Okay so did go through and create queries not using design view and using strictly code. Also changed the name of the table to operational reports. So far so good in terms of no problems. *fingers crossed* Hopefully it stays that way. Gah the issues of inherited databases!

Thanks all!