How to manipulate Zeroes in a Unit Price field in a Report

[LadyScot101]

I have created a report and, in the unit price field, I have set the decimal place to 5 in the report properties. Sometimes we have pricing for items up to 5 decimals long, such as $10.02985. It is rare though, so I'm to see if there is a way to have it drop the zeros down to 2 decimals when more is not needed. Is there a way? I'm thinking maybe a VBA event might work, but not sure. Any thoughts?

 

[billmeye]

Set your data type to Double and it will only show whatever you actually entered into the field. It won't show the $ but you can just manually place that before the number, i.e, ="$" & [Price]. Don't try using Format() as it will fix the number of decimals, just leave it unformated.

 

[LadyScot101]

I went into the table & on Unit Price, I hit the down arrow under Data Type & there is no Double to choose. Should I be someplace else?

 

[JHB]

You need to change the field type from Currency to Number, then you can choose Double.

 

[LadyScot101]

It worked when I changed the decimal place to 2. Bad thing is it rounds it to 2 on display unless you click in the field it will go back out to the 5 digit number that is actually there. It prints correctly also (5 digit number). When I set the decimal place to 5, it wouldn't work (showed zeros after actual number). I can live with the display issue, since if you click into it and it shows the actual number and prints correctly. There's not a way around that though, right?

 

[JHB]

There's not a way around that though, right?

I think there are, but difficult to say without seeing the report, (could you post the database with some sample data and only the necessary reports/forms, zip it?).

 

[LadyScot101]

Click on the Purchase Request button. ID 14004 is an example of the display issue. If you click on the
Purchase Order button in the middle of the screen (for Printing), you will see that it prints out correctly.

 

[JHB]

I've made a solution for you, try the attached database.
You've to put the below code into the Sub form's Current event in your other database, if you want to use it.

Private Sub Form_Current()
  Dim a As String
  If Not IsNull(Me.UNIT_PRICE) Then
    a = CStr(Me.UNIT_PRICE)
    If InStr(1, a, ",") Then
      If Len(Mid(a, InStr(1, a, ",") + 1, Len(a))) >= 2 Then
        Me.UNIT_PRICE.Format = ""
      Else
        Me.UNIT_PRICE.Format = "Standard"
        Me.UNIT_PRICE.DecimalPlaces = 2
      End If
    Else
      Me.UNIT_PRICE.Format = "Standard"
      Me.UNIT_PRICE.DecimalPlaces = 2
    End If
  End If
End Sub

you will see that it prints out correctly.

Yes for that one price, but not for the rest I think.

 

[LadyScot101]

I opened up the attached database and it still shows the rounded version of the .2895 as .29 unless it's clicked on. I've been told that our system doesn't handle the ME. codes. Usually errors out. This isn't erroring though, just not working it seems.

 

[JHB]

I've been told that our system doesn't handle the ME. codes. Usually errors out. This isn't erroring though, just not working it seems.

Ok - never heard that, (after more as 16 years with MS-Access).
I've attached a picture how it look like by me.

 

[JHB]

I've made a new solution which avoid the use of Me.

Private Sub Form_Current()
  Dim a As String
  If Not IsNull(Forms![Order Form]![Request Details].Form![UNIT PRICE]) Then
    a = CStr(Forms![Order Form]![Request Details].Form![UNIT PRICE])
    If InStr(1, a, ",") Then
      If Len(Mid(a, InStr(1, a, ",") + 1, Len(a))) >= 2 Then
        Forms![Order Form]![Request Details].Form![UNIT PRICE].Format = ""
      Else
        Forms![Order Form]![Request Details].Form![UNIT PRICE].Format = "Standard"
        Forms![Order Form]![Request Details].Form![UNIT PRICE].DecimalPlaces = 2
      End If
    Else
      Forms![Order Form]![Request Details].Form![UNIT PRICE].Format = "Standard"
      Forms![Order Form]![Request Details].Form![UNIT PRICE].DecimalPlaces = 2
    End If
  End If
End Sub

 

[LadyScot101]

That works! Thanks bunches for all your help!

 

[JHB]

You're welcome, luck with your project.