• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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

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
Jaime Bonilla
Jaime Bonilla
1 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.
Armen Stein - Microsoft Access MVP since 2006Commented:
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.

Jaime BonillaSystems AdmininistratorAuthor Commented:
I did change the query to an append query as suggested and it resolved my problem.  I greatly appreciate the advice.


Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now