Update query changes when saved

[jamestufcfan]

I've created an update query on access, but when I save it then reopen the
design view, the field and table have changed.

I'm wanting to update from a table I've called TBL-STOCK, updating a field
called A-PUBSTK-NO. When I put the table as TBL-STOCK and the field as
A-PUBSTK-NO, the update query works fine. However, when I save it and then
reaccess it, the field now says Expr1: TBL-STOCK.A-PUBSTOCK-No and the table
is blank. However, this means that the query won't run and I have to correct
it back each time I want to use the query (which is annoying as I want to
input it into a macro).

Any ideas how to stop doing it doing this, or why it is doing it would be
greatly accecpted. Please keep it simple though, am new to access!

 

[Marshall Barton]

I've created an update query on access, but when I save it then reopen the
design view, the field and table have changed.

I'm wanting to update from a table I've called TBL-STOCK, updating a field
called A-PUBSTK-NO. When I put the table as TBL-STOCK and the field as
A-PUBSTK-NO, the update query works fine. However, when I save it and then
reaccess it, the field now says Expr1: TBL-STOCK.A-PUBSTOCK-No and the table
is blank. However, this means that the query won't run and I have to correct
it back each time I want to use the query (which is annoying as I want to
input it into a macro).

Any ideas how to stop doing it doing this, or why it is doing it would be
greatly accecpted. Please keep it simple though, am new to access!

Please post a Copy/Paste of the query's SQL view. I suspect
that the problem is ,inus sign in the names.

It is never a good idea to use non-alphanumeric characters
in names. I would change those names to TblStock and
APubstkNo. In the context of names, underscore is an
acceptable character so, if you have a strong preference for
an odd character, you could use TBL_Stock and A_PUBSTK_NO,
but I have an aversion to names in all caps.

 

[jamestufcfan]

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET [TBL -
STOCK].[A-PUBSTK-NO] = [Code to be substituted];

Changes to

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET
[TBL-STOCK].[A-PUBSTK-No] = [Code to be substituted];

Is it the spaces in the title of TBL - STOCK ?

 

[Marshall Barton]

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET [TBL -
STOCK].[A-PUBSTK-NO] = [Code to be substituted];

Changes to

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET
[TBL-STOCK].[A-PUBSTK-No] = [Code to be substituted];

Is it the spaces in the title of TBL - STOCK ?

Please post a Copy/Paste of the query's SQL view. I suspect
that the problem is ,inus sign in the names.

It is never a good idea to use non-alphanumeric characters
in names. I would change those names to TblStock and
APubstkNo. In the context of names, underscore is an
acceptable character so, if you have a strong preference for
an odd character, you could use TBL_Stock and A_PUBSTK_NO,
but I have an aversion to names in all caps.

 

[Marshall Barton]

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET [TBL -
STOCK].[A-PUBSTK-NO] = [Code to be substituted];

Changes to

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET
[TBL-STOCK].[A-PUBSTK-No] = [Code to be substituted];

Is it the spaces in the title of TBL - STOCK ?

While spaces can be problematic, I think the core issue is
the minus signs.

As I said before, you really should change those names so
they ony contain alphanumeric (and underscore) characters.

 

[John Spencer]

You know this looks like Autocorrect in Word where two spaces is automatically
replaced with one space. I've never seen the behavior in Access, but?

What version of Access?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET [TBL -
STOCK].[A-PUBSTK-NO] = [Code to be substituted];

Changes to

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET
[TBL-STOCK].[A-PUBSTK-No] = [Code to be substituted];

Is it the spaces in the title of TBL - STOCK ?

While spaces can be problematic, I think the core issue is
the minus signs.

As I said before, you really should change those names so
they ony contain alphanumeric (and underscore) characters.

 

[jamestufcfan]

I've got Access 2007. I removed the hyphen and that didn't work. However, I
tried removingh the spaces and that worked. Thanks for all your help on this.
I think I might be back asking more questions in the future!

You know this looks like Autocorrect in Word where two spaces is automatically
replaced with one space. I've never seen the behavior in Access, but?

What version of Access?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET [TBL -
STOCK].[A-PUBSTK-NO] = [Code to be substituted];

Changes to

UPDATE [Stock code convertions] INNER JOIN [TBL - STOCK] ON [Stock code
convertions].[Stock Table Code] = [TBL - STOCK].[A-PUBSTK-NO] SET
[TBL-STOCK].[A-PUBSTK-No] = [Code to be substituted];

Is it the spaces in the title of TBL - STOCK ?

While spaces can be problematic, I think the core issue is
the minus signs.

As I said before, you really should change those names so
they ony contain alphanumeric (and underscore) characters.