Access: Modify values in column of datasheet subform

Question

I have a table with the fields: ID, SerialNumber, Capacity, Accuracy.

I display between 1 and 4 records in a datasheet subform based on the serial numbers.

Now I want to display every Capacity

above 5000kg as tonnes(divide by 1000 and add " t")
between 5000kg and 0.5kg as kg(keep the number and add " kg")
Below 0,5kg as grams(multiply by 1000 and add " g")

But, I want all the values to stay in kg in the table. So I only want to change this when I display them.

The last thing I tried was this: I added an extra column named txt_Capaciteit, used this as the 'main' column in which the data is stored and then add "kg" in an empty column. But this changes all the columns in Capaciteit to the column selected in the column txt_Capaciteit.

Me.Capaciteit = Me.txt_Capaciteit & " kg"

Edit:
I also tried to use an expression as control source. By using two if statements, then divide or multiply and at last add "t","kg, or "g". Even though this worked, it made the form very slow and laggy (took 1-2 seconds to load 4 fields)

Can someone please tell me what I'm doing wrong? Thanks in advance!

About 5000, and there wil be added +-10 every week. But as i said, i only display maximum 4 at a time. — KobeFl
It sounds like something that an Access data macro would be helpful for (add a new field to your table, something like CapacityAdjusted, as Short Text. Then, have an After Update and After Insert macro that updates this field using the logic you described. Unfortunately, I'm not familiar enough with Access data macros to provide a full answer (too bad you're not using SQL Server as your backend---I could be of more help there). — Zack
Where did you put your expression when it became slow, in the underlying query or on the form? Try it in the query it will normally perform quicker. If that doesn't work you can use a table to store the text and a multiplier and apply them with a join in the query. — Minty
@Minty I put it in the form since i didn't know how to implement this in a select query... — KobeFl

Gustav Gustav · Accepted Answer · 2018-08-16T13:42:57

Create a function like this:

Public Function FormatWeight(ByVal Value As Currency) As String

    Dim Result  As String

    Select Case Value
        Case Is < 0.5
            Result = Format(Value * 1000, "0 \g")
        Case Is < 5000
            Result = Format(Value, "0.00 \k\g")
        Case Else
            Result = Format(Value / 1000, "0.000 \t")
    End Select

    FormatWeight = Result

End Function

Then, to display the weight from kilograms in your form, use for the textbox this ControlSource:

=FormatWeight([YourWeigthField])

Access: Modify values in column of datasheet subform

1 Answers