Calculated fields. Stuck in a query hole! RRS feed

  • General discussion

  • Hello,

    I have a database that works with client information, invoicing details and price codes.

    I had Table A (tblClientDetails) and Table B (tblInvoicingDetails) working in a query to then do calculated fields that calculate Total time spent with client (calculated with a DateDiff) and the price value (calculated with an IIF statement depending on which Service Type was selected), these prices have now changed from the start of the new financial year (01 Jul 17), so I need to have both of the prices recorded (along with their codes and names etc) and then get any service that was on or prior to 30/06/2017 to use the old value and any service on 01/07/2017 or after to use the new value.

    I have created Table C (tblServiceTypes) to load all of the information with the columns named [ServiceTypeID] [ServiceType] [FundValue] [ServiceTypeCode] [ValidUntil]

    How can I write an expression or VBA code to apply to this IIF statement so I only need to worry about changing details in the table and not change the expressions?

    Under the pump and so confused!


    Wednesday, July 26, 2017 3:42 AM

All replies

  • The following function will return the accounting year for any date:

    Public Function AcctYear(DateVal As Date, MonthStart As Integer, DayStart As Integer) As String

        Dim dtmYearStart As Date
        If MonthStart = 1 And DayStart = 1 Then
            ' accounting year is calendar year, so return single year value
            AcctYear = Year(DateVal)
            ' get start of accounting year in year of date value
            dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
            ' if date value is before start of accounting year
            ' accounting year starts year previous to date's year,
            ' otherwise it starts with date's year
            If DateVal < dtmYearStart Then
                AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00")
                AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00")
            End If
        End If

    End Function

    So, assuming the value in the ValidUntil column is the final date in the accounting year in question, you should be able to join tblInvoicingDetails to tblServiceTypes in your query:

    ON tblInvoicingDetails.ServiceTypeID = tblServiceTypes.ServiceTypeID
    AND AcctYear(tblInvoicingDetails.InvoiceDate,7,1) = AcctYear(tblServiceTypes.InvoiceDate,7,1)

    You should then be able to return the value of the FundValue (I assume) column from tblServiceTypes relevant to the accounting year of the invoice in question, and with this compute the price.

    Ken Sheridan, Stafford, England

    Wednesday, July 26, 2017 1:35 PM