Current Year Date Filter

[barkly]

I run the attached <begindate enddate> module within various queries to select for events that occur within my current financial year which runs form July 1 to June 30.

The queries will work perfectly if there has been an event within the current financial year but if there hasn't been any event the query returns a blank - no data which is unsatisfactory.

No amount of Nz or ISNuLL will give me a response so I believe the issue must be with the date module. Would appreciate if someone could advise how the module might be improved (if this is the problem) so that I at least can get zero(0) responses in my queries if there are no dates. Also of course if there are no dates within the fin year there is no other data for the query to find as well.

A typical simple query is -

SELECT "Cash Div's this Fin Year" AS Text2, CDbl(Nz(DSum("[DivAmount]","[tbl_Dividend]"),0)) AS Val2
FROM tbl_Dividend INNER JOIN qry_Account ON tbl_Dividend.AccountID = qry_Account.AccountID
WHERE (((qry_Account.InUSe)="Yes") AND ((tbl_Dividend.DivDate)>Begindate(Date()) And (tbl_Dividend.DivDate)<Enddate(Date())))
GROUP BY "Cash Div's this Fin Year", CDbl(Nz(DSum("[DivAmount]","[tbl_Dividend]"),0));

.

And the date modules are -

Public Function BeginDate(dtCurrDate As Date) As Date

Dim startDay As Integer
Dim startMonth As Integer
Dim endDay As Integer
Dim endMonth As Integer
Dim newYear As Integer

startDay = 1
startMonth = 7
endDay = 30
endMonth = 6

If Month(dtCurrDate) >= startMonth Then
    newYear = Year(dtCurrDate)
Else
    newYear = Year(dtCurrDate) - 1
End If

BeginDate = CDate(startDay & "/" & startMonth & "/" & newYear)

End Function

Public Function EndDate(dtCurrDate As Date) As Date

Dim startDay As Integer
Dim startMonth As Integer
Dim endDay As Integer
Dim endMonth As Integer
Dim newYear As Integer

startDay = 1
startMonth = 7
endDay = 30
endMonth = 6

If Month(dtCurrDate) >= startMonth Then
    newYear = Year(dtCurrDate) + 1
Else
    newYear = Year(dtCurrDate)
End If

EndDate = CDate(endDay & "/" & endMonth & "/" & newYear)

End Function

.

Thanks in advance

[amrut]

If there are no records matching the criteria in query, it will not return any results which is very usual. Further, why are you using a function to calculate the financial year.
You can use something like

>IIf(Month(Date())>6,DateSerial(Year(Date()),7,1),DateSerial(Year(Date())-1,7,1))

in the query criteria for begin date.

[barkly]

amrut,

Thanks for your response.

Your current year date filter much simpler than mine so I have implemented that.

But I found I was still not getting any response out of my query when zero data to work with so not really a date filter type problem but my naming expression was causing the issue so qualified my Text naming statement as per this -

 SELECT IIf(IsNull([Val2]),"Cash Div's this Fin Year","Cash Div's this Fin Year") AS Text2, CDbl(Nz(Sum([tbl_Dividend].[DivAmount]),0)) AS Val2
FROM tbl_Dividend INNER JOIN qry_Account ON tbl_Dividend.AccountID = qry_Account.AccountID
WHERE (((qry_Account.InUSe)="Yes") AND ((tbl_Dividend.DivDate)>IIf(Month(Date())>6,DateSerial(Year(Date()),7,1),DateSerial(Year(Date())-1,7,1))));

This now gives me a response if there is data to work with and a zero if there is none.

Thankyou for your kick starter - appreciated

barkly

[vincent-leeway]

Yes a check before hand to check for null values is the way to go, very nice code.

[amrut]

Your current year date filter much simpler than mine so I have implemented that.

You need to construct a similar filter for year end too. As suggested, check by using DCount or a suitable function for zero records returned before you proceed for processing the records.
Like

 If DCount("*","YourQueryName")=0 then
'Do this
Else
'Do this stuff
Endif

[barkly]

Thanks again amrut,

I haven't used a filter construct like that before - something for me to work on.

Appreciated

regards

barkly