Convert Text field to numeric

Posted on 2005-04-06
Medium Priority
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 code...so I'd like to use a query if possible.

Question by:tdverdon
  • 2

Expert Comment

ID: 13721907
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

ID: 13721976
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

Arji earned 500 total points
ID: 13723067
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

864 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