Mod value to get remainder (1 Viewer)

[DCrake]

Silly question, I want to find out what the remainder is from any given number. It may be 11.01, 3.24, 0.10,0.11, etc. I need to be able to split the value into two parts

A everything before the decimal point
B everything after the decimal point

I need to retain the precision though

Stried using Split(n,".") works for a fashion but not exactly as I want it. Any clues?

 

[stopher]

n - dbl(int) ?

 

[ChrisO]

Not silly but a curious question: -

Sub Test()
    Dim d As Double
    Dim s As String
    
    d = -123.456
    
    s = Str(d)
    MsgBox CDbl(Split(s, ".")(0))
    MsgBox CDbl(Split(s, ".")(1))

End Sub

By definition everything after the decimal point would not have a decimal point.

Chris.

 

[DCrake]

Now this is where it gets complicted

I want to convert the remained from 10ths to 12ths Where as 1.11 = 1 dozen and 11 cans or 12.01 = 12 dozen and 1 can. I have managed it now, but not in the conventional way. It's all about consumption costing. Say you buy 6 and a half cases of beer and there are 24 cans in a case that is 13 dozen so I need to convert 12.12 to read 24.06. This is where it gets tricky the pack sizes can vary from say 6,10,12,15,24 so some maths need to be involved. Like I say done it now but would still like to know answer.

Using split it converted .10 to 1 if declared as a number, likewise .20 as 2 not twenty.

 

[stopher]

singles = (n - int)*100

So in n=12.10 then the value of singles would be 10

But I don't really get what you are trying to do. How does 12.12 equate to 24.06?

Why store pack sizes in this way?

Chris

 

[DCrake]

Mine is not to reason why. If the delivery note states 6X24 50cl bottles then I need to be able to record it or calulate it back to 12 dozen bottles. Because products can be bought in various pack sizes the consumption analysis needs to be consistant that is why everything is converted to dozens.

The end user does not want to do a conversion prior to entering the value manually.

Edit
The 12.12 equates to 25 dozen when the users adds 12 x 24 cases of lager plus a half case (12)

(12X24)+12 = 300
300/12 = 25

 

[ChrisO]

If the question is, can the Mod function be used for this, then I think a data type of Currency will need to be used.
If the Quantity can have a trailing zero it can’t be a number unless you use Currency.

Or, you may be able to use a String data type.

Sub Test()
    Dim cQuantity As Currency
    Dim sQuantity As String
    Dim Dozens    As Integer
    Dim Singles   As Integer
    Dim PackSize  As Integer
    
    [color=green]' cQuantity as Currency[/color]
    cQuantity = 12.12
    PackSize = 24
    
    Dozens = (cQuantity Mod 100) * (PackSize / 12)
    Singles = (cQuantity - Int(cQuantity)) * 100
    
    MsgBox Dozens + Singles / 12
    
    
    [color=green]' Or
    ' sQuantity As String[/color]
    sQuantity = "12.12"
    MsgBox GetDozens(sQuantity, PackSize)
    
End Sub


Public Function GetDozens(Quantity As String, _
                          PackSize As Integer) As Single
    
    Dim Position  As Integer
    Dim Power     As Integer
    Dim Dozen     As Integer
    Dim Remainder As Integer
    Dim Singles   As String
    
    Dozen = Int(Split(Quantity, ".")(0)) * PackSize / 12
    Singles = Split(Quantity, ".")(1)
    
    Power = 1
    For Position = Len(Singles) To 1 Step -1
        Remainder = Remainder + (Asc(Mid(Singles, Position, 1)) - 48) * Power
        Power = Power * 10
    Next Position

    GetDozens = Dozen + Remainder / 12

End Function

HTH.

Chris.

 

[gemma-the-husky]

presumably you need some sort of scale factor in the unit or product table, and maybe a flag(s) to indicate

a) whether fractional units are permitted and maybe
b) whether fractional units are based on the scale factor

I think it's a matter of setting the tables up to generate this stuff.

 

[DCrake]

Thanks Chris. Now this is where it gets tricky.

A product has a Yield an opening stock, Purchases, Credits & closing stock all of which are expressed as afore mentioned.

Yield = PackSize (6,8,10,12,15,24) in this example i'll use 15
OS = 3.13 - 3x15 + 13 = 58
Pur = 6 - 6x15 = 90
Less any credits - .04 (4 units)
Less CS - 2.11 - 2x15 + 11 = 41

So in theory we started with 58 cans
Purchased another 90 cans
equals 148 cans
We did a stock take and we had 2.11 (41 cans left on the shelf)
and credits for 4 cans

So in total we consumed 103 cans (148 -45)

so bearing in mind we want to convert that to dozens the consumption would be 8 doz + 7 cans expressed as 8.07

So I have a function that accepts the Yield and the 4 sub totals and works out the consumption.

Your solution is similar to mine but more sleeker, however I will test it to see if the results are the same.

This is then used in a query to calc the consumption

I THINK