asked on

# fiscal year end calculations

I need to perform some calculations based on our clients fiscal year ends.  Not all of our clients have the same fiscal year end month.  Some use December, others Sept, etc.  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?
Microsoft AccessSQL

Last Comment
GRayL
Emil_Gray

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

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);

GRayL

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;
GRayL

Anybody there?
Gustav Brock

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
``````
nfstrong

ASKER

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
``````
nfstrong

ASKER

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?
GRayL

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.
nfstrong

ASKER

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

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

ASKER

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

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 ;-)
nfstrong

ASKER

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?
Gustav Brock

> 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
Gustav Brock

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)

``````
nfstrong

ASKER

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
``````
Gustav Brock

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
``````
nfstrong

ASKER

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?
Gustav Brock

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

FYEnd <-> FYE

or vice versa.

/gustav
GRayL

Anything further on my input?
nfstrong

ASKER

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
``````
Gustav Brock

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>
``````
nfstrong

ASKER

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));"
``````
GRayL

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?
Gustav Brock

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
GRayL

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;
nfstrong

ASKER

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

Can you post the SQL that you are using please.
nfstrong

ASKER

``````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
``````
GRayL

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
nfstrong

ASKER

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())));
``````
GRayL

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
nfstrong

ASKER

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

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
``````
nfstrong

ASKER

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
``````
GRayL

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

ASKER

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
``````
GRayL

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
nfstrong

ASKER

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
``````
GRayL

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.
nfstrong

ASKER

Yes, the joins are correct.

Here is the db.
PCDB-test2ee.zip
GRayL

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

From where do you call the function  YTDDepreciationReturnCalc
nfstrong

ASKER

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.
nfstrong

ASKER

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

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

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

?  YTDDepreciationReturnCalc()
nfstrong

ASKER

Call YTDDepreciationReturnCalc(ClientNumber, DateOfData, YTDDepreciationCalc)
GRayL

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

ASKER

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

Change line 1 to:

``````Public Function YTDDepreciationReturnCalc(ByVal CliNum As String, ByVal strDate As String, ByVal YTDDepreciationCalc As String) As Variant
``````
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?
GRayL

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

ASKER

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?
nfstrong

ASKER

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

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
``````
nfstrong

ASKER

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.
GRayL

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.
nfstrong

ASKER

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
``````
GRayL

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?
GRayL

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

tblEBITDA

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

ASKER

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.
GRayL

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

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.

nfstrong

ASKER

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

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.
nfstrong

ASKER

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"));
``````
GRayL

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?
nfstrong

ASKER

Here you go.
PCDB-test2ee.zip
GRayL

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
``````
GRayL

``````http:#a21963861
``````
nfstrong

ASKER

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.
GRayL

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.
nfstrong

ASKER

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.
GRayL

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.

nfstrong

ASKER

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
``````
GRayL

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?
GRayL

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
nfstrong

ASKER

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.
GRayL

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.

GRayL

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.
nfstrong

ASKER

See ID: 21973040

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

The field tblEBITDACalc].[YTDDepreciationCalc] is empty.
nfstrong

ASKER

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.
GRayL

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
``````
nfstrong

ASKER

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.
GRayL

``````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;"
``````
nfstrong

ASKER

Ok, but how does that capture the FYE in tblClients?
ASKER CERTIFIED SOLUTION
GRayL

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
nfstrong

ASKER

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

I'm trying to enlist additional help.
SOLUTION
TextReport

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Jim P.

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.
nfstrong

ASKER

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.
TextReport

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#"
``````
nfstrong

ASKER

I have the following line in my function:

strDate = "1/31/2007"

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

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

strDate = #07/14/2008#
nfstrong

ASKER

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.
TextReport

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
nfstrong

ASKER

strDate is a date variable.  see post # 21975272.
TextReport

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 & "#));"
``````
nfstrong

ASKER

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.
TextReport

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
nfstrong

ASKER

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
``````
TextReport

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

TextReport

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
nfstrong

ASKER

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));
``````
nfstrong

ASKER

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.
nfstrong

ASKER

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.
GRayL

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

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

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
``````
TextReport

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;"
``````
nfstrong

ASKER

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.
SOLUTION
GRayL

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
TextReport

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
``````
nfstrong

ASKER

GRayL,

I tried that and I receive a syntax error.
nfstrong

ASKER

Andrew,

I receive a syntax error in the JOIN operation.
nfstrong

ASKER

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

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
GRayL

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

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?
TextReport

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
TextReport

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
``````
GRayL

You mean after double-clicking on the file?
TextReport

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
nfstrong

ASKER

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?
SOLUTION
TextReport

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
TextReport

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

ASKER

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.
SOLUTION
TextReport

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
nfstrong

ASKER

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

No problem
GRayL

Tedious though;-)
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY