Results 1 to 12 of 12

specify field type in make table query

  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    315

    specify field type in make table query

    Is there a way to specify a field type (short text) in a make table query. I have Googled and it appears that there is not so I was wondering if you could create the make table query, then view it in sql form and then add as string after that field.



    When I try
    SELECT AllRailcarData.RailcarID, AllRailcarData.CarInitials, AllRailcarData.CarNumber as string INTO TestFieldType
    FROM AllRailcarData;


    I get
    The select statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

    The actual make table query is quite long and has a lot of fields so I don't want to have to rewrite in vba if I don't have to.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Cannot specify datatypes with SELECT INTO statement. The new table should adopt the datatypes of the source.

    Why do you need to make this table?

    Review https://www.w3schools.com/sql/sql_create_table.asp
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    315
    It is a temporary table that allows me to select specific fields for processing.

    If i cant do that then I will try to just modify the code to have a delete query and delete the contents of the table instead of recreating it each time.

    Thanks

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,667
    If i cant do that then I will try to just modify the code to have a delete query and delete the contents of the table instead of recreating it each time.
    That is what I do, and it works quite well.
    The only thing to remember is to periodically Compact & Repair the database to keep it from bloating in size (deleting data in Access does not reduce the size until it is compacted).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    But why not just export the query?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    315
    I have just realized that it doesn't matter because either way Access drops the leading zero's on export. Even if the field is formatted to keep them. So I have to resort to creating a new field during the make table that I can format, so then when I export i can export that field as with the original name but now properly formatted.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,883
    This might create a text field:

    CStr(AllRailcarData.CarNumber)

    or if you're trying to preserve leading zeros:

    Format(
    AllRailcarData.CarNumber, "00000")

    with the appropriate number of zeros.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Format settings in table field do not change data. Are you exporting to Excel? Although not explicitly declaring datatype, the Format() on data should force Excel to recognize as text and set worksheet column as text. It did work in my quick test. I also tested a text field that had numbers with leading zeros. The leading zeros were retained by Excel without using Format() function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,667
    Are you exporting to CSV or other text file, and then opening directly in Excel?
    If so, then Excel does its own conversion and will usually drop the leading zeroes, but if you change the way you open it, you can avoid that issue.

    If you are exporting to a text or CSV file of some sort, view it in a Text Editor after exporting, like NotePad or WordPad, and confirm what is really in that field. Does it show the leading zeroes there?
    If so, then the issue isn't with Access or exporting, but rather how you are opening the file afterwards.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    244
    One method I use is a temp database and a template table.

    I usually create the temp database when opening my app. Then when I need the temp table I use a template table and just select that into the temp database. By using a template table I am able to make any changes to the table structure without having to re-do any code.

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    244
    Here's an example of creating a temp database, creating the tables from template tables, linking and un-linking from the temp tables, and deleting the temp database.

    ExampleTempDatabase.zip

  12. #12
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    315
    Yes June07 I ended up using Format(AllRailcarData.CarNumber, "00000") in the query and created a new field CarNumber1 so that I could modify that on export. When I tried to run it on the original name it would not let me
    example CarNumber:
    Format(AllRailcarData.CarNumber, "00000") did not work so i had to change the field name in temp table to CarNumber1 and do CarNumber: Format(AllRailcarData.CarNumber1, "00000")

    This is being exported to a flat text file where there are requirements to left align some fields, right align others and fill numeric fields with leading zeros.

    Moke123 I will check out your database today. Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Make table query type looses data
    By annux3 in forum Access
    Replies: 4
    Last Post: 07-05-2016, 05:26 AM
  2. Replies: 4
    Last Post: 10-01-2014, 02:59 PM
  3. Replies: 5
    Last Post: 06-19-2012, 09:46 AM
  4. Replies: 1
    Last Post: 05-24-2012, 02:29 AM
  5. change data type in make table query
    By jgelpi16 in forum Queries
    Replies: 2
    Last Post: 01-26-2011, 08:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums