fiscal year end calculations

Nested IIf statements in a query or SELECT Case in VBA.

Try this.  The table FyEnds has two fields, ClientID, and the FyEnd indicating the month in which the fy ends:

SELECT a.ClientID, b.FyEnd, Sum(fldAmt) From ClientData a
INNER JOIN FYEnds b ON a.ClientID = b.ClientID WHERE a.EntryDate BETWEEN
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date()),b.FyEnd+1,1),DateSerial(Year(Date())-1,b.FyEnd+1,1) AND
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date())+1,b.FyEnd+1,0),DateSerial(Year(Date()),b.FyEnd+1,0);

The first IIF() calulates the Start date of the FY and the second calculates the End date.  

I'm assuming a ClientData table has several clients, multi-year dates, and one or more amount fields which you want summed by client.  I just saw I neglected the GROUP BY, and don't need to see the FyEnd value:

SELECT a.ClientID, Sum(fldAmt) From ClientData a
INNER JOIN FYEnds b ON a.ClientID = b.ClientID WHERE a.EntryDate BETWEEN
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date()),b.FyEnd+1,1),DateSerial(Year(Date())-1,b.FyEnd+1,1) AND
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date())+1,b.FyEnd+1,0),DateSerial(Year(Date()),b.FyEnd+1,0)
GROUP BY a.ClientID;

Anybody there?

Here are some helper functions which you can adjust to your needs.

The basis function is DateFYStart. As is, it takes February as FYStart.

You could change that to take the month as a parameter:

<snip>
Public Function DateFYStart(ByVal intYear As Integer, ByVal intMonth As Integer) As Date
 
  Dim datFYstart As Date
  Dim intWeekday As Integer
  Dim intOffset  As Integer
 
  ' Nominel start date of fiscal year.
  datFYstart = DateSerial(intYear, intMonth, 1)
</snip>

To skip the full week adjustment, just remove that part of the function.

/gustav

Public Function DateFYStart(ByVal intYear As Integer) As Date
 
  Dim datFYstart As Date
  Dim intWeekday As Integer
  Dim intOffset  As Integer
 
  ' Nominel start date of fiscal year.
  datFYstart = DateSerial(intYear, 2, 1)
  intWeekday = WeekDay(datFYstart, vbMonday)
  If intWeekday <= 4 Then
    ' Use Monday before.
    intOffset = -intWeekday
  Else
    ' Use Monday next.
    intOffset = 7 - intWeekday
  End If
  ' Adjust start date to closest Monday.
  datFYstart = DateAdd("d", 1 + intOffset, datFYstart)
 
  DateFYStart = datFYstart
 
End Function
 
Public Function DateFYEnd(ByVal intYear As Integer) As Date
 
  Dim datFYend As Date
  
  ' Subtract one day from start date of next FY.
  datFYend = DateAdd("d", -1, DateFYStart(intYear + 1))
 
  DateFYEnd = datFYend
 
End Function
 
Public Function FiscalYear(ByVal datDate As Date) As Integer
 
  Dim intFYyear As Integer
  
  intFYyear = Year(datDate)
  If DateDiff("d", datDate, DateFYStart(intFYyear)) > 0 Then
    ' Fiscal year is before calendar year.
    intFYyear = intFYyear - 1
  End If
 
  FiscalYear = intFYyear
 
End Function
 
Public Function FiscalWeek(ByVal datDate As Date) As Long
 
  Dim lngFiscalWeek As Long
 
  lngFiscalWeek = DateDiff("ww", DateFYStart(FiscalYear(datDate)), datDate, vbMonday) + 1
 
  FiscalWeek = lngFiscalWeek
 
End Function

Select allOpen in new window

GRayL,
I'm trying to incorporate your code into my VBA function, but am getting an error on the WHERE statement.  It's a syntax error missing operator.

Public Function YTDAmortizationReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal YTDAmortizationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    ClientNumber = "ABC0"
    DateOfData = "3/31/2007"
    
    If YTDAmortizationCalc <> "" And IsNull(YTDAmortizationCalc) = False Then
        Set rs = db.OpenRecordset("SELECT " & YTDAmortizationCalc & " FROM tblNonCalcSpreads INNER JOIN tblEBITDA ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) WHERE tblEBITDA.ClientNumber = '" & ClientNumber & "' AND tblEBITDA.DateOfData = #" & DateOfData & "#")
 
        YTDAmortizationReturnCalc = rs.Fields(0)
            
 
        rs.Close
        Set rs = Nothing
        'db.Close
        'Set db = Nothing
    Else
        If ClientNumber = "ABC0" Then
        Set rs = db.OpenRecordset("SELECT a.ClientNumber, DSum(Amortization) From tblEBITDA a INNER JOIN tblAVSFSNIDCalc b ON a.ClientNumber = b.ClientNumber WHERE a.DateOfData BETWEEN Iif(Month(Date())>b.FYE,DateSerial(Year(Date()),b.FYE+1,1),DateSerial(Year(Date())-1,b.FYE+1,1) AND Iif(Month(Date())>b.FYE,DateSerial(Year(Date())+1,b.FYE+1,0),DateSerial(Year(Date()),b.FYE+1,0) GROUP BY a.ClientNumber")
        Else
        YTDAmortizationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
    strSQL = "Update tblEBITDA set YTDAmortization =" & YTDAmortizationReturnCalc & " WHERE tblEBITDA.ClientNumber = '" & ClientNumber & "' AND tblEBITDA.DateOfData = #" & DateOfData & "#"
    CurrentDb.Execute strSQL, dbFailOnError
 
    rs.Close
    Set rs = Nothing
    
End Function

Select allOpen in new window

I think it has something to do with my DateOfData field being month end dates.  Is there a way to reformat it in the query?

Are we clear FYEnd is just the number of the month, integers 1 to 12?  DateOfData can be any date between the FYStart and FYEnd dates, including month-end Dates.  I've looked at your use and I cannot find anything wrong.  Still looking.

Yes, FYE is just  the number of the month.  DateOfData wil be a month-end date.

Found it - add a parenthesis before GROUP BY to properly complete the Iff() function.

I added that, but I'm still getting the same error.

I see you are not using Option Explicit allowing you to use variables without first dimensioning them.  That's trouble looking for a place to happen.  You also create a vb variable with the same name as the field name - DateOfData - try changing the variable name to DataDate - just make it different than the field name.  Are you certain you are barking up the right WHERE clause ;-)

I added Option Explicit.
I've been playing with this trying to get it to work.  I tried cactus data's functions and can't get them quite right.  I've also played with GRayL's code.  
Let's see if I can clarify more of what I'm looking for to see if we are on the right track.  Say a client's FYE is March.  If I am entering a DateOfData of 5/31/2008, then I would want the sum of Amortization from April and May.  If a client's FYE is December and I'm entering DateOfData of 5/31/2008, then I would want the sum of Amortization from January to May.  Does that help clarify anything?

> Say a client's FYE is March

Then change 2 to 3 in this line:

  ' Nominel start date of fiscal year.
  datFYstart = DateSerial(intYear, 3, 1)

Now, function FiscalYear will return the Fiscal Year of any date:

intFY = FiscalYear(<any date>)

Then you can find the dates for FY start and for FY end by the functions DateFYStart and DateFYEnd.

datFYStart = DateFYStart(intFY)
datFYEnd = DateFYEnd(intFY)

These dates you can pass as parameters to a query where you filter on dates:

