QuickBooks: Distributing Overhead Using QODBC sp_report ProfitByCustomer WHEN NOT ALL EXPENSES WERE ASSIGNED TO JOBS

Annaliese DellSec-Treas
QODBC and tech tips
Published:
Oh no! Did you forget to:

1. Set up QuickBooks Payroll for Job Costing?
2. Check Payroll Items to Track Expenses By Job?
3. Assign Overhead to expenses not belonging to a particular job?

Not sure if you did or didn't do any of the above?

Find out quickly by running the QuickBooks Profit & Loss Standard report. Select a date macro that includes a lot of transactions. An entire year is best.

Next:

1. Set columns to Total Only and note the Net Profit at the bottom.
2. Set columns to Customer:Job and note the Net Profit at the bottom.

If the two Net Profits do not match, NOT all expenses were assigned to jobs. Quickly fix the features you forgot to avoid this dilemma going forward.

For past transactions, this article provides a work around.

Applications/Utilities required:

1. Microsoft Access
2. QOCBC
3. QuickBooks

Knowledge required:

1. Basic understanding of VBA
2. How to create a Microsoft Access form
3. How to use the Visual Basic Editor
4. How to run QuickBooks reports

You can simply create the form and copy the code below into it. If you want to understand the process so you can alter it to fit your needs, an explanation follows. Be warned, however, that it may seem confusing. A good many calculations and loops are required. The end result, however, is tidy and handy.

This method uses the QODBC sp_report ProfitAndLossStandard to:

 1. run the Profit & Loss Standard for ALL
 2. run the Profit & Loss Standard for Overhead 
 3. run the Profit & Loss Standard for EACH customer
 4. calculate amounts not applied to jobs or overhead
 5. add NoJob amounts to Overhead amounts for TotalOverhead
 6. distribute TotalOverhead among jobs according to each job's percent of payroll
 
This outline explains the steps. Sample code for DAO is provided.

1.  Create a Main table in Microsoft Access using QODBC sp_report ProfitAndLossStandard
2.  Add columns:
      a.  TotalGross
      b.  TotalNets

3.  Create a temporary table using QODBC sp_report ProfitAndLossStandard filtered by 'Overhead'. Add fields:
     a.  Overhead_Gross
     b.  Overhead_OH  (dropped later)
     c.  Overhead_Net (dropped later)

4.  Transfer temporary tabel data to main table
5.  Add column: NoJob for the expenses that were not assigned to either jobs or overhead
6.  Repeat steps 3-5 for each customer EXCEPT Overhead. (already done)
7.  Drop columns in Main table:
     a.  Overhead_OH
     b.  Overhead_Net

At this point the table has these columns:

 a.  Description: from initial profit report by TotalOnly
 b.  Amount: from initial profit report by TotalOnly
 c.  TotalGross: Total of Overhead + Each Job + No Job Assigned
 d.  TotalNets: Total of Each Job + Overhead + Each Job's Share of Overhead
 e.  Overhead_Gross: from profit report filtered by 'Overhead'
 f.  NoJob: subtract (Overhead + Each Job) from Amount
 g.  Columns for each job:
     i. Job_Gross: from Profit report filtered by customer
     ii. Job_OH: Job's share of overhead
     iii. Job_Net: Job's Gross + Job's share of overhead

The table should look similar to this with the three job columns repeating for each job.

Table1.png
Next you need to fill in the blanks by calculating the NoJob, Overhead_Gross and TotalGross amounts.
 
 LOOP through the Main table RECORDS
                            LOOP through the Main table COLUMNS stopping at each "_Gross" column
                                 calculate dblGross by adding values
                            NEXT COLUMN
                            NoJob = dblGross - Amount
                            Overhead_Gross = Overhead_Gross + NoJob
                            TotalGross  = dblGross + NoJob
                            dblGross=0
                       NEXT RECORD
                      

Open in new window


The Overhead_Gross column now includes NoJob amounts so you can distribute that figure among jobs. But how?

