Parameter query? [Resolved]

[salvelinus]

I've got a parameter query based on one table. The field names in the query don't match the field names in the table.

DELETE [Main].Hospital AS Expr1, [Main].Hospital AS Expr2, [Main].[Hospital] AS Expr3
FROM Main
WHERE ((([Main].[Hospital])<>[Which Hospital?]));

Hospital isn't a field in Main. What's going on? The query apparently works when run in a DoCmd.OpenQuery statement.

[brucevde]

Main is a query which selects data from the database table. This query then uses/deletes the records returned by Main.

[salvelinus]

Well, there's no query called Main. The only thing called Main is a table, which doesn't have a field Hospital. The query I posted is called ClarianMainQuery
There's a similar query:

DELETE [Mod List].HosCode AS Expr1, [Mod List].HosCode AS Expr2, [Mod List].[HosCode]
FROM [Mod List]
WHERE ((([Mod List].[HosCode])<>[Which Hospital?]));

where Mod List is a table, but again, no HosCode field.

[brucevde]

Main and Mod_List could be aliases.

[salvelinus]

Maybe, but where would I find that out? It seems odd to give an alias to a table that is the same name as another table.
Needless to say, I didn't write this app. It's Access VBA using DAO; I'm used to ADO, although will have to learn .net

[brucevde]

Can't you open the database directly in Access?

[salvelinus]

Sure. That's how I know what tables and fields there are, and those fields aren't in those tables.
There's a lot of clutter in the code, and possibly in the db as well. Stuff was put in to deal with situations that no longer apply. But I'd think it would raise an error when the query was run.

[salvelinus]

I found the problem. The queries are only used for a specific client. Each time the program is run, old tables are deleted and new ones imported, which do contain those fields if that client is the one being worked on.
But if you didn't work on that client last, you'll have tables referencing a different client, which won't include those fields.
This is a pitfall of customizing a program for each individual client, but that's what we do here. Oh well.