PARAMETERS FYStart DateTime, FYEnd DateTime;
<sql statement>
WHERE
  dateField Between FYStart And FYEnd;

/gustav

If FY start changes from client to client, add the month as a parameter.

/gustav

Public Function DateFYStart( _
  ByVal intYear As Integer, _
  ByVal intFYMonth As Integer) _
  As Date
 
  Dim datFYstart As Date
  Dim intWeekday As Integer
  Dim intOffset  As Integer
 
  ' Nominel start date of fiscal year.
  datFYstart = DateSerial(intYear, intFYMonth, 1)
 

Select allOpen in new window

Ok here's what I have.  What's wrong?

Public Function DateFYStart(ByVal intYear As Integer, ByVal intMonth As Integer) As Date
 
    Dim datFYstart As Date
    Dim intWeekday As Integer
    Dim intOffset As Integer
    
    'Nominel start date of fiscal year
    datFYstart = DateSerial(intYear, intMonth, 1)
    intWeekday = Weekday(datFYstart, vbMonday)
    If intWeekday <= 4 Then
        'Use Monday before
        intOffset = -intWeekday
    Else
        'Use Monday next
        intOffset = 7 - intWeekday
    End If
    'Adjust start date to closest Monday
    datFYstart = DateAdd("d", 1 + intOffset, datFYstart)
    
    DateFYStart = datFYstart
    
End Function
 
Public Function DateFYEnd(ByVal intYear As Integer, ByVal intMonth As Integer) As Date
    
    Dim datFYend As Date
    
    'Subtract one day from start date of next FY
    datFYend = DateAdd("d", -1, DateFYStart(intYear + 1, intMonth))
    
    DateFYEnd = datFYend
    
End Function
 
Public Function FiscalYear(ByVal datDate As Date) As Integer
 
    Dim intFYyear As Integer
    
    intFYyear = year(datDate)
    If DateDiff("d", datDate, DateFYStart(intFYyear, FYE)) > 0 Then
        'Fiscal year is before calendar year
        intFYyear = intFYyear - 1
    End If
    
    FiscalYear = intFYyear
    
End Function
 
