Ms access changing field name in query (1 Viewer)

lwarren1968

Registered User.

Local time

Today, 14:57

Joined

Jan 18, 2013

Messages

77

• Jan 31, 2019

• #1

FIELD NAME is "B"
Need to change it to "option:value", however the colon is causing a invalid syntex error?

option:value:B

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 14:57

Joined

Aug 30, 2003

Messages

36,124

• Jan 31, 2019

• #2

Untested but try

[option:value]:B

 

jdraw

Super Moderator

Staff member

Local time

Today, 17:57

Joined

Jan 23, 2006

Messages

15,379

• Jan 31, 2019

• #3

Please tell us more.

You can alias a field in a query

Code:Copy to clipboard

Select fld1, fld2 [COLOR="DarkOrange"]as MyCustomName[/COLOR] from MyTable

as MyCustomName represents an alias in the SQL of the query.

Access does not like special characters in field names. Use alpha and numerics and underscore "_" only.
Hope it helps.

 

Last edited: Jan 31, 2019

lwarren1968

Registered User.

Local time

Today, 14:57

Joined

Jan 18, 2013

Messages

77

• Jan 31, 2019

• #4

does not work as i get an "not a valid name"

 

lwarren1968

Registered User.

Local time

Today, 14:57

Joined

Jan 18, 2013

Messages

77

• Jan 31, 2019

• #5

the fleld B needs to reflect option:value

SELECT SW_LABEL_VALUE_UNION_QRY.A AS attribute_code, SW_LABEL_VALUE_UNION_QRY.B AS optionvalue
FROM SW_LABEL_VALUE_UNION_QRY;

 

Minty

AWF VIP

Local time

Today, 22:57

Joined

Jul 26, 2013

Messages

10,368

• Jan 31, 2019

• #6

Try

"Silly:Name": B

It really doesn't like using the : and I'm certain it will trip you up later in the project.
IMVHO

 

lwarren1968

Registered User.

Local time

Today, 14:57

Joined

Jan 18, 2013

Messages

77

• Jan 31, 2019

• #7

quotation marks are present in the field name when using

returns "Silly:Name"

 

Minty

AWF VIP

Local time

Today, 22:57

Joined

Jul 26, 2013

Messages

10,368

• Jan 31, 2019

• #8

If you are outputting this in a report or export query you can use the caption property if that would help.

The : is an illegal character as far as access (and SQL and almost any other dbms) is concerned, so I can't think of any way around it.

 

lwarren1968

Registered User.

Local time

Today, 14:57

Joined

Jan 18, 2013

Messages

77

• Jan 31, 2019

• #9

ok, if adding [] in the sql its self then it works.

SELECT SW_LABEL_VALUE_UNION_QRY.A AS attribute_code, SW_LABEL_VALUE_UNION_QRY.B AS [option:value]
FROM SW_LABEL_VALUE_UNION_QRY;

Thank all for helping!