This method uses payroll to calculate Percent of Overhead. The premise is that if each job took a certain percent of Total Payroll to complete, that job should also take the same percent of Overhead. To do this:

 1. Calculate Total Payroll minus Overhead Payroll as dblPayroll.
 2. Divide each job's payroll by dblPayroll to get each job's Percent of Total Payroll as dblPercentPayroll
 3. Multipy Overhead_Gross by dblPercentPayroll to get each job's SHARE of Overhead as Job_OH

The code does it this way:

  Extract Total Payroll by DLookup

  Extract Overhead Payroll by DLookup
  LOOP through COLUMNS stopping at each COLUMN like "*_Gross" (excluding Overhead)
       Extract Job Payroll by DLookup as dblJobPayroll
       Divide Job Payroll by Total Payroll to get Percent of Payroll as dblPercentPayroll
            LOOP through the records
                Multiply dblPercentPayroll by Overhead_Gross to get Job_OH
                * (each job's share of overhead is Overhead_Gross X that job's percent of payroll)
            NEXT RECORD
           dblJobPayroll=0
           dblPercentPayroll=0
  NEXT COLUMN
Using this method means any job with no payroll gets no share of overhead. For example, if Sale of Asset is assigned to Customer:Cash Sale, there is no payroll for Cash Sales and therefore no share of overhead.

Now that you have each Job's cost + share of overhead, you can add them together for each Job's NET.

The code does it this way:
 LOOP through Main table COLUMNS stopping at each column like "*_Net"
       LOOP through Main table RECORDS
            Calculate Job_Net by adding Job_Gross and Job_OH
       NEXT RECORD
 NEXT COLUMN
Also handy is adding all the JOB NETS for TotalNets to make sure Amount, TotalGross and TotalNets all match. The code does it this way:
 LOOP through Main Table RECORDS
        LOOP through Main table COLUMNS stopping at each column like "*_Net"
               add each value to dblTotal
        NEXT COLUMN
        TotalNets=dblTotal
        dblTotal=0
 NEXT RECORD 
  
Finishing Touches

The resulting table contains a lot of data, much of it for calculation purposes. You may want to delete all zero rows using fncDeleteRecord (included) or your own procedure.

You may also want to delete the following columns:

 1. NoJob
 2. like "*_OH"
 3. like "*_Gross"

Now the table should look similar to this. Amounts in the Amount, TotalGross and TotalNets columns should all equal each other.

The Overhead_Gross column will equal the first three columns when none of the transactions for those accounts were applied to jobs.

The Overhead_Gross column will NOT equal the first three columns when some of the transactions were applied to jobs but others were applied to Overhead.

table2.pngAfter examining the table, you may also wish to also delete the columns:

 1. TotalGross
 2. TotalNets

Creating a Form

Create a form containing:

    1. Listbox with One column: lstCustomer
    2. TextBox: DateFrom
    3. TextBox: DateTo
    4. CommandButton: cmdCreateTable
    5. CommandButton: cmdDropCustomerOHColumns
    6. CommandButton: cmdDropCustomerGrossColumn s
    7. CommandButton: cmdOpenTable

Add error trapping and other buttons and options as desired.

The code for the form follows. In the interest of brevity and because long lines of code can be intimidating and confusing, all but the most essential error trapping and module references were removed (for example, no trapping for existing table or query, etc.). Essential public procedures and functions follow in another  snippet below. You can substitute your own or use the ones provided.
 
Option Compare Database
                      Option Explicit
                      
                      Private Sub cmdOpenTable_Click()
                      DoCmd.OpenTable "ProfitByCustomer"
                      End Sub
                      
                      Private Sub Form_Open(Cancel As Integer)
                      On Error GoTo Form_Open_err
                      100     lstCustomer.RowSource = ""
                      110     Call fncQODBCQuery("Select FullName from Customer where sublevel=0", True)
                      120     Call fncRunSQL("select * into tblCustomer_RL from qryTemp")
                      130     lstCustomer.RowSource = "select FullName from tblCustomer_RL"
                      Exit Sub
                      Form_Open_err:
                      Debug.Print "Form_Open", Erl, Err.Number, Err.Description
                      End Sub
                      
                      Private Sub cmdCreateTable_Click()
                      On Error GoTo cmdCreateTable_Click_err
                      
                      '*** Make Total Profit Report
                      100     Dim t As String, i As Integer
                      110     t = "ProfitByCustomer"
                      120     Call fncProfitTable(t, DateFrom, DateTo)
                      
                      '*** add columns
                      130     Call fncAddField(t, "TotalGross", dbDouble, False)
                      140     Call fncAddField(t, "TotalNets", dbDouble, False)
                      
                      '*** add overhead first so it comes before the customer data
                      150     Call PutGross("tblTemp", t, "Overhead")
                      160     Call fncAddField(t, "NoJob", dbDouble, False)
                      
                      '*** iterate through customers
                      300     For i = 0 To lstCustomer.ListCount - 1
                      310         If lstCustomer.ItemData(i) <> "Overhead" Then
                      320             Call PutGross("tblTemp", t, lstCustomer.ItemData(i))
                      330         End If
                      340     Next i
                      
                      '*** drop unnecessary Overhead columns
                      400     Call fncRunSQL("ALTER TABLE " & t & " DROP COLUMN [Overhead_OH]")
                      410     Call fncRunSQL("ALTER TABLE " & t & " DROP COLUMN [Overhead_Net]")
                      
                      '*** perform calculations
                      500     Call PutTotals(t)
                      510     Call PutOverhead(t)
                      520     Call PutNet(t)
                      530     Call TotalNets(t)
                      
                      '*** delete zero rows and drop NoJob column
                      540     Call fncDeleteRecord(t, "Amount", "0")
                      560     Call fncRunSQL("ALTER TABLE " & t & " DROP COLUMN [NoJob]")
                      57      DoCmd.OpenTable t
                      Exit Sub
                      
                      cmdCreateTable_Click_err:
                      Debug.Print "cmdCreateTable_Click", Erl, Err.Number, Err.Description
                      End Sub
                      
                      Private Sub PutGross(sTableFrom As String, sTableTo As String, sCustomer As String)
                      On Error GoTo PutGross_err
                      
                      '*** create a viable Customer column by removing spaces, etc.
                      100     Dim sColumn As String
                      110     sColumn = fncColumn(sCustomer)
                      
                      '*** import the data for this customer into a temporary table
                      120     Call fncProfitTable("tblTemp", DateFrom, DateTo, sCustomer)
                      
                      '*** if this customer had no activity, don't add it
                      130     If Nz(DSum("[AMOUNT]", "tblTemp", "[DESCRIPTION]='NET INCOME'"), 0) = 0 Then Exit Sub
                      
                      '*** add additional columns for this customer to the Main table
                      150     Call fncAddField(sTableTo, sColumn & "_Gross", dbDouble, False, 20)
                      160     Call fncAddField(sTableTo, sColumn & "_OH", dbDouble, False, 20)
                      170     Call fncAddField(sTableTo, sColumn & "_Net", dbDouble, False, 20)
                      
                      '*** loop through the temporary table updating the Main table
                      '*** you may wish to do this using two recordsets instead of the Update Record method
                      '*** the two recordset method is faster, just more code lines
                      
                      200     Dim db As DAO.Database, rs As DAO.Recordset
                      210     Set db = CurrentDb
                      220     Set rs = db.OpenRecordset(sTableFrom)
                      
                      300     If rs.RecordCount > 0 Then
                      310         rs.MoveLast
                      320         rs.MoveFirst
                      330         Do While Not rs.EOF
                      340             Call fncRunSQL("Update " & sTableTo & _
                                      " set " & sColumn & "_gross = " & rs("Amount").Value & _
                                      " where Description='" & rs("Description") & "'")
                      350         rs.MoveNext
                      360         Loop
                      380     End If
                      390     rs.Close
                      400     Set rs = Nothing
                      410     Set db = Nothing
                      Exit Sub
                      PutGross_err:
                      Debug.Print "cmdCreateTable_Click", Erl, Err.Number, Err.Description
                      End Sub
                      
                      Private Sub PutTotals(sTableName As String)
                      On Error GoTo PutTotals_err
                      
                      '*** Step 1. Add all Cutomer_Gross + Overhead_Gross to get dblGross
                      '            This amount will differ from Amount column if all expenses were not assigned a job or overhead
                      '*** Step 2. Subtract Amount from dblGross for the NoJob amount--expenses not assigned to jobs
                      '*** Step 3. Add the NoJob amount to the Overhead_Gross amount for the true Overhead amount
                      '            This equals: Jobs amounts + Overhead Amount + Amounts not assigned to jobs
                      '*** Step 4. Add the NoJob amouns to the TotalGross amounts
                      '            This is a double check column that should match the Amount column figures in the end
                      '            It shows that jobs + overhead + no jobs = amounts from profit report by TotalOnly
                      
                      100     Dim db As DAO.Database, rs As DAO.Recordset, i As Integer
                      110     Dim dblGross As Double, dblNoJob As Double
                      210     Set db = CurrentDb
                      220     Set rs = db.OpenRecordset(sTableName)
                      230 If rs.RecordCount > 0 Then
                      240     rs.MoveLast
                      250     rs.MoveFirst
                      300     Do While Not rs.EOF
                      310     For i = 0 To rs.Fields.Count - 1
                      320         If rs.Fields(i).Name Like "*gross*" Then
                      330             dblGross = dblGross + Nz(rs.Fields(i).Value, 0)
                      370         End If
                      380     Next i
                      400     rs.Edit
                      410     rs("NoJob") = FormatNumber((dblGross - rs!Amount) * -1)
                      420     rs.Update
                      430     rs.Edit
                      440     rs("Overhead_Gross") = FormatNumber(rs!overhead_gross + rs!NoJob)
                      450     rs("TotalGross") = FormatNumber(dblGross + rs!NoJob, 0)
                      460     rs.Update
                      470     dblGross = 0
                      490     rs.MoveNext
                      500     Loop
                      510 End If
                      520 rs.Close
                      530 Set rs = Nothing
                      540 Set db = Nothing
                      
                      Exit Sub
                      PutTotals_err:
                      Debug.Print "PutTotals", Erl, Err.Number, Err.Description
                      
                      End Sub
                      Private Sub PutOverhead(sTableName As String)
                      On Error GoTo PutOverhead_err
                      
                      '*** uses payroll to determine the percent of TotalOverhead to apply to each job
                      '*** gets the Total Payroll for all JOBs (excluding overhead payroll) as dblPayroll
                      '*** gets each Job Payroll as dblJobPayroll
                      '*** calculates percent of payroll attributable to each job as dblPercentPayroll
                      '*** adds all the job payroll percents and prints the total in the immediate window
                      '*** this should always equal 1 (for 100%)
                      
                      100     Dim dblPayroll As Double, dblJobPayroll As Double
                      110     Dim dblPercentPayroll As Double, dblTotalPercent As Double
                      120     Dim db As DAO.Database, rs As DAO.Recordset, i As Integer, sFieldName As String
                      
                      '*** get the total payroll from the Amount column (original Profit Report by TotalOnly)
                      '----------------------------------------------------------------------------------------------------------------
                      '!!!!!//////////////////////  MAKE SURE YOU REPLACE 'Total 66000 · Payroll Expenses' \\\\\\\\\\\\\\\\\\\\\\\\\\\
                      '!!!!!\\\\\\\\\\\\\\\\\\\\  WITH THE NAME OF YOUR TOTAL PAYROLL ACCOUNT FROM QUICKBOOKS   ////////////////////
                      '----------------------------------------------------------------------------------------------------------------
                      
                      130     dblPayroll = _
                              Nz(DLookup("[Amount]", sTableName, "[Description]='Total 66000 · Payroll Expenses'"), 0)
                      
                      '*** subtract the Overhead Payroll from dblPayroll for the Payroll attributable to jobs as dblPayroll
                      140     dblPayroll = _
                              dblPayroll - Nz(DLookup("[Overhead_Gross]", _
                              sTableName, "[Description]='Total 66000 · Payroll Expenses'"), 0)   '*** REPLACE 'Total 66000 · Payroll Expenses'
                      
                      '*** format dblPayroll
                      150     dblPayroll = FormatNumber(dblPayroll, 0)
                      
                      '*** open the Main table as a recordset
                      210     Set db = CurrentDb
                      220     Set rs = db.OpenRecordset(sTableName)
                      230     If rs.RecordCount > 0 Then
                      240     rs.MoveLast
                      250     rs.MoveFirst
                      
                      '*** LOOP through COLUMNS stopping at each "_Gross" EXCEPT "Overhead_Gross"
                      '*** these are the JOBS or CUSTOMERS
                      310     For i = 0 To rs.Fields.Count - 1
                      320         If rs.Fields(i).Name Like "*_gross*" And InStr(1, rs.Fields(i).Name, "overhead", vbTextCompare) = 0 Then
                      
                      '*** Get each Job's payroll with DLookup
                      '----------------------------------------------------------------------------------------------------------------
                      '!!!!!//////////////////////  MAKE SURE YOU REPLACE 'Total 66000 · Payroll Expenses' \\\\\\\\\\\\\\\\\\\\\\\\\\\
                      '!!!!!\\\\\\\\\\\\\\\\\\\\  WITH THE NAME OF YOUR TOTAL PAYROLL ACCOUNT FROM QUICKBOOKS   ////////////////////
                      '----------------------------------------------------------------------------------------------------------------
                      
                      330             dblJobPayroll = _
                                      Nz(DLookup(rs.Fields(i).Name, sTableName, "[Description]='Total 66000 · Payroll Expenses'"), 0)
                      
                      '*** Divide the Job's payroll by the Total Payroll to get each Job's Percent of Payroll
                      360             If Nz(dblPayroll, 0) <> 0 Then dblPercentPayroll = FormatNumber(dblJobPayroll / dblPayroll, 2)
                      
                      '*** start a new LOOP for each JOB
                      
                      370                rs.MoveFirst
                      '*** LOOP through each JOB's RECORDS multiplying each Overhead_Gross value by dblPercentPayroll
                      '*** this gives you the portion of overhead attributable to each job
                      '*** store that value in the JOB_OH field which is represented by rs(sFieldName)
                      380     Do While Not rs.EOF
                      390         sFieldName = Replace(rs.Fields(i).Name, "_Gross", "_OH")
                      400         rs.Edit
                      410         rs(sFieldName).Value = FormatNumber(Nz(rs("overhead_gross") * dblPercentPayroll, 0), 0)
                      420         rs.Update
                      430         rs.MoveNext
                      440     Loop
                      450     End If 'THIS IS A "_Gross" COLUMN AND ALSO NOT THE OVERHEAD_Gross COLUMN
                      
                      '*** RESET VARIABLES FOR NEXT COLUMN LOOP
                      460     dblJobPayroll = 0
                      470     dblPercentPayroll = 0
                      
                      480     Next i 'COLUMN
                      
                      510     End If 'RS.RECORDCOUNT>0
                      520     rs.Close
                      530     Set rs = Nothing
                      540     Set db = Nothing
                      
                      Exit Sub
                      PutOverhead_err:
                      Debug.Print "PutOverhead", Erl, Err.Number, Err.Description
                      
                      End Sub
                      Private Sub PutNet(sTableName As String)
                      On Error GoTo PutNet_err
                      '*** LOOP THROUGH COLUMNS ADDING EACH JOB_GROSS TO EACH JOB_OH TO GET EACH JOB_NET
                      
                      150     Dim db As DAO.Database, rs As DAO.Recordset, i As Integer, sFieldName As String, dblTotalNets As Double
                      210     Set db = CurrentDb
                      220     Set rs = db.OpenRecordset(sTableName)
                      230     If rs.RecordCount > 0 Then
                      240     rs.MoveLast
                      310     For i = 0 To rs.Fields.Count - 1
                      250     rs.MoveFirst
                      320     If rs.Fields(i).Name Like "*_Net" Then
                      330         sFieldName = Replace(rs.Fields(i).Name, "_Net", "")
                      340         Do While Not rs.EOF
                      350             rs.Edit
                      360             rs(sFieldName & "_Net").Value = Nz(rs(sFieldName & "_Gross").Value + rs(sFieldName & "_OH").Value, 0)
                      370             rs.Update
                      380         rs.MoveNext
                      390         Loop
                      400         End If ' COLUMNNAME LIKE "*_NET"
                      410         Next i ' COLUMN
                      420     End If 'RS.RECORDCOUNT>0
                      430     rs.Close
                      440     Set rs = Nothing
                      450     Set db = Nothing
                      
                      
                      Exit Sub
                      PutNet_err:
                      Debug.Print "PutNet", Erl, Err.Number, Err.Description
                      
                      End Sub
                      Private Sub TotalNets(sTableName As String)
                      On Error GoTo TotalNets_err
                      
                      '*** LOOP THROUGH COLUMNS ADDING ALL THE JOB_NETS AND PUTTING THEM IN THE TOTALNETS FIELDS
                      100      Dim db As DAO.Database, rs As DAO.Recordset, i As Integer, sFieldName As String, dblTotal As Double
                      110      Set db = CurrentDb
                      120      Set rs = db.OpenRecordset(sTableName)
                      130      If rs.RecordCount > 0 Then
                      140      rs.MoveLast
                      150         rs.MoveFirst
                      160         Do While Not rs.EOF
                      170         For i = 0 To rs.Fields.Count - 1 'COLUMNS
                      180         If rs.Fields(i).Name Like "*_Net" Then
                      190             sFieldName = Replace(rs.Fields(i).Name, "_Net", "")
                      200             dblTotal = dblTotal + rs.Fields(i).Value
                      210         End If 'COLUMN NAME LIKE "*_NET"
                      220         Next i 'COLUMN
                      230         rs.Edit
                      240         rs("TotalNets").Value = dblTotal
                      250         rs.Update
                      260         dblTotal = 0
                      270         rs.MoveNext
                      280         Loop
                      290     End If 'RS.RECORDCOUNT>0
                      300 rs.Close
                      310 Set rs = Nothing
                      320 Set db = Nothing
                      
                      
                      Exit Sub
                      TotalNets_err:
                      Debug.Print "TotalNets", Erl, Err.Number, Err.Description
                      
                      End Sub
                      Function fncColumn(sCustomer As String) As String
                      On Error GoTo fncColum_err
                      '*** REMOVE CHARACTERS THAT CAUSE ERRORS IN COLUMN NAMES
                      '*** ADD MORE IF NECESSARY
                      '*** USE AN ARRAY IF NECESSARY
                      100     fncColumn = Replace(Replace(sCustomer, " ", ""), "-", "")
                      110     fncColumn = Replace(fncColumn, "&", "")
                      120     fncColumn = Trim(Left(fncColumn, 8))
                      Exit Function
                      fncColum_err:
                      Debug.Print "fncColumn", Erl, Err.Number, Err.Description
                      End Function
                      
                      Private Sub cmdDropCustomerGrossColumns_Click()
                      Dim db As DAO.Database, td As DAO.TableDef, i As Integer
                      Set db = CurrentDb
                      Set td = db.TableDefs("profitbycustomer")
                      For i = 0 To td.Fields.Count - 1
                          If td.Fields(i).Name Like "*_Gross" Then
                              Call fncRunSQL("ALTER TABLE PROFITBYCUSTOMER DROP COLUMN " & td.Fields(i).Name)
                          End If
                      Next i
                      Set td = Nothing
                      Set db = Nothing
                      End Sub
                      
                      Private Sub cmdDropCustomerOHColumns_Click()
                      Dim db As DAO.Database, td As DAO.TableDef, i As Integer
                      Set db = CurrentDb
                      Set td = db.TableDefs("profitbycustomer")
                      For i = 0 To td.Fields.Count - 1
                          If td.Fields(i).Name Like "*_OH" Then
                              Call fncRunSQL("ALTER TABLE PROFITBYCUSTOMER DROP COLUMN " & td.Fields(i).Name)
                          End If
                      Next i
                      Set td = Nothing
                      Set db = Nothing
                      
                      End Sub
                      