Public Function YTDAmortizationReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal YTDAmortizationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    'Dim YTDAmort As Long
    ClientNumber = "ABC0"
    DateOfData = "3/31/2007"
    
    If YTDAmortizationCalc <> "" And IsNull(YTDAmortizationCalc) = False Then
        
        strSQL = "SELECT " & YTDAmortizationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE (((tblEBITDA.ClientNumber) = '" & ClientNumber & "') " & _
                 "AND ((tblEBITDA.DateOfData) = #" & DateOfData & "#));"
        Set rs = db.OpenRecordset(strSQL)
        YTDAmortizationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        'db.Close
    Else
        FiscalYear (DateOfData)
        If ClientNumber = "ABC0" Then
        'YTDAmort = DSum("[Amortization]", "tblEBITDA", "[ClientNumber] = '" & ClientNumber & "'")
                'AND [DateOfData] BETWEEN DateFYStart(Year(DateOfData), Clients.FYE) " & _
        '       '  "AND DateFYEnd(Year(DateOfData))
        strSQL = "SELECT Sum(Amortization) as Amort " & _
                 "FROM ((tblClients INNER JOIN tblNonCalcSpreads ON tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "INNER JOIN tblEBITDA ON tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber " & _
                 "AND tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "WHERE tblEBITDA.DateOfData " & _
                 "BETWEEN DateFYStart(Year(#" & DateOfData & "#), tblClients.FYE) " & _
                 "AND DateFYEnd(Year(#" & DateOfData & "#), tblClients.FYE)" & _
                 "GROUP BY tblEBITDA.ClientNumber));"
                 'iif(Month(#" & DateOfData & "#)>tblClients.FYE,DateSerial(Year(#" & DateOfData & "#),tblClients.FYE+1,1),DateSerial(Year(#" & DateOfData & "#)-1,tblClients.FYE+1,1) " & _
                 '"AND iif(Month(#" & DateOfData & "#)>tblClients.FYE,DateSerial(Year(#" & DateOfData & "#)+1,tblClients.FYE+1,0),DateSerial(Year(#" & DateOfData & "#),tblClients.FYE+1,0)) " &
                 
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
        
        YTDAmortizationReturnCalc = rs!Amort
        Else
        YTDAmortizationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDAmortization =" & YTDAmortizationReturnCalc & " " & _
                 "WHERE (((tblEBITDA.ClientNumber) = '" & ClientNumber & "'_ " & _
                 "AND ((tblEBITDA.DateOfData) = #" & DateOfData & "#));"
        CurrentDb.Execute strSQL, dbFailOnError
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Select allOpen in new window

You may need to skip the week adjustment part ...?

/gustav

Public Function DateFYStart(ByVal intYear As Integer, ByVal intMonth As Integer) As Date
 
    Dim datFYstart As Date
    
    'Nominel start date of fiscal year
    datFYstart = DateSerial(intYear, intMonth, 1)
    
    DateFYStart = datFYstart
    
End Function

Select allOpen in new window

Ok, in my FiscalYear Function I'm using my field FYE, but I'm getting an error that it's not defined.  Am I using this in the right place?

You will, of course, have to adopt names of variables as to your liking:

FYEnd <-> FYE

or vice versa.

/gustav

Anything further on my input?

Ok, I've mixed the two suggestions.  Here's my code, but I'm getting a syntax error on my WHERE clause.  Let me know if I'm way off base here.

Public Function YTDAmortizationReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal YTDAmortizationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    'Dim FYEnd As Date
    'Dim YTDAmort As Long
    ClientNumber = "ABC0"
    DateOfData = "3/31/2007"
    
    If YTDAmortizationCalc <> "" And IsNull(YTDAmortizationCalc) = False Then
        
        strSQL = "SELECT " & YTDAmortizationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE (((tblEBITDA.ClientNumber) = '" & ClientNumber & "') " & _
                 "AND ((tblEBITDA.DateOfData) = #" & DateOfData & "#));"
        Set rs = db.OpenRecordset(strSQL)
        YTDAmortizationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        'db.Close
    Else
        'FYEnd = DateSerial(FiscalYear(DateOfData), [tblClients.FYE], 1)
        'Debug.Print FYEnd
        If ClientNumber = "ABC0" Then
        'YTDAmort = DSum("[Amortization]", "tblEBITDA", "[ClientNumber] = '" & ClientNumber & "'")
                'AND [DateOfData] BETWEEN DateFYStart(Year(DateOfData), Clients.FYE) " & _
        '       '  "AND DateFYEnd(Year(DateOfData))
        strSQL = "SELECT Sum(Amortization) as Amort, DateSerial(FiscalYear(DateOfData),tblClients.FYE,1) as FYEnd " & _
                 "FROM ((tblClients INNER JOIN tblNonCalcSpreads ON tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "INNER JOIN tblEBITDA ON tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber " & _
                 "AND tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "WHERE tblEBITDA.DateOfData " & _
                 "BETWEEN iif(DateOfData>FYEnd,DateAdd('m', +1, (DateAdd('y', -1, FYEnd)), DateAdd('m', +1, FYEnd))" & _
                 "AND (DateOfData)" & _
                 "GROUP BY tblEBITDA.ClientNumber));"
                 'DateFYStart(Year(#" & DateOfData & "#), tblClients.FYE) " & _
                 '"AND DateFYEnd(Year(#" & DateOfData & "#), tblClients.FYE)" & _
 
                 
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
        
        YTDAmortizationReturnCalc = rs!Amort
        Else
        YTDAmortizationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDAmortization =" & YTDAmortizationReturnCalc & " " & _
                 "WHERE (((tblEBITDA.ClientNumber) = '" & ClientNumber & "'_ " & _
                 "AND ((tblEBITDA.DateOfData) = #" & DateOfData & "#));"
        CurrentDb.Execute strSQL, dbFailOnError
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Select allOpen in new window

You mix datetime and string.

    Dim strDate As String
<snip>
    DateOfData = #3/31/2007#
 
    strDate = Format(DateOfData, "d\/m\/yyyy hh:nn AM/PM"
    
    If YTDAmortizationCalc <> "" And IsNull(YTDAmortizationCalc) = False Then
        
        strSQL = "SELECT " & YTDAmortizationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE (((tblEBITDA.ClientNumber) = '" & ClientNumber & "') " & _
                 "AND ((tblEBITDA.DateOfData) = #" & strDate & "#));"
<snip>  

Select allOpen in new window

Here's the select statement where I changed the BETWEEN clause.  I'm getting a syntax error on the WHERE clause.

strSQL = "SELECT Sum(Amortization) as Amort, DateSerial(FiscalYear(DateOfData),tblClients.FYE,1) as FYEnd " & _
                 "FROM ((tblClients INNER JOIN tblNonCalcSpreads ON tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "INNER JOIN tblEBITDA ON tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber " & _
                 "AND tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "WHERE tblEBITDA.DateOfData " & _
                 "BETWEEN iif(DateOfData>FYEnd,DateAdd('m', +1, (DateAdd('y', -1, FYEnd)), DateAdd('m', +1, FYEnd))" & _
                 "AND (DateOfData)" & _
                 "GROUP BY tblEBITDA.ClientNumber));"

Select allOpen in new window

Well, you've taken what should have been a relatively straight-forward two table query and decided to do it in code - which is where I decided to 'observe'.  Here we are, 5 days into the saga, and...

BTW Do you really mean DateSerial(FiscalYear(DateOfData)
--------------------------------------------------^--shouldn't this be just Year?

Ray is right. You should think this over.

> I have a field in a table that states the month each client uses as their fiscal year end month.  
> How would I reference this for calculations such as summing NetChargeoffs for the
> current fiscal year?

The easiest method is to shift the transactional dates to be relative not to the calendar year start but to the fiscal year start. Regarding the fiscal year start as a pseudo 1th. of Jan. makes all other date handling elementary.
Alternatively, establish fiscal year start and fiscal year end and compare transaction dates to these.

/gustav

Incorrect table and field names notwithstanding, This will get the sums for the current fiscal year to end of last month - modified from from my second post:

SELECT a.ClientID, Sum(fldAmt) From ClientData a
INNER JOIN FYEnds b ON a.ClientID = b.ClientID WHERE a.EntryDate BETWEEN
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date()),b.FyEnd+1,1),DateSerial(Year(Date())-1,b.FyEnd+1,1) AND
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date())+1,b.FyEnd+1,0),DateSerial(Year(Date()),b.FyEnd+1,0)
And a.EntryDate<=DateAdd("d",-Day(Date()),Date())
GROUP BY a.ClientID;

Ray,
I'm getting a syntax error (missing operator) error on the WHERE clause.

Can you post the SQL that you are using please.

Public Function YTDAmortizationReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal YTDAmortizationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    ClientNumber = "ABC0"
    DateOfData = "3/31/2007"
    
    If YTDAmortizationCalc <> "" And IsNull(YTDAmortizationCalc) = False Then
        
        strSQL = "SELECT " & YTDAmortizationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE (((tblEBITDA.ClientNumber) = '" & ClientNumber & "') " & _
                 "AND ((tblEBITDA.DateOfData) = #" & DateOfData & "#));"
        Set rs = db.OpenRecordset(strSQL)
        YTDAmortizationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        'db.Close
    Else
        If ClientNumber = "ABC0" Then
        strSQL = "SELECT tblEBITDA.ClientNumber, Sum(tblEBITDA.Amortization) " & _
                 "FROM tblEBITDA INNER JOIN tblClients " & _
                 "ON tblEBITDA.ClientNumber = tblClients.ClientNumber " & _
                 "WHERE tblEBITDA.DateOfData BETWEEN " & _
                 "Iif(Month(Date())>tblClients.FYE,DateSerial(Year(Date()),tblClients.FYE+1,1),DateSerial(Year(Date())-1,tblClients.FYE+1,1) " & _
                 "AND Iif(Month(Date())>tblClients.FYE,DateSerial(Year(Date())+1,tblClients.FYE+1,0),DateSerial(Year(Date()),tblClients.FYE+1,0) " & _
                 "AND tblEBITDA.DateOfData<=DateAdd('d',-Day(Date()),Date())" & _
                 "GROUP BY tblEBITDA.ClientNumber;"
 
                 Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
        
        YTDAmortizationReturnCalc = rs!Amort
        Else
        YTDAmortizationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDAmortization =" & YTDAmortizationReturnCalc & " " & _
                 "WHERE (((tblEBITDA.ClientNumber) = '" & ClientNumber & "'_ " & _
                 "AND ((tblEBITDA.DateOfData) = #" & DateOfData & "#));"
        CurrentDb.Execute strSQL, dbFailOnError
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Select allOpen in new window

Which WHERE clause, there are three of them?  Can you get the query to work from the query designer?   You are trying to execute two different queries through an If statement and then follow up with an Update query - all at once!   I think you should get each query running from the query designer first.  Then save the queries, and call them from code.  If you have to build the queries in code, then build from the executable saved queries.

I think your first If statement could be replaced by:

If Nz(YTDAmortizationCalc,"")<>"" Then

Ok, I built the query in design view and copied the SQL over.  The updated code is below.  I'm receiving an error "You tried to execute a query that does not include the specified expression 'DateOfData' as part of an aggregate function."  I'm not sure what I need to fix.

SELECT tblEBITDA.ClientNumber, Sum(tblNonCalcSpreads.Amortization) AS Amort, tblEBITDA.DateOfData
FROM (tblClients INNER JOIN tblEBITDA ON tblClients.ClientNumber = tblEBITDA.ClientNumber) INNER JOIN tblNonCalcSpreads ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber)
GROUP BY tblEBITDA.ClientNumber
HAVING (((tblEBITDA.DateOfData) Between IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE],0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0)) And IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE]+1,0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0))) AND ((tblEBITDA.DateOfData)<=DateAdd('d',-Day(Date()),Date())));

Select allOpen in new window

Remove tblEBITDA.DateOfData from the SELECT clause.  You cannot sum on the FY and still have every individual date in the recordset.  You have to either remove it from the SELECT or add it to the GROUP BY

OK, I removed that from the SELECT clause, but am still getting the same error on the entire HAVING clause.

This is how I think it should look.  Structure it like my post at http:#a21887221

