Formatting 3rd column based on 2 calcuated columns

[accessNator]

Here is a sample of a record from a table

Table_1
ref_Id, original_Ref_Id, sub_Total, rate_Multiplier, net_total
1, 0, 50, 0.0425, 2.125
2, 0, 120, 0.0425, 5.1
3, 1, 150, 0.0425, 6.375

My goal is to create a calculated field called final_Amt in a query based on two calculated columns. Here is my setup:

1. I Round my net_Total as a new calculated column to two decimal places and call it current_Total.
2. If the original_Ref_Id column is greater than 0 then I do a Dlookup function of the column original_Ref_Id which equals the value of the ref_Id and returns the respective net_Total as my NEW calculated original_Total column, and I would like to round it to two decimal places.

So my SQL query looks like this:

Select ref_Id, orig_Ref_Id, sub_Total, rate_Multiplier, net_Total, Round(net_Total,2) as current_Total, IIF(original_Ref_Id > 0, Round(DLookUp('net_Total', 'Table_1', 'ref_Id = ' & original_Ref_Id),2)) As original_Total from Table_1;

Everything works here as expected, but now I want to create a third calculated column based on the 2 calculated columns I just created and round the Amount to two decimal places.

The added criteria is:
If my original_Ref_Id column is greater than 0, then I create an IF statement condition to return an final_Amt column. So If the original_Ref_Id is greater than 0 then, current_Amount - original_Amount, otherwise current_Amount

The Query looks like this:
Select ref_Id, orig_Ref_Id, sub_Total, rate_Multiplier, net_Total, Round(net_Total,2) as current_Total, IIF(original_Ref_Id > 0, Round(DLookUp('net_Total', 'Table_1', 'ref_Id = ' & original_Ref_Id),2)) As original_Total , IIf([orig_Ref_Id] > 0,current_Total - original_Total], current_Total) AS final_Amt from Table_1;

But my final result, the final_Amt column which is a calculated column is not formatted properly. Instead of two decimal places based on my calculated columns, it goes out to at least 8 places in some of my records returned.

My question is how do I format my 3rd calculated column to always display 2 decimal places? I know I can format it in my properties setting in my Query grid IF i create the query from there, but in reality this Query will be created on demand using VB code.

If it helps, I can provide my VB code that creates the query.

Thanks in advance,
Chuck

 

[vbaInet]

Round() the WHOLE calculation inside that field or just create another aliased field and round that newly calculated field

 

[accessNator]

Round() the WHOLE calculation inside that field or just create another aliased field and round that newly calculated field

I cant believe that I didnt think of that...I guess I was so stuck doing it one way, I stopped thinking of the alias alternative. Thanks. I did that. I created another alias field and rounded it.

Thanks again vbaInet!

 

[vbaInet]

Your eyes get tired and the brain drains when you've looked at a problem over and over again, until you get that "aha!!" moment.

Glad to have helped.

 

[accessNator]

Your eyes get tired and the brain drains when you've looked at a problem over and over again, until you get that "aha!!" moment.

Glad to have helped.

vbaInet or anyone else,
I created this query using VBA. I know using the Round function does work when I create it using the standard query setup or VBA. But here is my problem now. My next step is using the result of that query in a 'Union ALL' query. The column that I am retrieving to use in the "Union ALL' query is the column which resulted in the ROUND function being used as stated above. But when I execute the VBA code that creates the 'Union ALL' query, I get an error message stating:

Run-Time error '5':
Invalid procedure call or arguement.

I know it is the column which I am retreiving which is the alias field that used the ROUND function is causing this error. How do I get around this?
I know the ROUND function does work if I hard code it in a 'Union ALL' query, but if I create the creating the query dynamically coding it in vba, it cause the error.

Suggestion? Hope this made sense.
-Chuck

Edit: I previously stated 'Union Select', I edited to reflect 'Union All'.

 

[vbaInet]

Show the sql statement and/vba code you're referring to?

 

