Results 1 to 5 of 5
  1. #1
    dlbDennis is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    2

    How to import data where the memo field has a special characters # at the beginning of the data

    I know it should be enclosed like this [#] But they are not


    So I have been trying to do an update query with no luck.
    Any help would be great
    Thanks in advance

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There is not enough information in this post. How about an example of the data, which I assume is in a text file, at the very least, along with what error you're encountering when trying to import the file.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In your query criteria, use Like "[#]*" and it will find fields beginning with #.

    Here is a sample table
    Click image for larger version. 

Name:	TblWithMemofieldwith_numbersign.jpg 
Views:	9 
Size:	64.9 KB 
ID:	25803

    And here is a query
    Code:
    SELECT Memotbl.id, Memotbl.fld
    FROM Memotbl
    WHERE (((Memotbl.fld) Like "[#]*"));
    and here is the result

    Code:
    id fld
    22 #78 has always been lucky for the Platzberg family. Starting with Friitz iin 1767, he won lotto max worth $78 Australian.

  4. #4
    dlbDennis is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    2
    I'm trying to import data from one table to another like this
    INSERT INTO table1
    SELECT table2.*
    FROM table2;
    But when there is a # in the memo field they will not import. I have removed it from 10 records and they imported just fine.
    So I'm thinking I need a query to ether import the records as they are or a query to remove just the # from the rest of the data.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I created two tables

    [code]Table1
    TID Field1 Field2 Field3 (Autonumber, text, date, memo respectively)

    I put in a record

    Field1 = "TEST DATA"
    Field2 = 1/1/2016
    Field3 = # TEST THIS and a bunch of random characters

    Table2
    TID Field1 Field2 Field3 (Number, text, date, memo respectively)

    I ran a query appending all records from table1 to table2 with the number sign in the memo field (field3) and it worked just fine.

    I also tried combinations of memo/text

    Table1 Text, Table2 Memo (field3)
    Table1 Memo, Table2 Text (field3)

    and that also worked

    Both of my tables are local tables. So again there is not enough information about your setup, please be as complete in your description of the problem so we don't have to do all the work for you.

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

Similar Threads

  1. Replies: 4
    Last Post: 09-18-2015, 07:20 AM
  2. Replies: 6
    Last Post: 05-08-2014, 01:32 PM
  3. Data Validation: check for special characters
    By mabrande in forum Access
    Replies: 11
    Last Post: 08-22-2013, 02:18 AM
  4. Limited characters in a memo field
    By Robbyp2001 in forum Forms
    Replies: 11
    Last Post: 11-24-2011, 07:04 AM
  5. Replies: 3
    Last Post: 03-05-2011, 12:46 PM

Posting Permissions

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