Update query does not recognize field

[ddsnydersnyder]

A little background, I am truly baffled on this one:

I have a simple update query with two tables with inner join. I am trying to update a field in one of the tables. After I save the query and reopen it, it does not recognize the field and labels it as "expression1". This field is not-updateable, so the update does not work.

I have attached two screenshots.

I am running Access 2007 and underlying tables are stored on MS SQL server. I am using Enterprise Manager to manage the tables.

 

[John Big Booty]

Your problem is being caused by the fact that you have spaces and a special character, in the form of a minus sing, embedded in your table name. This has caused Access to think that it is dealing with an expression rather than a table name.

Remove all spaces and other special characters from your table and other object and control names.

Consider implementing a naming protocol along the lines of; TBL_TableName, FRM_FormName, QRY_QueryName etc. limit yourself to alpha and numeric charters and the underscore (_) this will make it quite clear, once you start writing code, what type of DB object you are referring to in your code.

 

[boblarson]

Also, when referring to a table and field do not use the bang (!).

[Booking Master].[Onhands No]

I do agree with JBB that you should avoid spaces and special characters in names of fields or objects.

 

[ddsnydersnyder]

makes sense - thanks. I try to do the above in my own DB's, but I inherited this one, so changing it now would take a solid week:-(

Thanks again!

 

[boblarson]

Well, technically the bang will work but if the backend ever moves to SQL Server it won't work.