[accessNator]

Re: Formatting 3rd column based on 2 calculated columns

Show the sql statement and/vba code you're referring to?

This is going to look a bit different from what my original post because I didnt want to add so much detail to confuse. But here is my actual VBA coding example below. Thanks in advance.

Private Sub CommandHistory_Click()

    Dim db As DAO.Database
    Set db = CurrentDb
    
    Const Query1 As String = "qryKusfDataHistoryByMonthTransactions"
    Const Query2 As String = "qryKusfDataHistoryByMonthTransactionsLatePenalty"
    Const Query3 As String = "qryUnionTransactions"
   
On Error GoTo Err_CommandHistory_Click

    Me.txtStatus.ForeColor = vbBlue
    Me.txtStatus.Value = "Starting History"
    Me.Repaint
    
    On Error Resume Next

    ' Delete Querie(s) If exist
    db.QueryDefs.Delete Query1
    db.QueryDefs.Delete Query2
    db.QueryDefs.Delete Query3
    On Error GoTo 0
             
    CompanyHistoryByPeriodTransactions2 Query1
    CompanyHistoryByPeriodTransactions2 Query2
    
    'UnionTransactions
    CompanyHistoryUnionTransaction Query1, Query2, Query3
    
    Me.Repaint
    Me.txtStatus.ForeColor = vbBlue
    Me.txtStatus.Value = "Finished!"
    Me.CboIdSearch.SetFocus
    
    ' Audible Beep
    DoCmd.Beep
       
    Set db = Nothing
    
Exit_CommandHistory_Click:
    Exit Sub

Err_CommandHistory_Click:
    DoCmd.Beep
    MsgBox Err.description
    Resume Exit_CommandHistory_Click
End Sub

The line strSQL = strSQL & "Round(amt1,2) as amt is where I use the ROUND function and getting a alias column as you suggested

Private Sub CompanyHistoryByPeriodTransactions2(passQuery As String)

    Dim db As DAO.Database
    Set db = CurrentDb
          
    Dim qdf1 As DAO.QueryDef
    Dim rst1 As DAO.Recordset

    Dim strSQL As String
       
    ' Create Sql String From Table1
    Select Case passQuery
        Case "qryKusfDataHistoryByMonthTransactions"
            strSQL = "SELECT ref_id, wid, cid, tblCompanyInformation.company_name as company, submission_date, report_month, period_start, period_end, original_wid, revision, true_up, net_kusf_assessment_payment, "
            strSQL = strSQL & "Round([net_kusf_assessment_payment],2) AS current_wid_net, "
            strSQL = strSQL & "IIF(isnull(original_wid) or original_wid = 0 , 0, Round(DLookUp('[net_kusf_assessment_payment]','tblKusfFundData','[wid] =' & [original_wid]),2)) AS original_wid_net, "
            strSQL = strSQL & "IIf([original_wid] > 0,current_wid_net - [original_wid_net], current_wid_net) AS amt1, "
            [COLOR=Red][B]strSQL = strSQL & "Round(amt1,2) as amt[/B][/COLOR], "
            strSQL = strSQL & "IIF(revision = 0,'Original CRW', IIF(revision = -1 and true_up = 0, 'Revised CRW', 'True Up CRW')) as WorksheetDescription "
            strSQL = strSQL & "FROM tblCompanyInformation INNER JOIN tblKusfFundData ON tblCompanyInformation.company_cid = tblKusfFundData.cid;"

        Case "qryKusfDataHistoryByMonthTransactionsLatePenalty"
            strSQL = "SELECT ref_id, wid, cid, tblCompanyInformation.company_name as company, submission_date, report_month, period_start, period_end, original_wid, revision, true_up,net_kusf_assessment_payment, "
            'strSQL = strSQL & "DLookUp('[net_kusf_assessment_payment]','tblKusfFundData','[wid] =' & [original_wid]) AS original_net, [net_kusf_assessment_payment]-[original_net] AS diff_net, "
            strSQL = strSQL & "IIf([late_charge] <> 0,[late_charge],0) AS amt, "
            strSQL = strSQL & "IIF([late_charge] <> 0, 'Worksheet Late Penalty', 'Worksheet Late Penalty') as transaction_description "
            strSQL = strSQL & "FROM tblCompanyInformation INNER JOIN tblKusfFundData ON tblCompanyInformation.company_cid = tblKusfFundData.cid "
            strSQL = strSQL & "WHERE [late_charge] <> 0;"
   
   End Select
   
   ' Initialize Query
    Set qdf1 = db.CreateQueryDef(passQuery, strSQL)
    Set rst1 = qdf1.OpenRecordset(dbOpenDynaset)

    Set rst1 = Nothing
    Set db = Nothing

