Table links break when query saved 1

[Nelviticus]

We have some fairly complicated queries where the links between tables are defined within the query. When we save some of these queries, the next time they are opened we get a message similar to the following and some of the links are broken:

"Microsoft Access can't represent the join expression [LOOKUP-NEWSCHEMECODE].[start date]=UPMSCHEMEHIST.DATEEFFECTIVE in Design view.

* One or more fields may have been deleted or renamed.
* The name of one or more fields or tables specified in the join expression may be misspelled.
* The join may use an operator that isn't supported in Design view, such as > or <."

Both of the linked fields are defined as Date/Time. None of the three conditions mentioned apply - fields haven't been deleted or renamed, nothing has been misspelled and there are no unsupported operators - just an equals sign.

We open up the query, get the message, drag a field from one table to another to re-create the link, save and close the query, then open it again and we get the message. Any idea what the problem is? We're using Access 2002 SP3.

Thanks

Nelviticus

 

[r937]

the problem is, access is lame

you will have to make sure you always open this query in SQL View

r937.com | rudy.ca

 

[Nelviticus]

Hi there and thanks for replying. Are you saying that we're doing nothing wrong and this is just a known Access bug?

Regards

Nelviticus

 

[r937]

you are doing nothing wrong

complex joins, though, can sometimes be avoided by use of a query, i.e. join a table to a query where the query defines the constraints


r937.com | rudy.ca

 

[Nelviticus]

OK, thanks for the info.

Regards

Nelviticus