SELECT tblEBITDA.ClientNumber, Sum(tblNonCalcSpreads.Amortization) AS Amort
FROM (tblClients 
INNER JOIN tblEBITDA ON tblClients.ClientNumber = tblEBITDA.ClientNumber) 
INNER JOIN tblNonCalcSpreads ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) 
AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) 
AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber)
WHERE tblEBITDA.DateOfData Between IIf(Month(Date())>[tblClients].[FYE],
DateSerial(Year(Date()),[tblClients].[FYE]+1,1),DateSerial(Year(Date()),[tblClients].[FYE]+1,0)) AND 
IIf(Month(Date())>[tblClients].[FYE],
DateSerial(Year(Date())+1,[tblClients].[FYE]+1,0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0)))
GROUP BY tblEBITDA.ClientNumber

Select allOpen in new window

Ok, I adjusted the code and when it runs I get an error - No current record.
The FYE = 12 and the DateOfData = 3/31/2007, so the YTDDepreciation should be the Depreciation for January + Febraury + March, but it's not giving me any record.  I'm not sure why as there is data in the table.

Public Function YTDDepreciationReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal YTDDepreciationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    ClientNumber = "ABC0"
    DateOfData = "3/31/2007"
    
    If YTDDepreciationCalc <> "" And IsNull(YTDDepreciationCalc) = False Then
    
        strSQL = "SELECT " & YTDDepreciationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE tblEBITDA.ClientNumber = '" & ClientNumber & "' " & _
                 "AND tblEBITDA.DateOfData = #" & DateOfData & "#"
        Set rs = db.OpenRecordset(strSQL)
        YTDDepreciationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        
    Else
        If ClientNumber = "ABC0" Then
        strSQL = "SELECT tblEBITDA.ClientNumber, Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM (tblClients INNER JOIN tblEBITDA " & _
                 "ON tblClients.ClientNumber = tblEBITDA.ClientNumber) INNER JOIN tblNonCalcSpreads " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "WHERE (((tblEBITDA.DateOfData) BETWEEN " & _
                 "IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE],0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0)) " & _
                 "AND IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE]+1,0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0))))" & _
                 "GROUP BY tblEBITDA.ClientNumber;"
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount = 0 Then
                Debug.Print rs.RecordCount
                YTDDepreciationReturnCalc = Null
                Exit Function
            End If
 
        YTDDepreciationReturnCalc = rs!Depr
        Else
        YTDDepreciationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDDepreciation =" & YTDDepreciationReturnCalc & " " & _
                 "WHERE tblEBITDA.ClientNumber = '" & ClientNumber & "' " & _
                 "AND tblEBITDA.DateOfData = #" & DateOfData & "#"
        CurrentDb.Execute strSQL, dbFailOnError
 
        rs.Close
        Set rs = Nothing
        'db.Close
        Set db = Nothing
 
End Function

Select allOpen in new window

I see you have a variable named DateOfData (Line 7) as well a field by the same name.  Try naming the variable strDate.

I changed my variables, but am still getting no records.  Did I miss one?

Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal strDate As Date, ByVal YTDDepreciationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    CliNum = "ABC0"
    strDate = "3/31/2007"
    
    If YTDDepreciationCalc <> "" And IsNull(YTDDepreciationCalc) = False Then
    
        strSQL = "SELECT " & YTDDepreciationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        Set rs = db.OpenRecordset(strSQL)
        YTDDepreciationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        
    Else
        If CliNum = "ABC0" Then
        strSQL = "SELECT tblEBITDA.ClientNumber, Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM (tblClients INNER JOIN tblEBITDA " & _
                 "ON tblClients.ClientNumber = tblEBITDA.ClientNumber) INNER JOIN tblNonCalcSpreads " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "WHERE (((tblEBITDA.DateOfData) BETWEEN " & _
                 "IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE],0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0)) " & _
                 "AND IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE]+1,0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0))))" & _
                 "GROUP BY tblEBITDA.ClientNumber;"
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount = 0 Then
                Debug.Print rs.RecordCount
                YTDDepreciationReturnCalc = Null
                Exit Function
            End If
 
        YTDDepreciationReturnCalc = rs!Depr
        Else
        YTDDepreciationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDDepreciation =" & YTDDepreciationReturnCalc & " " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        CurrentDb.Execute strSQL, dbFailOnError
 
        rs.Close
        Set rs = Nothing
        'db.Close
        Set db = Nothing
 
End Function

Select allOpen in new window

Why do you keep changing the BETWEEN clause around?  If FYE was 6 and Date() is today, then my query will give
Between 2008-07-01 and  2009-06-30

What you wrote will produce 2009-05-31 and 2009 -06-30

Sorry about that.  I must have copied it wrong.  I've changed it and reran it and am still getting a zero recordcount.

Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal strDate As Date, ByVal YTDDepreciationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    CliNum = "ABC0"
    strDate = "12/31/2007"
    
    If YTDDepreciationCalc <> "" And IsNull(YTDDepreciationCalc) = False Then
    
        strSQL = "SELECT " & YTDDepreciationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        Set rs = db.OpenRecordset(strSQL)
        YTDDepreciationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        
    Else
        If CliNum = "ABC0" Then
        strSQL = "SELECT tblEBITDA.ClientNumber, Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM (tblClients INNER JOIN tblEBITDA " & _
                 "ON tblClients.ClientNumber = tblEBITDA.ClientNumber) INNER JOIN tblNonCalcSpreads " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "WHERE (((tblEBITDA.DateOfData) BETWEEN " & _
                 "IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date()),[tblClients].[FYE]+1,1),DateSerial(Year(Date()),[tblClients].[FYE]+1,1)) " & _
                 "AND IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE]+1,0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0))))" & _
                 "GROUP BY tblEBITDA.ClientNumber;"
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount = 0 Then
                Debug.Print rs.RecordCount
                YTDDepreciationReturnCalc = Null
                Exit Function
            End If
 
        YTDDepreciationReturnCalc = rs!Depr
        Else
        YTDDepreciationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDDepreciation =" & YTDDepreciationReturnCalc & " " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        CurrentDb.Execute strSQL, dbFailOnError
 
        rs.Close
        Set rs = Nothing
        'db.Close
        Set db = Nothing
 
End Function

Select allOpen in new window

When you run the strSQL in the query Designer, what happens.  Can you go back to the design view from the SQL view and do the joins appear correct?

I think we are at the point where you have to strip the mdb down to the min required to support the question and upload it using Attach File below.  Be sure do a Compact and Repair before uploading.

Yes, the joins are correct.

Here is the db.
PCDB-test2ee.zip

A zillion queries and forms, just one table.  Don't we need the table with ClientID and FYE.  Where should I be looking?

From where do you call the function  YTDDepreciationReturnCalc

The FYE is in tblClients while the Depreciation field is in table tblEBITDA, which are related through tblNonCalcSpreads.  The funtion is in the EBITDACalcFields Module.

I have been using the module CalcTest to call and test my functions.

Sorry, the slider was all the way to the right and I only 'saw' one table.  Please ignore my post at http:#a21956620

I now need the format of the call to  YTDDepreciationReturnCalc i.e.

?  YTDDepreciationReturnCalc()

Call YTDDepreciationReturnCalc(ClientNumber, DateOfData, YTDDepreciationCalc)

I know that part.  I need to know the actual values you used for the three variables

The ClientNumber = "ABC0", you can use any month-end date for 2007, and the value for YTDDepreciationCalc is in tblEBITDACalc. ([tblEBITDACalc].[YTDDepreciationCalc])

Change line 1 to:

Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal strDate As String, ByVal YTDDepreciationCalc As String) As Variant

Select allOpen in new window

Comment out line 7 - prefix a single quote.

Now when I call the procedure:

