Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    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'"
    Else
    [Text14]="[Wt]&' '&[Unit]"
    End If

    Any suggestions or tips, I appreciate any help given.

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    My solution would be to store kgs only, by converting the lbs to kgs when the data is inserted. The data entry form would need a way to indicate wether kgs or lbs are being entered.

  3. #3
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    Any suggestions

    on how to go about doing this?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

  5. #5
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    again, how...

    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).

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by RAPUNIT
    on how to go about doing this?
    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.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

  8. #8
    Join Date
    Aug 2005
    Location
    New England
    Posts
    35

    Unhappy Hopefully almost there

    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"
    Else
    [WtLb] = Round([WtKg] / 2.2) & " Lbs"
    End If

    End Sub

    When I run it, I get Run time error '-2147352567(80020009)':
    The value you entered isn't valid for this field.
    Any suggestions?
    Last edited by RAPUNIT; 11-02-05 at 15:26.

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    What's the datatype of your WtLb and WtKg fields? If it's numeric, you can't concat the " Kgs" and " Lbs" to it.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •