Help with Excel RoundDown Function

[dowlingm815]

I have spinning cycles on attempting to Round Down within VBA. The code is as follows. I would appreciate any assistance. Line 102 is where it is generating a compiler error of "Method or data member not found."

Private Sub Create_tbl_SOData_Table()
 
   On Error GoTo Err_Hndlr
 
 
strFirstRec = "Yes"
 
' **** initialize temporary fields, date is set to dummy initial date, it has no meaning
 
strTempSalesOrderId = 0
strTempSalesOrderNo = " "
strTempPO = " "
strTempPOID = 0
strTempUnitPrice = 0
strTempExtendedPrice = 0
strTempShipmentPrice = " "
strTempAccountCode = " "
strTempInd = "D"
strTempFund = " "
strTempIndexOrg = " "
strTempRule = "J099"
strTempPOLineNo = 0
strTempDate = "03/19/2007"
strTempBuyer = " "
strTempProductDescription = " "
strTempQuantity = 0
strTempSKUCatalog = " "
 
Dim dbs As DAO.Database
Dim rstTemp As DAO.Recordset
Dim strSQL As String
 
Set dbs = CurrentDb()
 
'**** create output table
'Delete temporary tabl
DoCmd.RunSQL "DROP TABLE tbl_SOData;"
 
 strSQL = "SELECT SO_Data_Formatted.[Sales Order Id], " & _
                "SO_Data_Formatted.[Sales Order No], " & _
                "SO_Data_Formatted.[PO #], " & _
                "SO_Data_Formatted.[PO ID], " & _
                "SO_Data_Formatted.[Unit Price], " & _
                "SO_Data_Formatted.[Extended Price], " & _
                "SO_CFSummary.[Shipment Price], " & _
                "SO_CFSummary.Account AS [Account Code], 'D ' AS Ind, " & _
                "SO_CFSummary.Fund, " & _
                "SO_CFSummary.Index AS IndexOrg, " & _
                "'J099 ' AS RuleCode, " & _
                "SO_Data_Formatted.[PO Line #], " & _
                "SO_Data_Formatted.SODate, " & _
                "SO_Data_Formatted.[Buyer: First Name], " & _
                "SO_Data_Formatted.[Product Description], " & _
                "SO_Data_Formatted.Quantity, " & _
                "SO_Data_Formatted.[SKU/Catalog #], " & _
                "SO_Data_Formatted.[Sales Order No] " & _
        "FROM SO_CFSummary " & _
        "INNER JOIN SO_Data_Formatted " & _
        "ON (SO_CFSummary.[Sales Order Line Number] = SO_Data_Formatted.[PO Line #]) " & _
        "AND (SO_CFSummary.[Sales Order Id] = SO_Data_Formatted.[Sales Order Id]) " & _
        "WHERE (((SO_Data_Formatted.[SODate]) Between #" & [Forms]![F_Import_DATA]![txb_date_start] & "# And #" & [Forms]![F_Import_DATA]![txb_date_end] & "#)) " & _
        "ORDER BY SO_Data_Formatted.[Sales Order No];"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tbl_SOData( [Sales Order Id] Integer, " & _
                                        "[Sales Order No#] Varchar (10), " & _
                                        "[PO #] Varchar(8), " & _
                                        "[PO ID] Integer, " & _
                                        "[Unit Price] money, " & _
                                        "[Extended Price] money, " & _
                                        "[Shipment Price] money, " & _
                                        "AccountCode Varchar(6), " & _
                                        "Ind Varchar(1), " & _
                                        "Fund Varchar(6), " & _
                                        "IndexOrg Varchar(6), " & _
                                        "RuleCode VARCHAR(4),  " & _
                                        "[PO Line No#] Integer, " & _
                                        "S0Date DATETIME, " & _
                                        "Buyer VARCHAR(50), " & _
                                        "[Product Description] VARCHAR(150),  " & _
                                        "Quantity Integer, " & _
                                        "[SKU/Catalog #] VARCHAR(50))")
 
