Why is (L \ 12) calculating to 0?

Question

I am trying to do calculations through VBA.

I am doing it through the form itself because when Production creates one of these Job Tickets a number of fields can change on the fly. Which Unit of Measure we are running in, which type of Wood, how we Wrap it, etc.

All of these changes affect the total footage or amount of pieces we have to run, which is why I have If-Then-Else statements for them.

It works until I get to Wrap SQ Footage. I get a zero inserted into my field, but when I do the calculations on my own I never get 0.

I created this expression in the control source of one of the Wrap SQ Footages, and it comes out correctly.

=Abs(Int( (([Wrap_Slit1]/12) * [Quantity_Ordered] ) * ( [RIP_Scrap_Rate] + 1))))

Private Sub FTG_Calculations()

    'Declare Variable
    Dim L As Double
    Dim Length As Double
    Dim OrderFTG As Double
    Dim UoM As String
    Dim W As Double
    Dim frm As Access.Form
    Set frm = Forms!Frm_JobTicket
    
    'Set L equal to Length from Tbl_JobTicketMould
    L = DLookup("Length", "Tbl_JobTicketMould", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
    
    'Convert Length to Feet
    Length = (L \ 12)
    
    'Find Unit of Measure for this part
    UoM = DLookup("Unit_of_Measure", "Tbl_JobTicketUoM", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
    
    'Mupltiply Length times Quantity to get Order Footage
    OrderFTG = Int((Length * Me.Txt_Pcs_JobTicket))
    
    'If UoM is PCS then insert that number. Otherwise set equal to Quantity Ordered divided by Length of piece(in FT)
    If UoM = "PCS" Then Me.Txt_Pcs_JobTicket = Me.Quantity_Ordered Else: Me.Txt_Pcs_JobTicket = Abs(Int(Me.Quantity_Ordered \ Length))
    
    'Define limits of the loop. Then runs through all Wrap SQ FTG fields and inputs calculation
    For W = 1 To 3
    
       'If UoM is PCS then calculate Order Footage to find Wrap Sqaure Footage. Otherwise take slit size in FT and multiply by Order Quantity and Scrap Rate
        If UoM = "PCS" Then
            frm("Txt_Wrap" & W & "SQFTG_JobTicket") = (((frm("Wrap_Slit" & W) \ 12) * OrderFTG) * (Round((frm("RIP_Scrap_Rate")), 3) + 1))
        Else: frm("Txt_Wrap" & W & "SQFTG_JobTicket") = (((frm("Wrap_Slit" & W) \ 12) * frm(Quantity_Ordered)) * (frm(RIP_Scrap_Rate + 1)))
        End If
    Next W

I figured out the issue is in the (frm("Wrap_Slit" & W) \ 12) area. Wrap_Slit1 shows a value of 2 in the data tips, but when I divide by 12 it comes out to 0.

All of my data points are set to double, and the variables are declared as double. It is rounding down when it should come out to .16667.

Answer 1

Place the following code before the:

 If UoM = "PCS" Then

Msgbox code:

MsgBox("Current State:" & vbCrLf & _
       "UoM:" & vbTab & UoM & vbCrlf & _
       "OrderFTGL" & vbTab & OrderFTG & _
       "Wrap_Slit1:" & vbTab & Me.Wrap_Slit1 & _
       ... continue pattern for other desired values in calculation...
       "Continue...", vbOK)