Escaping & in field names?

[mclifford82]

Hello everyone,

I have a query that I am using as a source to append data into a linked Oracle table. One of the field names in the destination table is RT&T, but whenever I close the query window, it changes the table name to RT & T, and the query no longer works. If I run the query from within design view after removing the spaces, it runs. It's only after the query window is closed does access change the field name.

Now, aside from the fact that it's probably not a good idea to have an ampersand in a table name, how can I get Access to understand that there can't be spaces around the ampersand in the field name?

I've tried [RT&T], "RT&T", RT&&T, RT\&T and none of those work. There are other applications that rely on this table, so changing the field name is not a very good option for what seems to be an Access quirk.

Any help is greatly appreciated!

 

[SOS]

You would need to use square brackets: [RT&T]. You say you used square brackets but that should have worked. What is the actual SQL of the query?

 

[mclifford82]

I'll have to post back with the full query tomorrow, as it's at work and I don't have access to it at the moment.

I'll also retry the brackets, though I am fairly certain I did try that.

Append Query:

Field: RT&T
AppendTo: [RT&T]

No aggregation, just a straight-up append from one source to one destination.

Thanks again.

 

[SOS]

I'll have to post back with the full query tomorrow, as it's at work and I don't have access to it at the moment.

I'll also retry the brackets, though I am fairly certain I did try that.

Append Query:

Field: RT&T
AppendTo: [RT&T]

No aggregation, just a straight-up append from one source to one destination.

Thanks again.

You would also need the brackets in both
Field: [TableName].[RT&T]
AppendTo:[TableName2].[RT&T]

 

[gemma-the-husky]

if its oracle, could you get your oracle dbs manager to give you a view of this, with the RT&T aliased as RTT maybe - that might make life easier in the long run, as long as you can use the view in access

 

[mclifford82]

Here is an example that illustrates the dilemma. I have tried each of your suggestions, inlcuding [Tablename].[Fieldname] for both Field and AppendTo, as well as bracketing the names. I've even made the edits in SQL view.

When you open the query, you'll note that field a&b shows in the destination as a & b.

It would appear to me that Access just doesn't allow ampersands in a destination field name without spaces around it.

Thanks again! And I'll talk to my sup about getting an Oracle view setup.

 

[SOS]

That is interesting and I've never seen that before. Thanks for the sample. It does exactly as you say and I've yet to figure out how to stop it. I guess that is one good example as to why special characters in names are not good in Access (I understand about it being in Oracle that way - and that also is bad naming so I guess a view is the only way).

 

[mclifford82]

Actually, upon further reflection, I came to wonder if a View would work either, as those are non-updateable (that I know of). My supervisor is going to change the Oracle table structure to remove the ampersand, so we're going to solve it that way.

Still, I find it odd that Access allows fieldnames with ampersands if they appear as a source for the query, but not when they are the destination.

Thanks again.

 

[The_Doc_Man]

What is probably going on is that Access expression evaluation is seeing the & as an operator, not as a field name, and auto-correct is probably on. So it auto-corrects your tight little expression RT&T to RT & T, looking of course to concatenate [RT] and [T]

Which means it wasn't isolated enough to block the expression evaluator. Which usually in turn means "not enough quotes."