Design View

  • Thread starter Thread starter I am stuck
  • Start date Start date
I

I am stuck

I have a field with the below data:(Data Type Text)

$2,000,000.

Any way under Design View (Format), that I can remove
the $ , . from the $2,000,000.

I can't use data type number because the information is
being imported from another source which would require
major changes to remove these values. Plus the value will
not import because some of the rows have text.

Thanks in Advance,

I am stuck
 
Rich,

No, you can't do this using any sort of built in format functionality.
It might be best to make a user-defined function to handle this. For
example, put something like this (untested!) in a standard module...
Public Function StripTextNumber(InputString As String) As String
Dim Output As String
Dim CommaPos As Integer
If Left(InputString,1) = "$" Then
If Right(InputString,1) = "." Then
Output = Left(InputString, Len(InputString)-1)
End If
CommaPos = InStr(Output,",")
Do While CommaPos > 0
Output = Left(Output,CommaPos-1) & Mid(Output,CommaPos+1)
CommaPos = InStr(Output,",")
Loop
StripTextNumber = Output
Else
StripTextNumber = InputString
End If
End Function

Then, in a query, make a calculated field that looks like this...
CleanedUp: StripTextNumber([YourFieldName])
 
Thanks,

Trying to make it easy.
-----Original Message-----
Rich,

No, you can't do this using any sort of built in format functionality.
It might be best to make a user-defined function to handle this. For
example, put something like this (untested!) in a standard module...
Public Function StripTextNumber(InputString As String) As String
Dim Output As String
Dim CommaPos As Integer
If Left(InputString,1) = "$" Then
If Right(InputString,1) = "." Then
Output = Left(InputString, Len(InputString)- 1)
End If
CommaPos = InStr(Output,",")
Do While CommaPos > 0
Output = Left(Output,CommaPos-1) & Mid (Output,CommaPos+1)
CommaPos = InStr(Output,",")
Loop
StripTextNumber = Output
Else
StripTextNumber = InputString
End If
End Function

Then, in a query, make a calculated field that looks like this...
CleanedUp: StripTextNumber([YourFieldName])

--
Steve Schapel, Microsoft Access MVP

I have a field with the below data:(Data Type Text)

$2,000,000.

Any way under Design View (Format), that I can remove
the $ , . from the $2,000,000.

I can't use data type number because the information is
being imported from another source which would require
major changes to remove these values. Plus the value will
not import because some of the rows have text.

Thanks in Advance,

I am stuck
Click to expand...
.
Click to expand...
 
If you're trying to make it easy, John's suggestion in the other thread
is obviously easier than my suggestion... I didn't think of using Replace()
 
Why bother?

When you want to use the numeric value 2 millions, use
CCur to convert it to numeric.

For example (from the Debug window):

?CCur("$2,000,000.00")
2000000

If you really want to change the Text Field values, you
can also use the above in an Update Query.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top