If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Access make table query with one of the field / column names with a colon in it

Closed Thread
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 12
Question Access make table query with one of the field / column names with a colon in it

One of the field titles in the table I need to create using the access make table query needs to contain colons

the title needs to be this-
c:Also_Available_in_case_qtys_of:string


the original field name in the source table is -

case Qty

I tried

c:Also_Available_in_case_qtys_of:string : case Qty

did not work.
I need to treat the "c:Also_Available_in_case_qtys_of:string" part as text.


Any ideas?
The mentions I saw on the internet to similar problem never had solution shown.


I am using access 2003


Thanks in advance.
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: Nevada, USA
Posts: 2,793
This works:

SELECT "Blah" AS [Really:Bad Idea] INTO NewTableName
__________________
Paul
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 12
I am not sure on how to read this reply.

Thanks for the quick reply

But,
This is for a field name , in a table, not the table name
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: Nevada, USA
Posts: 2,793
In that example "Really:Bad Idea" will be the field name. The table name is "NewTableName".
__________________
Paul
  #5 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
:shudder: A field name or table name should NEVER contain colons. There must be another way.
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,293
why do you need a column to be named this way?
choose a column name that is appropriate for the task
assuming that you are using JET you can set a property (forget which, but its used as a column heading.
its relatively rare for people to solely use SQL to extract and format data. these days its more usual to use SQL to extract the data and format that data inside the front end.. In my books thats where you should be doing your
Code:
c:Also_Available_in_case_qtys_of:string : case Qt
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
  #7 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 12
Unhappy colon in field name

I wish I had the complete control of all other systems in the world.

The purpose of the out file is for working with OTHER systems.

If one of them requires a field name to be in a specific format, then not much I can do about their system.


Their system requires the field name to contain the colons.

I cannot change their system. Sometimes this happens, it is a big world out there.
  #8 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,293
are they reading your Access/JET data directly, or are you going throuigh soem intermediate process say a file export

based on Error message when you use special characters in Access databases it seems you cannot use the colon as a column name.. ever. so either the other system is talking gibberish OR its not compatable with Access/JET

are you certain its the column name and not the column data you are referring to.....

as I read the colons in what you have said is a column anme as column data.. the : acts as a placeholder

I don't understand your reference to titles.. its doens' stack up, or perhaps I just don't understand the terminology

I'd expect the title for this column to be something like extendeddescription. as it describes additional information such as this product ias available in cases of x units.
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
  #9 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: Nevada, USA
Posts: 2,793
Quote:
Originally Posted by healdem
it seems you cannot use the colon as a column name.. ever..
Actually the colon is listed in the section that recommends you not use it, but if you must you have to bracket the field name. As demonstrated above, you can use it, but my choice of field name demonstrates how I felt about it.
__________________
Paul
  #10 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 12
Pbaldy is correct , Healdem less than correct

The colon can be used in the field name.


as for Healdem responses
"it seems you cannot use the colon as a column name.. ever. "

I actually can rename the field name to include the colons.
Then I can export to comma delimited file.

and his insight
"choose a column name that is appropriate for the task"

Er - this is what I am doing, and the task REQUIRES the colon in the name

And the company requiring this maintains probably the largest database in the world. I am sure they have their reasons for "WHY" for this particular field naming convention.

Thanks for the help in submitting a possible solution Pbaldy
  #11 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
Use a query to change the field names from something sane in to this colon business.

SELECT [mySaneFieldName] As [Are: You; kidding!?]
FROM [myTable]
__________________
oh yeah... documentation... I have heard of that.

*** What Do You Want In The MS Access Forum? ***
  #12 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Good advice. LOL @ the fieldnames!
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
  #13 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 12
Thanks for the info.

It works perfectly, and the destination database accepts the file perfectly

BUT "breaks" when the query is saved

NOTES-
though when it is typed in the design view it does not work.
(typing in the variation for design view )
when typed in the sql view it works.

When I switch back to design view, the expression is the exact same one I typed that generated an error before.

yet it works.

BUT when the query is saved, when reopened, it now does not function. It rewrites the repression due to the : ' s


Would it be possible to treat the colons as a character?
  #14 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 12
I find it amazing that so many people have difficulty grasping that the export file I am creating requires some colons in one of the field names. The destination DB requires it to submit this particular field. It is a unique field.
I had 4 different persons respond- two gave valid advice on possible solutions. - Thanks Pbaldy and Teddy


Two ( Healdem and StarTrekker) either just had no clue on using access, on solving the problem Or they just kept saying it was a bad idea. They seemed to think I had control on the DB requirements of a MULTIBILLION-DOLLAR company, who runs probably the biggest DB in the world. They must only deal with very small companies.



Sorry for the ranting, but it is just amazing that some people who claim to be experts in a field are unable to deal with the various changes in the database requirements on a global scale.
.
  #15 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,293
who claims they are experts? I certainly don't.
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On