End Sub

The code where I reference passQuery1 & ".amt is where I am getting the error message that I stated.

Private Sub CompanyHistoryUnionTransaction(passQuery1 As String, passQuery2 As String, passQuery3 As String)
    Dim db As DAO.Database
    Set db = CurrentDb
         
    Dim qdf1 As DAO.QueryDef
    Dim rst1 As DAO.Recordset

    Dim strSQL As String
    
    strSQL = "SELECT " & passQuery1 & ".cid As cid, " & passQuery1 & ".company, " & passQuery1 & ".report_month As reporting_period, " & [B][COLOR=Red]passQuery1 & ".amt[/COLOR][/B], " & passQuery1 & ".WorksheetDescription As Description "
    strSQL = strSQL & "From " & passQuery1 & ";"
    strSQL = strSQL & "Union All "
    strSQL = strSQL & "SELECT " & passQuery2 & ".cid As cid, " & passQuery2 & ".company, " & passQuery2 & ".report_month As reporting_period, " & passQuery2 & ".amt, " & passQuery2 & ".transaction_description  As Description "
    strSQL = strSQL & "From " & passQuery2 & " "
    strSQL = strSQL & "ORDER BY 1, 3;"
   
   ' Initialize Query
    Set qdf1 = db.CreateQueryDef(passQuery3, strSQL)
    Set rst1 = qdf1.OpenRecordset(dbOpenDynaset)
    

    Set rst1 = Nothing
    Set db = Nothing
End Sub

 

[boblarson]

This:
strSQL = strSQL & "IIf([original_wid] > 0,current_wid_net - [original_wid_net], current_wid_net) AS amt1, "
strSQL = strSQL & "Round(amt1,2) as amt, "

Should actually be:
strSQL = strSQL & "Round(IIf([original_wid] > 0,current_wid_net - [original_wid_net], current_wid_net),2) AS amt, "

And get rid of this:
strSQL = strSQL & "Round(amt1,2) as amt, "

Also your

strSQL = strSQL & "ORDER BY 1, 3;"

should be using the names not the ordinal position.

strSQL = strSQL & "ORDER BY cid, reporting_period;"

 

[accessNator]

This:
strSQL = strSQL & "IIf([original_wid] > 0,current_wid_net - [original_wid_net], current_wid_net) AS amt1, "
strSQL = strSQL & "Round(amt1,2) as amt, "

Should actually be:
strSQL = strSQL & "Round(IIf([original_wid] > 0,current_wid_net - [original_wid_net], current_wid_net),2) AS amt, "

And get rid of this:
strSQL = strSQL & "Round(amt1,2) as amt, "

Also your

strSQL = strSQL & "ORDER BY 1, 3;"

should be using the names not the ordinal position.

strSQL = strSQL & "ORDER BY cid, reporting_period;"

Thanks for your reply BobLarson,
I have been going crazy in trying to figure this error out. I went and did your suggestion and I still got my error message. But I see what you were doing which is very similar in what vbainet proposed a few post earlier. I finally realized what is causing this. Everytime, I got the error message and attempt to debug it would show it to break in a ROUND function that I implemented in a common module. This Round function is created by Ken Getz custom function of ROUNDING