Open in new window


The following need to be added in a public module. You might find them handy for other procedures as well.

1. fncQODBCQuery
2. fncRunSQL
3. fncProfitTable
4. fncAddField
5. fncDeleteRecord
 
Function fncQODBCQuery(sSQL As String, blnReturnsRecords As Boolean, Optional q As String = "qryTemp")
                      Dim db As Database, qd As QueryDef
                            Set db = CurrentDb
                            Set qd = db.CreateQueryDef(q)
                            qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
                            qd.SQL = sSQL
                      If blnReturnsRecords = True Then
                            qd.ReturnsRecords = True
                      Else
                            qd.ReturnsRecords = False
                            qd.Execute
                      End If
                      Set qd = Nothing
                      Set db = Nothing
                      End Function
                      
                      
                      
                      Function fncRunSQL(sSQL As String)
                      DoCmd.SetWarnings False
                      DoCmd.RunSQL sSQL
                      DoCmd.SetWarnings True
                      End Function
                      
                      
                      Function fncProfitTable(sTableName As String, BegDate As Date, EndDate As Date, Optional sCustomer As String = "")
                      Dim sDateString As String, sSQL As String, sEntityFilter
                      If Nz(sCustomer, "") <> "" Then sEntityFilter = ", entityfilterfullnamewithchildren ='" & sCustomer & "'"
                      sDateString = " datefrom=" & fncQBDate(BegDate) & " ,dateto=" & fncQBDate(EndDate)
                      sSQL = "sp_report ProfitAndLossStandard show text,label,amount_1 parameters " & sDateString & ", returnrows='All'" & sEntityFilter
                      Call fncQODBCQuery(sSQL, True)
                      Call fncRunSQL("select text  & label as Description ,fix((nz(amount_1,0)*100)/100) as Amount into " & sTableName & " from qryTemp")
                      End Function
                      
                      Function fncAddField(sTableName As String, sFieldName As String, dbFieldType As String, Optional blnAutoNumber As Boolean = False, Optional iFieldSize As Integer = 255)
                      Dim db As DAO.Database, td As DAO.TableDef
                      Set db = CurrentDb
                      Set td = db.TableDefs(sTableName)
                      td.Fields.Append td.CreateField(sFieldName, dbFieldType, iFieldSize)
                      td.Fields.Refresh
                      If blnAutoNumber = True Then td.Fields(sFieldName).Attributes = dbAutoIncrField
                      Set td = Nothing
                      Set db = Nothing
                      End Function
                      
                      Function fncDeleteRecord(sTableName As String, sFieldName As String, sFieldValue As String)
                      On Error GoTo fncDeleteRecord_err
                      100     Dim db As DAO.Database, td As DAO.TableDef
                      110     Set db = CurrentDb
                      120     Set td = db.TableDefs(sTableName)
                      130     If sFieldValue = "Null" Then
                      200     Call fncRunSQL("delete from [" & sTableName & "] where isnull([" & sFieldName & "])=true")
                      210     ElseIf td.Fields(sFieldName).Type = 20 Or td.Fields(sFieldName).Type = 7 Then
                      220     Call fncRunSQL("delete from [" & sTableName & "] where [" & sFieldName & "]=" & sFieldValue)
                      230     Else
                      300     Call fncRunSQL("delete from [" & sTableName & "] where [" & sFieldName & "]='" & sFieldValue & "'")
                      310     End If
                      Exit Function
                      fncDeleteRecord_err:
                      Call fncWriteError(Now, "", "Module Functions", "Function fncDeleteRecord", Err.Number, Err.Description, Erl)
                      End Function
                      

Open in new window


Once you have this form and the procedures in the database, you may want to add features like:

1. A list box for One-click DateMacros
2. An option to select customers and skip the Share of Overhead part
3. Work with different sublevels of customers
4. Add an option for Active, Not-Active or All customers

Important!:  When using the Share of Overhead feature, you must include ALL customers, Active and Not-Active and ALL from the same sublevel and never mix sublevels.

Now don't forget to add job costing (if you use it) to your payroll items and expenses going forward.
0
2,039 Views
Annaliese DellSec-Treas
QODBC and tech tips

Comments (1)

Annaliese DellSec-Treas

Author

Commented:
Thank you! I hope it helps somebody.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.