Unanswered: Probably a simple fix for those more knowledgeable than I
I have a table to store weights, some chose to enter the weights in lbs while others use kgs. After much prodding, some threatening, and overall pleading I am stuck with a dilemma. Neither side is willing to change, and I need all the weights to eventually end up as kgs. I have the table set up so that the weights and units are two separate fields, now I just need to figure out how to tell the database to only convert those records where Unit=lbs. I tried
If [Unit]="lbs" Then
[Text14]="Round([Wt]*0.45) & ' kgs'"
Any suggestions or tips, I appreciate any help given.
I suppose on what you are trying to achieve..
One weasel appraoch would be to store the value as kilograms but display in both. Incidently, I think your conversion fadctor is wrong, AFAIK there are 454g to one pound, ok its not that significant but it is a variance.
The alternative is to set up a table defining measurement systems and how they relate back to your internal measurement base system. In weight measurements its not a significant problem, but there are, to my knowledge 3 significant measurement systems metric, avadopurius and US. The easiest, and most consistent is metric
the others are notionally similar. The main diffedrences are in volumes where a US pint and gallon shortchange the consumer.
I have no idea how I would go about storing them as Kgs but displaying both (although really it makes no difference, all outputs must be in Kgs...just some are lazier than others and the rest refuse to stop converting prior to entering). In the end I just need the Kgs to be stored and/or displayed. This would all be easy if everyone agreed on ONE input and stuck to it...but alas life is not meant to be easy. I'm learning as I go, and appreciate any help I can get (the more detailed the better).
I've done this in Oracle Forms. I used an invisible field that was bound to the database, and a visible, unbound one to show and accept values in the user's desired units (meters or feet, in my case, with meters being the only unit in the database). When querying, the visible field would be filled with the value in the desired units by code in the on-query trigger; Access probably has a similar event.
When a user changed or entered a value in the visible field, another trigger (event) would fire, and code would convert the value to meters and put it in the invisible, bound field.
I don't know how easy or difficult this would be in Access, though. It also depends on what you already have.
ivons Oracle expertese is showing, and there are equivalent methods in Access.
The relevant events are the forms on current event, the respective controls "on change event", and /or lost focues event.
If say your base measurement system is metric & KG's then you could have an unbound control which uses the weight (kg) divided by your imperial converter, byt say using a user written function such as ConvertToKG(dblImpValue) and ConvertToPounds(dblMetricValue)
You have a choice in your form design, you could allow entry in either weight box and set the value in the 'other' box. That way round your die hard adherents to the imperial system can retain the units they are used to and those used to the metric system use the metric value. Your subsequent reports can offer the metric or imperial values.
I've added a new field to the table, WtLb and WtKg, and added the following code to the form's After Update Event:
Private Sub Form_AfterUpdate()
If IsNull(WtKg) Then
[WtKg] = Round([WtLb] * 0.45) & " Kgs"
[WtLb] = Round([WtKg] / 2.2) & " Lbs"
When I run it, I get Run time error '-2147352567(80020009)':
The value you entered isn't valid for this field.
I'm guessing that the assignment is actually to the control not the column itself.
If you have a problem with that then an alternative is to define 2 text box controls (called say tbMetricWeight & tbImpWeight), with a label to the right of each text box. the labels being left justified reading "Kg" & "Lbs"
the text boxes being right justified one bound to your metric weight field.
in TBMetricWeight's on change, on lost focus call the format function. If you put soemthing in the onchange event you will need to decide what to do if the information is wrong
likewise with TBImpWeight, except that TBImpWeight is unbound - you don't need to persist / store a value in that control beyond the current record
you need to set a value in TBImpWeght in the foms on current event.