? ytddepreciationreturncalc("ABC0","03/31/2007","YTDDepreciation")
 14
Is that what you expect?

There is no field by that name, I used "TDDepreciation" and it returned 14

That's the result I want, but that's not what I'm getting.

Comment out line 7 - prefix a single quote.
The is the line where I'm setting strDate, correct?

I tried using YTDDepreciation as well and I get #Error as a result

I changed line 1 from  ByVal strDate = Date to ByVal strDate = String
Line 7 is prefixed with a single quote - commented out - thus ignored by the compiler.  I also commented out line 55

using the actual field name

? ytddepreciationreturncalc("ABC0","03/31/2007","YTDDepreciation")
 14

The function as I used it is below

Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal strDate As String,  ByVal YTDDepreciationCalc As String) As Variant
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    CliNum = "ABC0"
    'strDate = "12/31/2007"
    
    If YTDDepreciationCalc <> "" And IsNull(YTDDepreciationCalc) = False Then
    
        strSQL = "SELECT " & YTDDepreciationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        Set rs = db.OpenRecordset(strSQL)
        YTDDepreciationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        
    Else
        If CliNum = "ABC0" Then
        strSQL = "SELECT tblEBITDA.ClientNumber, Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM (tblClients INNER JOIN tblEBITDA " & _
                 "ON tblClients.ClientNumber = tblEBITDA.ClientNumber) INNER JOIN tblNonCalcSpreads " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "WHERE (((tblEBITDA.DateOfData) BETWEEN " & _
                 "IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date()),[tblClients].[FYE]+1,1),DateSerial(Year(Date()),[tblClients].[FYE]+1,1)) " & _
                 "AND IIf(Month(Date())>[tblClients].[FYE],DateSerial(Year(Date())+1,[tblClients].[FYE]+1,0),DateSerial(Year(Date()),[tblClients].[FYE]+1,0))))" & _
                 "GROUP BY tblEBITDA.ClientNumber;"
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount = 0 Then
                Debug.Print rs.RecordCount
                YTDDepreciationReturnCalc = Null
                Exit Function
            End If
 
        YTDDepreciationReturnCalc = rs!Depr
        Else
        YTDDepreciationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDDepreciation =" & YTDDepreciationReturnCalc & " " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        CurrentDb.Execute strSQL, dbFailOnError
 
        'rs.Close
        Set rs = Nothing
        'db.Close
        Set db = Nothing
 
End Function

Select allOpen in new window

That's what I was missing.  Commenting out line 55.  Now it works.  Except for 3/31/2007, I'm getting 52 as the result instead of 14.  All the other dates are working.

Then I would bet the value for 03/31/2007 has changed or you typed 12/31/2007.  I just re-checked and all is well on this end.

I'm still getting 52 for 3/31/2007.  My query results are below.

ClientNumber	DateOfData	YTDDepreciation
ABC0	1/31/2007	          5
ABC0	2/28/2007	          9
ABC0	3/31/2007	         52
ABC0	4/30/2007	         19
ABC0	5/31/2007	         23
ABC0	6/30/2007	         28
ABC0	7/31/2007	         33
ABC0	8/31/2007	         37
ABC0	9/30/2007	         42
ABC0	10/31/2007	45
ABC0	11/30/2007	49
ABC0	12/31/2007	52

Select allOpen in new window

Yes, but what does that field contain in the table tblEBIDTA.  This is what the field looks like in my table:

tblEBITDAtblEBITDAYTDDepreciation5914192328333742454952
Note the third record is 14.  I'm guessing 52 in your copy?

Isn't that a suprise!  I used the Code Snippet
 
tblEBITDA

YTDDepreciation
5
9
14
19
23
28
33
37
42
45
49
52

Select allOpen in new window

I have the same data as you in the tblEBITDA.  I'm not sure why I'm getting 52 for 3/31/2007 and you're not.

Make sure any/all open table view(s) are  closed before running the query.

You can have a table view open, make changes to the field, and run a query on the table and the query will not reflect the changes you made until you move the cursor to a different record.

I closed the db and reopened it.  I only had the query open and ran it and I got the same results as before.

When you say query, I presume you mean a saved query.  What is the query name?  If it was not in your upload, paste the sql in the code snippet below.

I was using the qryCalcTest to test the calculation.  the SQL is below.

SELECT tblNonCalcSpreads.ClientNumber, tblNonCalcSpreads.DateOfData, YTDDepreciationReturnCalc([tblNonCalcSpreads].[ClientNumber],[tblNonCalcSpreads].[DateOfData],[tblEBITDA].[YTDDepreciation]) AS YTDDepreciation
FROM (tblClients INNER JOIN tblNonCalcSpreads ON tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) INNER JOIN (tblEBITDACalc INNER JOIN tblEBITDA ON tblEBITDACalc.ClientNumber = tblEBITDA.ClientNumber) ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber)
WHERE (((tblNonCalcSpreads.ClientNumber)="ABC0"));

Select allOpen in new window

When I run that query, I get a message "YTDAmortizationReturnCalc" function not found.  I scanned the whole Project for that name and came up empty.  Just to be sure we are on the same page, delete the first upload and give us a new zip file?

Here you go.
PCDB-test2ee.zip

And here you go.  You just won the "Dork of the Week Award" ;-)  Here is what is in the YTDDepreciation field in table tblEBIDTA

YTDDepreciation
5
9
52  <- notice this. As I said in my post at http:#a21963861
19
23
28
33
37
42
45
49
52

Select allOpen in new window

http:#a21963861

Select allOpen in new window

YTDDepreciation should be recaluclated each time you run the query incase there has been any updates to the Depreciation field.  So I still don't understand why it would show 52 for 3/31/2007.

So what happens if you change the table value to 14?  I did just that, re-ran qryTestCalc and it returned 14 for end-March.  And I might add, I do think I answered the question - way back when.

But it's not working.  The function should sum the Depreciation for the current fiscal year and then update that total back to the table, but it's not.  If it was 3/31/2007 YTDDepreciation would be 14.  

In query qryCalcTest, you call the function YTDDepreciationReturnCalc([tblNonCalcSpreads].[ClientNumber],[tblNonCalcSpreads].[DateOfData],[tblEBITDA].[YTDDepreciation]) AS YTDDepreciation
The third variable is defined in the table as long integer.

Getting to the function, that arguement is received as ByVal YTDDepreciationCalc As String

You then go on to build a query: "SELECT " & YTDDepreciationCalc & etc.  I cannot see what tblNonCalcSpreads contributes to the query.

Insert: Debug.Print strSQL :at line 1892 of your EDITDACalcFields module.  Run qryCalcTest.
You now have a recordset of 27 rows of whatever was the value of tblEBITDA.YTDDepreciation for that month.
Is that what you expected.  It doesn't make sense to me.

The call for the function should be YTDDepreciationReturnCalc([tblNonCalcSpreads].[ClientNumber],[tblNonCalcSpreads].[DateOfData],[tblEBITDACalc].[YTDDepreciationCalc] as YTDDepreciation.

tblNonCalcSpreads is where the values for Depreciation are, so that is referenced to pull that data.

Is that what I expected?  Yes and No.  I want the YTDDepreciation displayed, but not pulled directly from tblEBITDA.  I want it to be calculated as some of the values may have changed.

I've adjusted the function using your code to reference the DateOfData value as that is what should be compared to the FYE.  There's something not quite right as I'm getting 3 as a result no matter what DateOfData I use.

Does this make any sense?

Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal strDate As Date, ByVal YTDDepreciationCalc As String) As Variant
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    CliNum = "ABC0"
    strDate = "5/31/2007"
    
    If YTDDepreciationCalc <> "" And IsNull(YTDDepreciationCalc) = False Then
    
        strSQL = "SELECT " & YTDDepreciationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        Set rs = db.OpenRecordset(strSQL)
        YTDDepreciationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        
    Else
        If CliNum = "ABC0" Then
        strSQL = "SELECT Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM (tblClients INNER JOIN tblEBITDA " & _
                 "ON tblClients.ClientNumber = tblEBITDA.ClientNumber) INNER JOIN tblNonCalcSpreads " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "WHERE (((tblEBITDA.DateOfData) BETWEEN " & _
                 "IIf(Month(#" & strDate & "#)>[tblClients].[FYE],DateSerial(Year(#" & strDate & "#),[tblClients].[FYE]+1,1),DateSerial(Year(#" & strDate & "#),[tblClients].[FYE]+1,1)) " & _
                 "AND IIf(Month(#" & strDate & "#)>[tblClients].[FYE],DateSerial(Year(#" & strDate & "#)+1,[tblClients].[FYE]+1,0),DateSerial(Year(#" & strDate & "#),[tblClients].[FYE]+1,0))));"
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount = 0 Then
                Debug.Print rs.RecordCount
                YTDDepreciationReturnCalc = Null
                Exit Function
            End If
 
        YTDDepreciationReturnCalc = rs!Depr
        Debug.Print rs!Depr
        Else
        YTDDepreciationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDDepreciation =" & YTDDepreciationReturnCalc & " " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        CurrentDb.Execute strSQL, dbFailOnError
 
        'rs.Close
        Set rs = Nothing
        'db.Close
        Set db = Nothing
 
End Function

Select allOpen in new window

Getting back to the original question, my answer provided the start and end FY dates for any date given by Date().  I now see that should have been - given by strDate.  You can convert strDate to an an actual date by CDate(strDate) instead of "#" & strDate & "#".  If the aim of the exercise is to recalculate YTDDepreciation in case there have been any changes, the If statement is preventing that. It never gets to the Else condition so long as there is a value in tblEDITDA for that client on that month-end.

I think the psuedo code should be

OldValue = DLookup(YTDDepreciation in tlbEBITDA)

If Sum(tblNonCalcSpreads.Depreciation)<> OldValue Then  Update YTDDepreciation in tlbEBITDA

Comments?

Re:  https://www.experts-exchange.com/questions/23544826/Type-Back-Rate-of-the-New-Post-box-with-all-the-doo-dads-Much-Too-Slow.html?cid=239&anchorAnswerId=21973758#a21973758

I have had to submit a complaint about the 'improved' textbox EE installed.  It has become so horribly slow I now have to do all my input in NotePad and paste into the box

I have changed the type back to Date for strDate.  I don't think tblEBITDA has anything to do with updating the YTDDepreciation except for that being the location of that field.  Line 9 is actually checking for a value in YTDDepreciationCalc which is in tblEBITDACalc.

With my changes I made, I'm getting a result of 3 for any DateOfData I use and it is being updated back to tblEBITDA.  I'm just not sure why a value of 3 is returned.

SELECT tblNonCalcSpreads.ClientNumber, tblNonCalcSpreads.DateOfData, YTDDepreciationReturnCalc([tblNonCalcSpreads].[ClientNumber],[tblNonCalcSpreads].[DateOfData],[tblEBITDA].[YTDDepreciation]) AS YTDDepreciation

This is the SELECT clause of the query which calls the function. Note the third varilable of the call is [tblEBITDA].[YTDDepreciation] which is not in tblEBITDACalc.

The essence of this question was how do I sum dated values with a client id and select only those values than are in the clients fiscal year, the end month of which is any month number 1 to 12.  I answered that ages ago.  We have spent the last two weeks troubleshooting your software in areas not related to this question.  I think you should close this question and ask another specific to the function you are having trouble with.

See ID: 21973040

The call for the function should be YTDDepreciationReturnCalc([tblNonCalcSpreads].[ClientNumber],[tblNonCalcSpreads].[DateOfData],[tblEBITDACalc].[YTDDepreciationCalc] as YTDDepreciation.

The field tblEBITDACalc].[YTDDepreciationCalc] is empty.  

Right, that is what the function is checking.  I'm using the same format for all of my functions as you can see some are populated and some aren't.  So since this one is empty it will perform the Else statement, but it's not working right.

Assuming strDate is now  DateTime and correcting the error in the FY calc, here's what the Function should look like.  Note I commented out the strDate line.

Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal strDate As Date, ByVal YTDDepreciationCalc As String) As Variant
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    CliNum = "ABC0"
    'strDate = "5/31/2007" <--Commented out
    
    'If YTDDepreciationCalc <> "" And IsNull(YTDDepreciationCalc) = False Then  <---Changed as in next line
    Nz(YTDDepreciationCalc,"")<> ""
    
        strSQL = "SELECT " & YTDDepreciationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        Set rs = db.OpenRecordset(strSQL)
        YTDDepreciationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        
    Else
        If CliNum = "ABC0" Then
        strSQL = "SELECT Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM (tblClients INNER JOIN tblEBITDA " & _
                 "ON tblClients.ClientNumber = tblEBITDA.ClientNumber) INNER JOIN tblNonCalcSpreads " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "AND (tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) " & _
                 "WHERE tblEBITDA.DateOfData BETWEEN " & _
                 "IIf(Month(strDate)>[tblClients].[FYE],DateSerial(Year(strDate),[tblClients].[FYE]+1,1), " & _
                 "DateSerial(Year(strDate)-1,[tblClients].[FYE]+1,1)) AND " & _
                 "IIf(Month(strDate)>[tblClients].[FYE],DateSerial(Year(strDate)+1,[tblClients].[FYE]+1,0), " & _
                 "DateSerial(Year(strDate),[tblClients].[FYE]+1,0));"
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount = 0 Then
                Debug.Print rs.RecordCount
                YTDDepreciationReturnCalc = Null
                Exit Function
            End If
 
        YTDDepreciationReturnCalc = rs!Depr
        Debug.Print rs!Depr
        Else
        YTDDepreciationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDDepreciation =" & YTDDepreciationReturnCalc & " " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & strDate & "#"
        CurrentDb.Execute strSQL, dbFailOnError
 
        'rs.Close
        Set rs = Nothing
        'db.Close
        Set db = Nothing
 
End Function

Select allOpen in new window

I ran the qryTestCalc and get #Error for all the results.  
I'm getting an error on the strSQL line starting at line 26.  Too few parameters. Expected 1.

After another good hard look at this thing, I can see where strSQL in the Else part is totally wrong.  The function is passed the ClientNumber as CliNum and the date as strDate so  you can use tblNonCalcSpreads directly without any joins.  
 
 
        strSQL = "SELECT tblNonCalcSpreads.ClientNumber, Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM tblNonCalcSpreads WHERE tblNonCalcSpreads.DateOfData = #" & strDate & _
                 "# AND tblNonCalcSpreads.ClientNumber = '" & CliNum & _
                 "' GROUP BY tblNonCalcSpreads.ClientNumber;"

Select allOpen in new window

Ok, but how does that capture the FYE in tblClients?

I'm getting a Too few parameters. Expected 1. error.

