Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Thanked 0 Times in 0 Posts

    Clean Data (2003 ALL SPs)

    One of my most frequent uses of Access is to take data from different places (eg an accounting system) and use it to carry out calculations, produce reports or merge with other data. I do the link to Access via ODBC when I can but often the only available method is to export data from the source file to a txt file and link my tables to there. If appropriate, I will use the linked table to make a clean table in Access eg to get rid of extaneous data or because the ODBC link is too slow.
    A continuing problem (primarily with the txt files ) is that commas in fields (which I have no control over) will throw the data out, confusing the import process and resulting in quotes around data.
    Given that I am making tables with the data and often using the opportunity to tidy up/manipulate data, is there a process I can use in the make table query to "clean up" eg get rid of all quotes or replace commas with spaces or something else. I know this is easy in Excel but cant work out how in Access
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Wilmington, North Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: Clean Data (2003 ALL SPs)

    <P ID="edit" class=small>(Edited by NYIntensity on 15-Jul-05 04:33. Replaced highlighted text. Originally gave wrong location for formula. Sorry)</P>Steve,
    I'm not sure how to incorporate this into your query, but you can create the following module:

    Public Function MyReplace(aValue As Variant, sWhat As String, sBy As String) As Variant
    If IsNull(aValue) Then
    MyReplace = Null
    MyReplace = Replace(aValue, sWhat, sBy)
    End If
    End Function

    And use it in the <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Field row</span hi> of the query grid like this:
    MyReplace([field name],","," ")
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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