Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Field in Query Returns Symbols Instead of Alpha Characters

    Hi,

    Using Access 2016.
    My query is returning some odd characters in one field that should be straight forward alpha characters. Symbols look along the lines of 㼜ş. But I can see a normal looking field when I review the data in the table. The data type of the field is Long Text created when I imported an Excel file.

    Any thoughts?

    thx
    w

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its almost certainly to do with code pages. the exdl data was encoded with one code page and the Access db is in another
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks healdem,

    I think I was not clear.
    I imported the Excel file into a new table in the database.

    I reviewed the new table and everything looks perfect.

    I then created a query to join this table to the main table (left join) and dragged a few fields to the query grid.
    When i view the query in datasheet view is when I notice the odd symbols from a field that is of data type Long Text.

    thx
    w

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is 'longtext' never heard of it in a MS Access context. MySQL yes, but not Access (assuming you are using JET as the SQL engine)

    if you write a query on just the one table do you still get the symbols
    is it when you jojn the tables you get the problem
    you may need to coerce the new data to a specified format. when you query tables you are relying on the underlying database engine to do something. its quite possible you have one tabel with one code page, anoither tabel with another code page. the JET SQL engine can handle that if the tables are separate. but when you try to join two tables and they use different code pages something has got to give.

    the default SQL engineused in Access (JET) is now pretty old, yes its been updated, its been extended but much of the core design dates back so many years before extended multilingual codepages were developed. JET SQL may still have some hangovers from exgended ASCII, not the more modern UNICODE implementations.

    it could be that you need to shift longtext to memo
    how much space do you need for this data
    could you use varchar instead of long text
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post

    ...what is 'longtext' never heard of it in a MS Access context...

    ...it could be that you need to shift longtext to memo...
    Actually, as of 2013, I believe, Memo became Longtext! At the same time Text, of course, became Shorttext! I have no idea why the 'Boys of Redmond' felt the need to change nomenclature!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Missinglinq,

    Correct, Memo is now LongText : Long Text

    healdem,

    A simple select query on the Table with the Long Text data type returned the correct results.
    Once I join the Notes Table to Tracking Table, I receive the odd symbols as opposed to the expected simple textual description.

    thx
    w

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if its fine in a single table query, but not fine in a multi table query then Id suggest rying to see if its soemthign to do with the memo/longtext
    do you actually need a memo/longtext column for this. would a varchar do?

    just thinking out aloud what would happen if you explcitly coerce the column to string using say cstr()

    is it possible there is redundant data in the column as an artifact of the import.

    what code page did the original data come in.

    is it possible it is valid data but in say UNICODE.. what haoppens if you test some of that dat with asc()

    http://www.fmsinc.com/free/NewTips/A...ccesstip44.asp and similar may be woirth a read
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks healdem,

    I had "Totals" turned on for the query - which I did not need. As soon as I turned "Totals" off, I received the expected results including the Notes field. Lesson learned: shut down and take a good break - especially when something does not make sense.

    thx
    w

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •