X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Formatting Fields In Make Table Query, Access 2016    
post Feb 12 2019, 03:04 PM

Posts: 12
Joined: 16-October 11

I have a Make Table query that includes an input field that must be formatted as Long Text. When I run it and make the new table, it formats that field as Short Text and truncates the field contents.

How do I specify the field formatting in the Make Table Query? The data in this field originates from a Long Text field in an input Table that feeds into the Query. Then in my query, I have a Builder statement that applies an IF rule to assign either the original value or a new default value as a substitute for the null values.

First, I tried the Properties window, but I couldn't find 'Long Text' as an option.

Next, I was hoping that their might be some kind of formatting function, something like FORMAT( (MyBuilderStatements),'Long Text'), but couldn't find anything like this.

My current work-around was to:
  • make the table
  • change the Table field from Short Text to Long Text
  • delete the table contents
  • changed my Make Table Query to an Append Query

Thank you in advance for any help you can share.

Go to the top of the page
post Feb 12 2019, 04:03 PM

UA Admin
Posts: 34,812
Joined: 20-June 02
From: Newcastle, WA

I would suggest that your workflow should be:

Empty the current table like this: "DELETE * FROM tmpYourTableNameGoesHere"

Refill the table with the current append query.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Feb 12 2019, 04:28 PM

Posts: 1,270
Joined: 4-June 18
From: Somerset, UK

Agree with George. However if for any reason that solution isn't acceptable, instead create a data definition query in SQL view (or use VBA):
    PersonID INT,
    LastName TEXT(25),
    FirstName TEXT(20),
    Address MEMO,
    Postcode TEXT(8));

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th April 2019 - 07:36 AM