I'm trying to enlist additional help.

This is a rather extensive post, and I didn't read all of it, so bear with me.

I've worked with both PeopleSoft and Great Plains. And for a while our Fiscal calendar was 6 months off from the actual calendar. The way both applications handle it is that they have the actual date of the entry in the table and then they had individual columns for FY Fiscal_Month and Fiscal_Day as 3 separate columns that were populated when the entry was made in the table(s).

There was a master calendar table that had the beginning and end for a given fiscal year.

While I know it would be a complete redesign, it might be the better solution.

I changed the SQL as Andrew suggested and I'm getting 52 as a result no matter what date I enter.  Also, when I call the function from a query, I get #Error as a result for all dates.

Hi can you confirm exactly how you are entering the date. You may wish to try the line below immediately before the strSQL = line. You can change it to the date you require but mus be in MM/DD/YYYY format with the #'s around in the text.

Cheers, Andrew

strDate = "#07/14/2008#"

Select allOpen in new window

I have the following line in my function:

strDate = "1/31/2007"

When I added the #'s I get a type mismatch error.

If you wrap it in pound signs, remove the double quotes.  

strDate = #07/14/2008#

I still get 52 for a result no matter what date I put in and when I run a query and call the function I still get #Error as a result.

strDate = "#1/31/2007#"

You need the double quotes for this as strDate is a string variable.
You need to #'s because in the SQL string your criteria needs to read
     WHERE FieldName = #MM/DD/YYYY#

Cheers, Andrew

strDate is a date variable.  see post # 21975272.

OK then you will need to do the #'s in the strSQL as below.
You should also either rename the variable to dtmDate or change it to a String.
Finally if your defaulkt date format is MM/DD/YY or MM/DD/YYYY you should be OK but if it isnt then you may need to FORMAT strDate, each reference to it will need to be

Format(strDate,"MM/DD/YYYY")

Cheers, Andrew

"WHERE tblNonCalcSpreads.DateOfData BETWEEN " & _
"IIf(Month(#" & strDate & "#)>[tblClients].[FYE],DateSerial(Year(#" & strDate & "#),[tblClients].[FYE]+1,1), " & _
"DateSerial(Year(#" & strDate & "#)-1,[tblClients].[FYE]+1,1)) AND #" & _
strData & "#));"

Select allOpen in new window

Ok I changed strDate to dtDate.  

When I step throught the code in VBA I get 52 for a result no matter what date I use.  When I call the function in a query I get #Error as a result for all dates.  This code should work, it doesn't make sense to me why I'm getting no/wrong data.

Can you post the full function now as it has changed a bit since the original posting.  Also put a breakpoint in the code and Debug.Print strSQL and paste it here to see what it is doing.

It is probably also worth creating the SQL as an Access Query and running that then we can look at converting the SQL into VBA if the query is working correctly.

Cheers, Andrew

Here's the function.  I'll work on creating the query.

Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal dtDate As Date, ByVal YTDDepreciationCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    CliNum = "ABC0"
    dtDate = #10/31/2007#
    
    If YTDDepreciationCalc <> "" And IsNull(YTDDepreciationCalc) = False Then
        
        strSQL = "SELECT " & YTDDepreciationCalc & " " & _
                 "FROM tblNonCalcSpreads INNER JOIN tblEBITDA " & _
                 "ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) " & _
                 "AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber) " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & dtDate & "#"
        Set rs = db.OpenRecordset(strSQL)
        YTDDepreciationReturnCalc = rs.Fields(0)
            
        rs.Close
        Set rs = Nothing
        
    Else
        If CliNum = "ABC0" Then
        strSQL = "SELECT Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
                 "FROM (tblNonCalcSpreads INNER JOIN tblClients " & _
                 "ON tblNonCalcSpreads.ClientNumber = tblClients.ClientNumber " & _
                 "WHERE tblNonCalcSpreads.DateOfData BETWEEN " & _
                 "IIf(Month(#" & dtDate & "#)>[tblClients].[FYE],DateSerial(Year(#" & dtDate & "#),[tblClients].[FYE]+1,1), " & _
                 "DateSerial(Year(#" & dtDate & "#)-1,[tblClients].[FYE]+1,1)) AND " & _
                 "IIf(Month(#" & dtDate & "#)>[tblClients].[FYE],DateSerial(Year(#" & dtDate & "#)+1,[tblClients].[FYE]+1,0), " & _
                 "DateSerial(Year(#" & dtDate & "#),[tblClients].[FYE]+1,0));"
 
        Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount = 0 Then
                Debug.Print rs.RecordCount
                YTDDepreciationReturnCalc = Null
                Exit Function
            End If
 
        YTDDepreciationReturnCalc = rs!Depr
        Debug.Print rs!Depr
        Else
        YTDDepreciationReturnCalc = Null
        End If
    End If
        'This will save the value back to the table
        strSQL = "UPDATE tblEBITDA " & _
                 "SET YTDDepreciation =" & YTDDepreciationReturnCalc & " " & _
                 "WHERE tblEBITDA.ClientNumber = '" & CliNum & "' " & _
                 "AND tblEBITDA.DateOfData = #" & dtDate & "#"
        CurrentDb.Execute strSQL, dbFailOnError
 
        'rs.Close
        Set rs = Nothing
        'db.Close
        Set db = Nothing
 
End Function

Select allOpen in new window

Hope you don't mind but I would like to point that in line 9 your IF doesn't need the ISNull bit because the YTDDepreciationCalc parameter is a string and therefore can not be null but could be an empty string as you are testing for.

Secondly can you paste the result of your Debug.Print

Cheers, Andrew

Another tip for you, when you layout your SQL in you include vbCrLf & before the _ when you Debug.Print the strSQL variable you will get the SQL layed out on multiple lines.

What do you get if you remove the WHERE condition completely from the SQL?

Cheers, Andrew

Debug.Print results:

