Home > Enterprise >  How is this code calculating out to 0? Help appreciated
How is this code calculating out to 0? Help appreciated

Time:08-19

I am trying to do calculations through VBA, although I am not a veteran coder. I am doing it through the form itself because when Production goes to create one of these Job Tickets a number of very important fields can change on the fly, such as which Unit of Measure we are running in, which type of Wood, how we Wrap it, etc. All of these changes would affect the total footage or amount of pieces we have to run, which is why I have been making If-Then-Else statements for them, and it has worked out well so far until I get to Wrap SQ Footage. I am continually getting a zero inserted into my field, but when I do the calculations on my own I have never gotten 0. Code is below, any help or suggestions are very much appreciated.

I have created this expression in the control source of one of the Wrap SQ Footages, and it comes out correctly. I can't see what is the difference between these in terms of the math they are doing.

=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 have figured out so far that the issue is in the (frm("Wrap_Slit" & W) \ 12) area. For some reason Wrap_Slit1 shows a value of 2 in the data tips, but when I divide by 12 it comes out to 0. I have all of my data points set to double, and the variables I have declared to double so I have no clue why it is rounding it down when it should come out to .16667

CodePudding user response:

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)
  • Related