Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    weird format in a query (access 2003)

    Not sure what is going on with this but I figured I'd give it a shot. I have a linked table that I run a query off of. I have a text field in my query caled WS where some of the records are showing with a square or a box and the end of the displayed data. The weird part is this is not they way the data has been entered into the table. The problem is I can't do a join on the data in this field because of the square. It does not return a like value because of the square at the end of the data. Is there a way to format this so it removes the square??? I can't change the field value in the linked table so it will have to be in the query. Any ideas?? Thanks....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: weird format in a query (access 2003)

    I suspect that the box character is a carriage return or line feed. Try creating a calculated field in the query:
    <code>
    WS1: Replace([WS], Chr(13), "")
    </code>
    Check whether this makes the box character disappear. If not, change the definition to
    <code>
    WS1: Replace([WS], Chr(10), "")
    </code>
    If that doesn't work either, more investigation is needed.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: weird format in a query (access 2003)

    Where did the linked data come from?
    I think you will find it's a special character maybe a tab character.
    In a query set a field to be
    LastChar: asc(Right (field,1))
    This will return the decimal equivalent, this will give you the value of the box character.
    You can then run a special query to get rid of this strange character.

Posting Permissions

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