select tablename.fieldname yet error 3079

[Bas Cost Budde]

This is my query:

SELECT DISTINCT relatie.relatieID
FROM Relatietype INNER JOIN relatie ON Relatietype.relatietypeID =
relatie.relatietypeID
WHERE (((Relatietype.omschrijving)='promotie') AND
((Relatietype.status)<>'weg'));

These are my tables (I left out some text fields)
Relatie(relatieID,naam,relatietypeID,opmerking,sta tus,username,userdate)
RelatieType(relatietypeID,omschrijving,flags,statu s,username,userdate)

Reference exists on relatietypeID.

Access now complains that the field [status] can refer to more than one
table in the FROM clause. All right--that's why I put in the table name
[relatietype], right? I completely fail to see what is wrong.

Bas Cost Budde
Holland

If you want to use my email, the q should be a t

[Allen Browne]

Hello Bas. Haven't seen you around the groups recently. Good to hear from
you again.

Your query statement looks fine to me too. It looks like Access is confused.
If this is Access 2000 or later, my money would be on Name AutoCorrect as
the cause of the confusion. There's well over a dozen bugs listed at:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html
and we are still discovering more.

So, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact.

If that does not solve the problem, please post back again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:cm*********@news2.solcon.nl...

This is my query:

SELECT DISTINCT relatie.relatieID
FROM Relatietype INNER JOIN relatie ON Relatietype.relatietypeID =
relatie.relatietypeID
WHERE (((Relatietype.omschrijving)='promotie') AND
((Relatietype.status)<>'weg'));

These are my tables (I left out some text fields)
Relatie(relatieID,naam,relatietypeID,opmerking,sta tus,username,userdate)
RelatieType(relatietypeID,omschrijving,flags,statu s,username,userdate)

Reference exists on relatietypeID.

Access now complains that the field [status] can refer to more than one
table in the FROM clause. All right--that's why I put in the table name
[relatietype], right? I completely fail to see what is wrong.

Bas Cost Budde
Holland

If you want to use my email, the q should be a t

[Bas Cost Budde]

Allen Browne wrote:

Hello Bas. Haven't seen you around the groups recently. Good to hear from
you again.

Your query statement looks fine to me too. It looks like Access is confused.
If this is Access 2000 or later, my money would be on Name AutoCorrect

It's 97. I was wondering wether I included enough information :-)

There IS AutoCorrect in my Tools menu, and that DOES solve the problem.
Not that I understand how or why...

"My car won't shift to 5th gear"
"Have you got a yellow suitcase in the back?"
"Why, yes."
"Try moving it to the left side of the compartiment"

....

[David W. Fenton]

Bas Cost Budde <b.*********@heuvelqop.nl> wrote in
news:cm**********@news2.solcon.nl:

Allen Browne wrote:

Hello Bas. Haven't seen you around the groups recently. Good to
hear from you again.

Your query statement looks fine to me too. It looks like Access
is confused. If this is Access 2000 or later, my money would be
on Name AutoCorrect

It's 97. I was wondering wether I included enough information :-)

There IS AutoCorrect in my Tools menu, and that DOES solve the
problem. Not that I understand how or why...

"My car won't shift to 5th gear"
"Have you got a yellow suitcase in the back?"
"Why, yes."
"Try moving it to the left side of the compartiment"

My guess is that there was some other saved property referring to
[status], like a sort order or filter, that doesn't show up in SQL
view. My bet is that if you'd cut and pasted the raw SQL into a new
query it would have worked.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bas Cost Budde]

David W. Fenton wrote:

My guess is that there was some other saved property referring to
[status], like a sort order or filter, that doesn't show up in SQL
view. My bet is that if you'd cut and pasted the raw SQL into a new
query it would have worked.

Strong suspicion! I have indeed noticed that this causes problems now
and then.

Here, though, I have a query that I just created using CreateQuerydef,
and I can't find any property like that.

Copying and pasteing works. I use the query in an automated process,
though, so this is not a ready solution.

Please note that unchecking AutoCorrect worked, but only initially. I
seem to have changed something. Next time the query was created, it got
me the same error.