Set rstTemp = dbs.OpenRecordset(strSQL)
Set rstSummary = CurrentDb.OpenRecordset("tbl_SOData")
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
 
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
    End If
 
    strTempSalesOrderId = rstTemp![Sales Order Id]
    strTempSalesOrderNo = rstTemp![Sales Order No]
    strTempPO = rstTemp![PO #]
    strTempPOID = rstTemp![PO ID]
 
    If Not IsNull(rstTemp![Unit Price]) Then
        strTempUnitPrice = rstTemp![Unit Price]
        strTempUnitPrice = Application.WorksheetFunction.RoundDown(strTempUnitPrice, 2)
    Else
         strTempUnitPrice = 0
    End If
    If Not IsNull(rstTemp![Extended Price]) Then
        strTempExtendedPrice = Application.WorksheetFunction.RoundDown((rstTemp![Extended Price]), 2)
    Else
         strTempExtendedPrice = 0
    End If
    If Not IsNull(rstTemp![Shipment Price]) Then
        strTempShipmentPrice = Application.WorksheetFunction.RoundDown((rstTemp![Shipment Price]), 2)
    Else
        strTempShipmentPrice = 0
    End If
    strTempAccountCode = rstTemp![Account Code]
 
     strTempFund = rstTemp!Fund
     strTempIndexOrg = rstTemp!IndexOrg
    strTempPOLineNo = rstTemp![PO Line #]
    strTempDate = rstTemp![SODate]
    strTempBuyer = rstTemp![Buyer: First Name]
    strTempProductDescription = rstTemp![Product Description]
    strTempQuantity = rstTemp![Quantity]
    strTempSKUCatalog = rstTemp![SKU/Catalog #]
 
 
        rstSummary.AddNew
 
            rstSummary![Sales Order Id] = strTempSalesOrderId
            rstSummary![Sales Order No#] = strTempSalesOrderNo
            rstSummary![PO #] = strTempPO
            rstSummary![PO ID] = strTempPOID
            rstSummary![Unit Price] = strTempUnitPrice
            rstSummary![Extended Price] = strTempExtendedPrice
            rstSummary![Shipment Price] = strTempShipmentPrice
            rstSummary!AccountCode = strTempAccountCode
            rstSummary!Ind = strTempInd
            rstSummary!Fund = strTempFund
            rstSummary!IndexOrg = strTempIndexOrg
            rstSummary!RuleCode = strTempRule
            rstSummary![PO Line No#] = strTempPOLineNo
            rstSummary!S0Date = strTempDate
            rstSummary!Buyer = strTempBuyer
            rstSummary![Product Description] = strTempProductDescription
            rstSummary!Quantity = strTempQuantity
            rstSummary![SKU/Catalog #] = strTempSKUCatalog
 
 
        rstSummary.Update
 
 
        strTempSalesOrderId = 0
        strTempSalesOrderNo = " "
        strTempPO = " "
        strTempPOID = 0
        strTempUnitPrice = 0
        strTempExtendedPrice = 0
        strTempShipmentPrice = " "
        strTempAccountCode = " "
 
        strTempFund = " "
        strTempIndexOrg = " "
 
        strTempPOLineNo = 0
 
        strTempBuyer = " "
        strTempProductDescription = " "
        strTempQuantity = 0
        strTempSKUCatalog = " "
 
    rstTemp.MoveNext
Loop
 
rstTemp.Close
rstSummary.Close
 
 
Create_tbl_SOData_Exit:
  Exit Sub
 

[ADezii]

I'm a little confused, dowlingm815. Are you attempting to utilize Excel's RoundDown() Worksheet Function from within Access in Line #102? If so, that approach will never work.

[dowlingm815]

Hey Adezii,

Nice to hear from you. Yes, I need to round down, if the price is 2.667, it needs to drop the values pass the second digit, 2.66. I've tired cast, decimal, I'm running out of ideals.

Mary

[NeoPa]

My best guess is that the OP is trying to do just that ADezii. It's hard to be sure because there is so little information in the question other than hundreds of lines of code. I can't say I'm overly amused.

I should make clear that the WorksheetFunction class is a member of the Excel Application object only. This is not general VBA, and needs to be made available to an Access Project (Tools / References in the VBA IDE) before use. It would then be referenced as :

Excel.WorksheetFunction.RoundDown()

F2 from the VBA IDE provides a way (The Object Browser) to negotiate the class structures associated with each application or library. This can be extremely helpful for a developer.

[NeoPa]

A VBA specific approach might be to use the Fix() function. This returns a rounded down integral value, but would need to be used with multiplying and dividing to handle numbers of decimal places :

strTempUnitPrice = CDbl(Fix(Val(strTempUnitPrice) * 100)) / 100

I assume you know the limitations of storing your numeric values in string variables.

[ADezii]

'NOTE: You must 1st set a Reference to the Microsoft Excel XX.X Object Library
Dim appExcel As Excel.Application      'Declare in a Broader Scope
 
Set appExcel = New Excel.Application
 
Debug.Print appExcel.WorksheetFunction.RoundDown("32.519123", 2)
 
'Execute after processing Recordset
appExcel.Quit
Set appExcel = Nothing

OUTPUT:

32.51

[NeoPa]

Application Automation (as you have illustrated) is actually not necessary ADezii. As long as the Microsoft Excel XX.XX Object Library is referenced, the method can be called (See in post #4 how it is referenced in code).

[ADezii]

Good point, NeoPa, and an oversight on my part.

[dowlingm815]

Gentlmen,

Thank you for your assistance. I opted for using for using CDbl(Fix(Val(field))) approach to be consistent with the code. I apppreciate all of your input.

Mary