Results 1 to 4 of 4
  1. #1
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169

    String without the quotes

    I am trying to get the string that is within my quotes and not having any luck with the various ways I am attempting to do this. I have read only access so I cannot do a replace or update of the raw data. The raw data looks like this:

    uniqueid memberfirst memberlast memberaddress membercity memberstate memberzip memberage dateofbirth
    "111111" "john" "doe" "111 jones str" "Bronx" "ny" "10021" "25" "12/19/1965 00:00:00"

    Don't ask me why the DBA built this table like this. None of the other tables are like this.



    I have tried:

    select distinct substring(uniqueid,'"','')
    select distinct str$(uniqueid,'"','')
    select distinct mid(uniqueid,'"','')



    Nothing works

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Where is the raw data? You can import that kind of data into Access. I often import .txt or .csv files where the data is text and is quoted, and may have commas or Tabs or other character separating each field.

  3. #3
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    It is in a SQL Server and the output has to remain in the SQL server. The tables that join on this one all have it like:

    MemberTable
    uniqueid memberfirst memberlast memberaddress membercity memberstate memberzip memberage dateofbirth
    "111111" "john" "doe" "111 jones str" "Bronx" "ny" "10021" "25" "12/19/1965 00:00:00"

    ConditionTable
    uniqueid condition dateofservice outcome
    111111 diabetes 01/01/2016 a1c eye and neph exam completed routing visits on a monthly basis to maintain normal levels

    None of the other tables they have the " " and I cannot pull in any data on join because of this " " issue. I cannot export outside of the SQL Server. Rights for this are turned off. I know there is a way to query this just have not found it yet.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Maybe use Replace function:

    Replace([fieldname],"""","")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. I never know how many quotes to use!!
    By crombiecrunch in forum Modules
    Replies: 2
    Last Post: 05-12-2014, 07:54 AM
  2. Replace double quotes
    By Kay in forum Programming
    Replies: 27
    Last Post: 12-10-2012, 10:04 PM
  3. Getting the Quotes right
    By aytee111 in forum Programming
    Replies: 2
    Last Post: 10-11-2012, 08:04 PM
  4. Suppress quotes when writing a string via Write #
    By GraeagleBill in forum Programming
    Replies: 9
    Last Post: 10-08-2012, 06:29 PM
  5. New Database on Customer Quotes
    By rkalapura in forum Access
    Replies: 2
    Last Post: 07-13-2012, 07:15 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