need help with getting digits to not appear in formula

[sfryer]

I'm working on a query, it pulls a field that is actually a dollar amount, that looks like any of these examples:

1254.2502ffffffffff
12.25fffff
1200.2351fffffffff
10.02
123.05fffffffff

Basically the dollar amount can be anywhere from 1 dollar to a couple of thousand. After the decimal it can be anywhere from two places to a bunch of digits and then normally it's padded with fffffff at the end. I need this to be a number with only two places after the decimal.

Any help is greatly appreciated.

 

[rainman89]

look at the fixed data type

 

[gemma-the-husky]

you have to think where these extended decimals are coming from

there are 2 issues

1. rounding - how you display it is one issue, which can be managed by setting a sutiable display format
2. storage - but the display is independent of the storage - if you shouldnt have these extended decimals then you need to establish where they are coming from

 

[Insane_ai]

You need to trim the "f"'s off the end of your string, convert the value to a number then store it somewhere else.

You should really consider formatting the Values to currency on the table and not allow the value to have text if you need it to be referenced or manipulated as currency. If this is not an option for whatever reason you may have, the following will help you convert the data. You would then use the GoodNumber in your query.

In this example, I created a table to emulate your issue call tblNumber_Conversion.
It has three fields, Index (Autonumber), BadNumber (Text), And GoodNumber (Currency). I attached the following code to convert the values to a command button for purposes of testing and example. Recreate this table with your examples above to see it in action.


Private Sub CmdConvert_Click()
Dim db As DAO.Database 'I used Microsoft DAO 3.6 Object Library reference
Dim rs As DAO.Recordset 'I used Microsoft DAO 3.6 Object Library reference
Dim strSQL1 As String 'Used to pull the records
Dim strSQL2 As String 'Used to store the new value
Dim curGoodNumber As Currency 'Used for the Good Number
Dim strBadNumber As String 'Used to manipulate the bad number
Dim C As Integer 'Length Count of the Bad Number

strSQL1 = "SELECT tblNumber_Conversion.* FROM tblNumber_Conversion"
Set db = CurrentDb
Set rs = db.OpenRecordSet(strSQL1)
DoCmd.SetWarnings False

rs.MoveFirst
With rs
Do Until .EOF
strBadNumber = rs![BadNumber] 'Current record's Bad Number Field
C = Len(rs![BadNumber])
Do Until C = 0 'In case the entire value is all "f"'s
If Right(strBadNumber, 1) = "f" Then
C = C - 1
strBadNumber = Left(strBadNumber, C)
I = I + 1
Else
Exit Do ' if the last character was not an F, this over rides the Do until C = 0
End If
Loop

curGoodNumber = (Format(strBadNumber, "Currency"))
'The Message box allows you see what is about to happen
'you may comment it out or remove it after testing
MsgBox "Original Number = " & rs![BadNumber] _
& vbNewLine & "New Number = " & curGoodNumber


strSQL2 = "UPDATE tblNumber_Conversion SET GoodNumber = " _
& "'" & curGoodNumber & "'" _
& " WHERE tblNumber_Conversion.Index = " & rs![Index]


DoCmd.RunSQL strSQL2
rs.MoveNext
Loop
End With

DoCmd.SetWarnings True
End Sub

 

[Mandiab]

Thanks neileg!!!

In case anyone wants the code, it's:

CCur(Val([MyFieldName]))

 

[Insane_ai]

Use CCur(Val([MyFieldName])) it is much simpler.

Thanks for teaching me something new.