Link to home
Start Free TrialLog in
Avatar of Dustin Stanley
Dustin Stanley

asked on

MS Access Define a New Field Name In A Query When Special Characters Are Used In The Field Name

I know in a Query I can define the new field name with : as in New Name:

But what if the new name has : in it. Here is an example I ran across. And I have no choice but to use this Field Name

ShippingService-1:Cost

I have tried

"ShippingService-1:Cost": "0"

Thanks.
SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you can other special characters but not the :

ShippingService-1/Cost: "0"

or


ShippingService-1;Cost: "0"
Avatar of Dustin Stanley
Dustin Stanley

ASKER

It says in valid name when I use [ShippingService-1:Cost]: "0"

Rey I have no choice. I am importing into a area and that is what THEY say has to be the header name.
Also when I use "ShippingService-1:Cost" it shows just that as the field name. "ShippingService-1:Cost"

But if i remove the Quotes then it says invalid.
who are THEY? and what is the file that need to have the header that you are referring to?
are you exporting the query to a file?
They is a third party. Yes it is a Access Products Export to CSV File.
OK.
You can not use the ":" as part of the header name in a query - you have to accept this.

what you can do is use other special character like I posted above.
export the query to csv file.
modify the name of the header after the export.
Rey your gonna make me cry! I did the Find Replace after exporting but this is sad. But why does it except "ShippingService-1:Cost" and show it just like that "ShippingService-1:Cost"?  It makes it seem almost like there is a way. The " makes it literal. But then it includes the " in the final result. Very Confusing!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
aikmark can you explain what you mean exactly please. What is the DependentID , LastName, and First Name.

What is the final result in your situation?

Thanks.

I have to leave work tonight but I will work back on it in the morning.

There are several of these header names that include : so this is kinda important. If I can find the best work around for one then they should all work the same.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That was a test table from a different question.  I added a field to the query with the alias.  You can ignore everything but that aliased field in the SQL.  I think that is the central point of your question.
Well now I'm excited! I'll try it in the morning. Thanks!
That my friends is why Persistence is the key. That works great in the SQL.

There is one problem though. Once you go into Design View it will not allow you to go back into SQL View or Datasheet View without giving errors. It just won't let you leave Design View. So I altered the SQL all in Notepad and transferred it into SQL View.

Then saved the SQL in the Notepad for further testing and altering.


THANKS A TON!