Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    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. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,794
    This works:

    SELECT "Blah" AS [Really:Bad Idea] INTO NewTableName
    Paul

  3. #3
    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. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,794
    In that example "Really:Bad Idea" will be the field name. The table name is "NewTableName".
    Paul

  5. #5
    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. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,526
    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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    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. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,526
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,794
    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. #10
    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. #11
    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. #12
    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. #13
    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. #14
    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. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,526
    who claims they are experts? I certainly don't.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •