• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

VBA Function to find a fiscal month start date

Hi Experts,

Our fiscal year starts in November and ends in September.  I have built a function that finds the latest fiscal November start month in the data.  (i.e. if the data had 2013/11/30 and 2012/11/30, 2013/11/30 would be returned)  I've finally got it so it doesn't show errors and when I press F8 the variables show the correct date, however, the problem is that when I try to use the function in a query to filter the date by the function, I'm not getting any results at all.  Any ideas on how to fix my function?

Thanks!


Option Compare Database
Option Explicit

Public Function MaxDataDate()
Dim mDataDate As Date
mDataDate = DLookup("MaxOfDataDate", "q_DataDate_MAX")
MaxDataDate = mDataDate
End Function

Public Function MinFiscalYearMonth() As Date
Dim fDataDate As Date
Dim fYear As Integer
Dim vdate As Variant
Dim testNovember As Date
Dim testNovember2 As Date

fYear = Year(MaxDataDate())
vdate = fYear & "/11/30"

testNovember = DateValue(vdate)
testNovember2 = DateAdd("yyyy", -1, testNovember)

If DCount("[datadate]", "t_OD_Database_Portfolio_Level", "[datadate] = " & CDate(testNovember)) > 0 Then

    MinFiscalYearMonth = testNovember
   
    Else: MinFiscalYearMonth = testNovember2
End If

End Function
0
grmcra
Asked:
grmcra
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this

If DCount("[datadate]", "t_OD_Database_Portfolio_Level", "[datadate] = #" &  testNovember & "#") > 0 Then


or revised the function


Public Function MinFiscalYearMonth() As Date
Dim fDataDate As Date
Dim fYear As Integer
Dim vdate As date
Dim testNovember As Date
Dim testNovember2 As Date

fYear = Year(MaxDataDate())
vdate =dateserial(Year(MaxDataDate()),11,30)

testNovember = vdate
testNovember2 = DateAdd("yyyy", -1, testNovember)

If DCount("[datadate]", "t_OD_Database_Portfolio_Level", "[datadate] = #" &  testNovember & "#") > 0 Then

    MinFiscalYearMonth = testNovember
   
    Else: MinFiscalYearMonth = testNovember2
End If

End Function
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show how you're doing this:

when I try to use the function in a query to filter the date by the function
Generally, you'd use this in the Query itself, and then filter the columns, like this:

SELECT Col1, Col2, MinFiscalYearMonth AS Col3 FROM SomeTable WHERE MinFiscalYearMonth>#11=2013#
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now