Weights & Measures

[Bob HoM]

How do I create a table for Weights (in lbs. & oz.) that I can query for totals? This is to create a League table for a fishing club's competitions)?

Access only seems to be interested in money & date formatting!

[orange]

Use a common unit eg oz where 1 lb = 16 oz
store the weights in oz, display/report in lbs and oz

Divide total oz by 16 to get lbs, remainder * 16 to get remaining oz.

for example

6 lb 6 1/2 oz bass (a beauty)

store as 6 *16 lb = 96 oz
+ 6 1/2 oz
====================
Total is 102.5 oz.

For display or report
102.5 oz = 102.5/16 = 6.40625 lb


or to get the remainder oz
1 lb = 16 oz
so .40625 lb = 16 *.40625 = 6.5 oz


====> 6 lb 6.5 oz

Good luck.

[Bob HoM]

Thanks for reply. I understand the calculation, but my problem is separating the remainder (.40625 in your reply) from the leading integer in order to multiply it by 16.

[orange]

Subtract the whole number part
eg

the wt in pounds is 6.40625 lb,
so subtract 6 lbs 6.40625 lb -6.0 = .40625 (that is part pounds)

multiply the part lbs by 16 to get oz.

.40625 lb * 16 oz/lb = 6.5 oz.

[Bob HoM]

Thanks again. The maths I fully understand. What I want to know is how to do this in Access, rather than manually alter every record (I vaguely remember using a DatePart function in an earlier version - is there something like this for numbers?).

[orange]

Bob,

No, there is nothing like that for numbers.
But, there is a technique. Here is a sample using Integer divide-- where you can get the full lbs and then subtract that from the total weight to get the Part lbs.

Here is a sample procedure that uses the 6 lb 6.5 oz fish in earlier post.
This could be made into a user defined function quite easily. I'm not sure of your vba skills, but you should be able to follow it from the comments. You could copy this example and run it.

I'm going out for a while, but will check back later.

'---------------------------------------------------------------------------------------
' Procedure : wt
' Author    : mellon
' Date      : 11/04/2016
' Purpose   : from post https://www.accessforums.net/showthread.php?t=59159
'
' to convet a fractional weight from pounds(lb) to  pounds and ounces(oz)
'where 1 lb= 16 oz.
'
'*********************************************************************
'  USES INTEGER DIVIDE in order to separate full lbs and part lbs
'*********************************************************************
'---------------------------------------------------------------------------------------
'
Sub wt()
10    Dim fishwtlbs As Single  'total weight of the fish in lbs
20    fishwtlbs = 6.40625      'an example of a fish weighing 6lbs 6.5 oz
      Dim FullLbs As Single    'full lbs if fish
      Dim PartLbs As Single    'fractional remainder of fish weight in lbs
      Dim PartLbsAsOZ As Single 'fractional remainder of fish weight in oz
      
      'the trick to get the full pounds
      ' integer divide to get full lbs (removes the decimals)
30       On Error GoTo wt_Error

40    FullLbs = fishwtlbs \ 1   'note the backward slash  INTEGER DIVIDE *******!!!!

50     PartLbs = (fishwtlbs - FullLbs)  'remove the full pounds from total weight in pounds to get the part pounds

60     PartLbsAsOZ = PartLbs * 16 ' to go from pounds to oz multiply by 16
       
70     Debug.Print "Fish Total lbs : " & fishwtlbs & vbCrLf _
                  & "Total wt in oz : " & fishwtlbs * 16 & vbCrLf _
                  & "FullLbs : " & FullLbs & vbCrLf _
                  & "PartLbs : " & PartLbs & vbCrLf _
                  & "Part pounds as oz : " & PartLbsAsOZ & vbCrLf _
                  & "Display wt of fish : " & FullLbs & " lbs  " & PartLbsAsOZ & " oz"

80       On Error GoTo 0
90       Exit Sub

wt_Error:

100       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure wt"
End Sub

The results:

Fish Total lbs : 6.40625
Total wt in oz : 102.5
FullLbs : 6
PartLbs : 0.40625
Part pounds as oz : 6.5
Display wt of fish : 6 lbs  6.5 oz

[Bob HoM]

That's what I was looking for!
Sorry I didn't express myself too well to start with (I was beginning to believe you must think I'm really thick)!!

Thans again for your patience & help.

[orange]

No problem, happy to help.

Good luck.