e.g.

Public Function Round( _
ByVal Number As Variant, NumDigits As Long, _
Optional UseBankersRounding As Boolean = False) As Double
'
' ---------------------------------------------------
' From "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
' ---------------------------------------------------
'
  Dim dblPower As Double
  Dim varTemp As Variant
  Dim intSgn As Integer

  If Not IsNumeric(Number) Then
    ' Raise an error indicating that
    ' you've supplied an invalid parameter.
    Err.Raise 5
  End If
  dblPower = 10 ^ NumDigits
  ' Is this a negative number, or not?
  ' intSgn will contain -1, 0, or 1.
  intSgn = Sgn(Number)
  Number = Abs(Number)

  ' Do the major calculation.
  varTemp = CDec(Number) * dblPower + 0.5
  
  ' Now round to nearest even, if necessary.
  If UseBankersRounding Then
    If Int(varTemp) = varTemp Then
      ' You could also use:
      ' varTemp = varTemp + (varTemp Mod 2 = 1)
      ' instead of the next If ...Then statement,
      ' but I hate counting on TRue == -1 in code.
      If varTemp Mod 2 = 1 Then
        varTemp = varTemp - 1
      End If
    End If
  End If
  ' Finish the calculation.
  Round = intSgn * Int(varTemp) / dblPower
End Function

I am using Access 2003 and I had some rounding issues to address and I thought I read somewhere, if I want my calculations to round properly in my VBA code calculation, I had to implement Ken Getz custom ROUND function.

So, I was assuming that I was using that same ROUND function in my Regular QUERIES and VBA code based on the custom code. But in reality, if I have that ROUND function declared in my module, it creates a conflict in my VBA code versus the ROUND function Access 2003 uses, Correct?

Do I even need to use Ken G's round function in my VBA code for rounding? The rounding issue is a problem when my calculation tries to use Bankers Rounding. i.e. 0.125 rounds to 0.12 (2 is even), whereas 0.135 rounds to 0.14

Hope that made sense to you where rounding in Access confuses me.

 

[boblarson]

The round function that Ken supplied was for versions of Access that didn't have that built in (as are many functions that start out that way). So you shouldn't have to use his function. However, if you still have issues around the .125, .135 part when you use the built in function I think there is a specific user-defined function that someone wrote to deal with that.

 

[accessNator]

The round function that Ken supplied was for versions of Access that didn't have that built in (as are many functions that start out that way). So you shouldn't have to use his function. However, if you still have issues around the .125, .135 part when you use the built in function I think there is a specific user-defined function that someone wrote to deal with that.

Hello Bob and All,
So to make sure I understand this. If I use Round(AnyNumber,2) in my vba code, I should be fine since I am using Access 2003. I dont have to create a custom round function. But in the case where there is rounding issues of the .125, .135 I should use a user-defined Round function?

If so, is this custom round function you are referring too? Then would I use this for all of my vba code that uses Round function?

[INDENT][FONT=Courier][SIZE=1]Function Round_TSB(dblNumber As Double, intDecimals As Integer) As Double
  Dim dblFactor As Double
  Dim dblTemp As Double[/SIZE][/FONT]         [/INDENT][INDENT]    [FONT=Courier][SIZE=1]dblFactor = 10 ^ intDecimals
  dblTemp = dblNumber * dblFactor + .5

  Round_TSB = Int(CDec(dblTemp)) / dblFactor

End Function[/SIZE][/FONT]         [/INDENT]

 

[boblarson]

I believe that is what I was referring to and yes, if you are in need of ensuring the correct rounding for those type of numbers then I would suggest using this function in all places where you would be thinking of using the simple Round function.

 

[accessNator]

I believe that is what I was referring to and yes, if you are in need of ensuring the correct rounding for those type of numbers then I would suggest using this function in all places where you would be thinking of using the simple Round function.

Thank you Bob for the clarification. This has helped me and I am sure this will help a lot of posters in the future.