SELECT Sum(tblNonCalcSpreads.Depreciation) AS Depr FROM (tblClients INNER JOIN tblNonCalcSpreads ON tblClients.ClientNumber = tblNonCalcSpreads.ClientNumber) INNER JOIN (tblEBITDACalc INNER JOIN tblEBITDA ON tblEBITDACalc.ClientNumber = tblEBITDA.ClientNumber) ON (tblNonCalcSpreads.DateOfData = tblEBITDA.DateOfData) AND (tblNonCalcSpreads.ClientNumber = tblEBITDA.ClientNumber)WHERE tblNonCalcSpreads.DateOfData BETWEEN IIf(Month(#10/31/2007#)>[tblClients].[FYE],DateSerial(Year(#10/31/2007#),[tblClients].[FYE]+1,1), DateSerial(Year(#10/31/2007#)-1,[tblClients].[FYE]+1,1)) AND IIf(Month(#10/31/2007#)>[tblClients].[FYE],DateSerial(Year(#10/31/2007#)+1,[tblClients].[FYE]+1,0), DateSerial(Year(#10/31/2007#),[tblClients].[FYE]+1,0));

Select allOpen in new window

If I remove the WHERE I still get 52 for a result.

I put the code in a query and added a GROUP BY on ClientNumber and DateOfData.  It's pulling the actual value from tblNonCalcSpreads for Depreciation instead of summing them for a YTD number.  When I remove the GROUP BY, it sums everything together, which is 52.

Would it have something to do with FYE being a number.  If you add 1 to it, most of the time you will get 13 and that's not a month.

FYE is the number of the month in which the fiscal year ends.  

With DateSerial if you say DateSerial(2008,13,1) you get 1st January 2009
Cheers, Andrew

My test of the logic with the WHERE shows 1 slight flaw, namely you end up with BETWEEN 01/01/2008 AND 01/01/2009 which means any record that do not contain a time but is 01/01/2009 would be counted in the year when I would think it should not.

Cheers, Andrew

The first 2 columns are where the month of the date is after the FYE.
BETWEEN - AND                    BETWEEN - AND
01/02/2007    01/02/2008    01/02/2006    01/02/2007
01/03/2007    01/03/2008    01/03/2006    01/03/2007
01/04/2007    01/04/2008    01/04/2006    01/04/2007
01/05/2007    01/05/2008    01/05/2006    01/05/2007
01/06/2007    01/06/2008    01/06/2006    01/06/2007
01/07/2007    01/07/2008    01/07/2006    01/07/2007
01/08/2007    01/08/2008    01/08/2006    01/08/2007
01/09/2007    01/09/2008    01/09/2006    01/09/2007
01/10/2007    01/10/2008    01/10/2006    01/10/2007
01/11/2007    01/11/2008    01/11/2006    01/11/2007
01/12/2007    01/12/2008    01/12/2006    01/12/2007
01/01/2008    01/01/2009    01/01/2007    01/01/2008

Sub AJ()
Dim cnt As Long
Dim strSQL
    
For cnt = 1 To 12
    Debug.Print DateSerial(Year(#10/31/2007#), cnt + 1, 1) _
              , DateSerial(Year(#10/31/2007#) + 1, cnt + 1, 1) _
              , DateSerial(Year(#10/31/2007#) - 1, cnt + 1, 1) _
              , DateSerial(Year(#10/31/2007#), cnt + 1, 1)
Next cnt
 
End Sub

Select allOpen in new window

To get round this if none of the date field contain time then subtract 1 from the DateSerial after the AND.

strSQL = "SELECT Sum(tblNonCalcSpreads.Depreciation) AS Depr " & _
         "FROM (tblNonCalcSpreads INNER JOIN tblClients " & _
         "ON tblNonCalcSpreads.ClientNumber = tblClients.ClientNumber " & _
         "WHERE tblNonCalcSpreads.DateOfData BETWEEN " & _
         "IIf(Month(#" & dtDate & "#)>[tblClients].[FYE],DateSerial(Year(#" & dtDate & "#),[tblClients].[FYE]+1,1), " & _
         "DateSerial(Year(#" & dtDate & "#)-1,[tblClients].[FYE]+1,1)) AND " & _
         "IIf(Month(#" & dtDate & "#)>[tblClients].[FYE],DateSerial(Year(#" & dtDate & "#)+1,[tblClients].[FYE]+1,0), " & _
         "DateSerial(Year(#" & dtDate & "#),[tblClients].[FYE]+1,0))-1;"

Select allOpen in new window

Now I'm getting 49 for a result on all of the dates.  49 is the total of 1/31/2007-11/30/2007 where 52 was 1/31/2007-12/31/2007.  All of the entries in the db will be month end dates without times.

I can not see any logic error in the WHERE condition but it can be rewritten so it is a bit more streamline and hopefully readable, it may make it easier to track down the logic issue if there is one.

I have aliased the tables and removed the [ ] as they are not required in this code to reduce the legnth of the SQL String

You coulkd eliminate the IIF completely as Month(#" & dtDate & "#) > C.FYE will return either 0 or -1 but that makes it harder to read.

Cheers, Andrew

strSQL = "SELECT Sum(S.Depreciation) AS Depr " & vbCrLf & _
         "FROM (tblNonCalcSpreads S INNER JOIN tblClients C" & vbCrLf & _
         "ON S.ClientNumber = C.ClientNumber " & vbCrLf & _
         "WHERE S.DateOfData " & vbCrLf & _
         "BETWEEN DateSerial(Year(#" & dtDate & "#)-IIF(Month(#" & dtDate & "#) > C.FYE,0,1),C.FYE+1,1)" & vbCrLf & _
         "AND     DateSerial(Year(#" & dtDate & "#)+IIF(Month(#" & dtDate & "#) > C.FYE,1,0),C.FYE+1,1)" & vbCrLf

Select allOpen in new window

GRayL,

I tried that and I receive a syntax error.

Andrew,

I receive a syntax error in the JOIN operation.

I found the syntax error in the JOIN operation, but I still get 52 as a result for all the dates.

Yea I missed a ), sorry. I am now convinced this is now a data issue but I am unable to use 2003, can you upload a 2000 version of the data file?
Cheers, Andrew

I'm running A2003 and it says the mdb at http:#a21965049 is a A2K file format.

As I said before, the BETWEEN clause as currently written will capture all the FY Month-End dates for the dtDate passed to it.  Ergo,  you have to remove the portion after the AND and replace it with dtDate.  Anybody getting it?

Thanks GRayL but I get an error when I try to open the MDB, I am actually running Access 2002

Microsoft Access cannot open this file.
The file is located outside your intranet or on an untrusted site. Microsoft Access will not open the file due to potential security problems.
To open the file, copy it to your machineor an accessible network location.

This is after saving it to My Documents or on my desktop.

Cheers, Andrew

Hi, I passed my old pc to the kids and it still has Access 2000 on it and I can open the file on it OK.

GRayL you are correct the code is returning all the data for the FY the date entered is in. All the data has FYE of 12 and when you enter the date 31/03/07 you do indeed get 52 as this is the total for 2007, it is also the total for all the data and this I beleive is causing confusion and this is a case where more data will show the query working correctly. There are several records for 2008 that are NULL, try putting data into these and see the results of the query.

The original question talks about Current FY, if you pass in todays date it will work for the current FY, the problem is there is no data to return for the current FY and the answer returned is NULL. This will be the Year To Date figure if you do not post data ahead of today.

Finally if you do post data with a date in the future then the code below, as GRayL stated earlier, will work for you.

Cheers, Andrew

strSQL = "SELECT Sum(S.Depreciation) AS Depr " & vbCrLf & _
         "FROM tblNonCalcSpreads S INNER JOIN tblClients C" & vbCrLf & _
         "ON S.ClientNumber = C.ClientNumber " & vbCrLf & _
         "WHERE S.DateOfData " & vbCrLf & _
         "BETWEEN DateSerial(Year(#" & dtDate & "#)-IIF(Month(#" & dtDate & "#) > C.FYE,0,1),C.FYE+1,1)" & vbCrLf & _
         "AND     Date()" & vbCrLf

Select allOpen in new window

You mean after double-clicking on the file?

Hi GRayL, not sure which post you are refering to, if it the one where I can't open the file then it is an Access 2002 security thing that I can't be bothered with so please ignore as I got in to the MDB in Access 2002.
Chers, Andrew

I get it now.  Sorry for being so dense there.  I got the code to work as I want now, but I'm still receiving #Error as a result when I call the function in a query.  Why would it work one way and not the other?

Sorry that is of cource NZ([Field],"")

That worked.  I'm not getting the #Error anymore, but the result I get is whatever the last value was after I ran the function previously.  When I call the function in VBA for 1/31/2007 I get 5, and then when I call the function through a query for 3/31/2007 I get 5 as well, but it should be 14.

Of course.  That worked.  I appreciate all of your help and patience on this.  Thanks!

No problem

Tedious though;-)