Field Names replaced by Expr1

[Neilbees]

Hi all

I've searched to try and find the answer this this but with no luck - probably not searching for the right keywords.

Anyway, hope someone can help. I built a pretty standard Access 2007 database at work, front end back end set up, all been working fine. Been on a weeks holiday, come back and find a lot of the queries are messed up.

Basically in a lot of cases the field names in queries have been prefaced with Expr1. So ClientName has become Expr1: ClientName. So that leads to users getting prompted for parameters when they try and run reports. Has anyone come across this before, know what causes it, know how to solve it and stop it happening again? (I don't ask for much!)

Thanks as always.

 

[DCrake]

Sounds like your client table has either been renamed, deleted or dropped from the front end.

If all users are experiencing the same problem check your back end for the client table.

 

[Neilbees]

Hi David

Thanks for your quick response. The tables are still there. There are other queries that look at ClientName that are not erroring in this way.

 

[DCrake]

So now we know the table is still there, is the fieldname ClientName still there?

Does it appear in the field list for the client table in your query?

 

[Neilbees]

Yep, the fields are still there (this isn't a problem specific to ClientName - that was just one example). Where the query is querying a table the field appears in the field list. Where a query is querying a query, the original query is working fine.

 

[DCrake]

Try recreating the offending query and see what happens

 

[Neilbees]

No problems - worked fine. Also, just deleting the Expr1 prefix in the queries results in the queries working again.
So it seems it's not so much an issue of fixing the queries, it's more why did it happen and will it happen again?!?!?

 

[Neilbees]

Hi all
I know it's bad form to bump your own post but the issue has occurred again and I am just wondering if anyone else has ever experienced this?

It's as if the link to the back end is getting lost, causing the queries to default to this Expr1, Expr2 etc but when I go in and check the connection is there, the tables are there, the field lists in the queries are there.

Has anyone any idea what might be causing this and how it might be resolved?

 

[gemma-the-husky]

you get a expr1, if you duplicate the same field within a query. Is that a possibility?

 

[DCrake]

Are you using any process that relinks your back end? It could be that someone is running a query during this process.

Does each user have their own copy of the front end on hteir machine?

 

[Neilbees]

Thanks for replies and interest.
GTH - the issue doesn't seem to be to do with duplicate fields.
David - not that I'm aware of. The issue is the same for all users - same errors. There is no sophisticated stuff - just a standard front end back end link.

 

[DCrake]

You did not answer the second question.

Does each user have theire own front end on their machines?

 

[Neilbees]

Sorry David - yes all users have their own front end on their machines.

 

[spiketoo]

Not to raise the dead here but this also has happened to moi. Seems to be random and not sure the extent as yet. Just went to the Expr fields and reselected the actual field name. I did get a corrupted DB trying to link a subreport to main report earlier and had to copy all into a new DB - just FE not the BE. Kept getting funky error stating I needed to create sort group or expression in group footer - really strange. Not sure if these Exprs are resultant of the corruption or not.

 

[JayLobos]

We have the same issue, but with a little variance...

We have temporary tables that are build on the fly, but some of these temporary tables have queries using them. Because these tables do not exist until right before the query is run or opened and are cleaned up after the process completes, they never exist for any extended period of time. Anyone have any idea how to stop Access 2010 from trying to autocorrect/alias these fieldnames to Expr1, Expr2, Expr3, etc...

We are praying for a setting that we can tweak, but I have found nothing yet.

Thanks for the help,

Jason

 

[boblarson]

Normally if it names a field Expr1, etc. it is because of one of two things.

1. You already have that field in the query and this is a duplicate of the same field. If you do that it will use the Expr1, Expr2, naming.

2. You didn't provide a name for your field which is an expression. In order to avoid the Expr1, Expr2, etc. in that you need to provide a name. So, if you are in Design View, you would use

MyNewNameHere:Expression Goes Here
(for example EstimatedReturnDate:IIf([RentalDate] Is Not Null And DateAdd("d", 7, [RentalDate])< Date(), "OverDue", "On Time")

If in SQL View

IIf([RentalDate] Is Not Null And DateAdd("d", 7, [RentalDate])< Date(), "OverDue", "On Time") As EstimatedReturnDate