Convert Text field to numeric

Posted on 2005-04-06
Last Modified: 2012-06-22
I am importing a table into Access from and Excel spreadsheet being sent to me.  I have a field that comes in as:
390        - 0013  etc.

(sometimes with spaces and sometimes without)

I need somehow to have these read as

390-13 etc.

I am not very good with calling I'd like to use a query if possible.

Question by:tdverdon

    Expert Comment

    Paste the following code into a module and call it the same way you would any other function.

    eg RemoveSpaces([FieldName])

    This will give you the whole field with all whitespace stripped out.

    Public Function RemoveSpaces(strSearchString As String) As String

    On Error GoTo ErrorTrap:

    Dim strRemaining As String
    Dim intPosition As Integer

        strInput = Trim(strSearchString)
        intPosition = InStr(strInput, " ")
        If intPosition > 0 Then
            RemoveSpaces = Left(strInput, intPosition) & RemoveSpaces(Right(strInput, Len(strInput) - intPosition))
            RemoveSpaces = strInput
        End If

    Exit Function

        MsgBox Err.Description, vbCritical, strAppTitle
        RemoveSpaces = "Error"
        Exit Function
    End Function

    Expert Comment

    Sorry, I didn't finish reading your message.  The above answer will strip out spaces, but it doesn't give you the full answer.  

    You will have to rely on some more string manipulation to finish the job.  

    Can you rely on the second part of the input string always being 4 digits?
    Will there allways be a hyphen separating the two components?
    LVL 17

    Accepted Solution

    I would strip the "-" the same way jez p stripped the spaces out using a two step process but using and 'if then' to do the right ones:

    If Instr(strInput," ") AND Instr(strInput,"-") then
      'Strip the spaces and dashes

    elseIf Instr(strInput," ") AND NOT Instr(strInput,"-") then
       'Strip the spaces

    elseIf Instr(strInput,"-") AND NOT Instr(strInput," ") then
     ' Strip the dashes

    end if

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now