Adding A Space after the Decimal!

[Martian262002]

Hello Everyone ,

In access, I have a field in a table that I need to fix. The field contains a charge(money) value, with a code attached to it. For example:

[2345.0020680]---> The (2345.00) is the charage amount & the (20680) is the code.

[17580.04PPO]----> (17580.04) is Charge; (PPO) is the code.

**Every charge amount is different, and every codes length is different; so there is no consistency in this field.

I need to add a space, comma, backslash, or something two places after the decimal, to split the two. Once I do that I'll export to excel, and split the field into 2.

HELP!

Thanks in advance everyone,

Martian262002

 

[RuralGuy]

How about:

Public Function SpecialFunction(InString As String) As String

SpecialFunction = Left(InString, InStr(1, InString, ".") + 2) & " " & _
          Right(InString, Len(InString) - (InStr(1, InString, ".") + 2))

End Function

 

[mdcory]

I also need to add a space 2 places after the decimal when I run a report. I tried the above string but I'm not sure how to do it. Could someone help explain how to use this a little more.

Thanks.

 

[ajetrumpet]

Cory,

The code that RuralGuy wrote uses a few different string functions to... first, find the "." in the cell value; then, add a space between the specified LEFT & RIGHT portions of the cell string. Of course though, as it is written above, it would have to be "called" out of the module from which it is stored before it can perform.

If you need to do this when you run a report, you have two options...

1) Write the code in the "OnLoad" event of the report specifying the control name of the field that is in question....

Me.ControlName = code

2) Write the code as a substitute field for the "original field that has to be manipulated" in a query, along with all the other fields that should be in the report. Then just set your report's recordsource as the query.

 

[RuralGuy]

I also need to add a space 2 places after the decimal when I run a report. I tried the above string but I'm not sure how to do it. Could someone help explain how to use this a little more.

You can set the ControlSource of a TextBox on your report to:
=SpecialFunction([YourField])
...using your field and control names of course.

 

[Pat Hartman]

I would just concatenate the fields rather than using the instr.

Format(ChargeAmt),"0.00") & ":" & CodeField

This formats the money field with a fixed two decimal places. It then concatenates a semi-colon followed by the CodeField.