How do I indicate the field types when creating a new table while using an MS Access Make Table Query?

Posted on 2011-05-07
Last Modified: 2012-05-11
I am trying to create an Access table by using a make table query that contains an expression to build a concatenated data field.  The expression uses the Dconcat function created by Patrick Matthews which is very helpful for putting together the data into one field, but the problem is that the data being concatenated sometimes goes over the 255 characters limit that is created by default when the make table query runs.  I need to know how to indicate in the query to make the field be of "Memo" type instead of the "Text" type that gets created by default so that the data will fit in the field as needed.  Any assistance with solving this problem will be greatly appreciated.  Thanks! DConcat-SampleData-jb.accdb DConcat-SampleData-jb.accdb
Question by:jaime0820
    LVL 77

    Accepted Solution

    There isn't any way to control the data type in a make-table query.

    To get the final result you want you will have to build the table first with the memo field in place and try using an append query.

    However that still might not work because many SQL operations involving memo fields result in them being truncated to 255 characters.  If that turns out to be the case, you may be faced with doing the update of the memo field in code.
    LVL 9

    Expert Comment

    by:Armen Stein - Microsoft Access MVP since 2006
    To add to Peter57r's excellent answer, the table can be persistent.  Instead of using a Make Table, keep a table with the structure you want.   Each time to you need to use it, run a Delete query to clean out all the records, then reload them with an Append query.  This will keep your structure correct.


    Author Closing Comment

    I did change the query to an append query as suggested and it resolved my problem.  I greatly appreciate the advice.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now