fiscal year

[CaptR]

I have a database that calculates the total times an object appears in a
field on a form. I can get it to calculate the last 6 months or the year
from the calendar but I cannot change it to calculate a fiscal year. This is
in a function, not a query.

Public Function gettxt6MET(UserID As Variant) As Integer
Dim strWhere As String

If Not IsNull(UserID) Then
strWhere = "UserID = " & UserID & " AND inputText = 'Excused Tardy' AND
Inputdate > #" & Format(Date - 183, "mm/dd/yyyy") & "#"
gettxt6MET = DCount("UserID", "tblInput", strWhere)
End If
End Function

Is there a way to change this to calculate a fiscal year 1 April to 31 March

Thanks

 

[Marshall Barton]

I have a database that calculates the total times an object appears in a
field on a form. I can get it to calculate the last 6 months or the year
from the calendar but I cannot change it to calculate a fiscal year. This is
in a function, not a query.

Public Function gettxt6MET(UserID As Variant) As Integer
Dim strWhere As String

If Not IsNull(UserID) Then
strWhere = "UserID = " & UserID & " AND inputText = 'Excused Tardy' AND
Inputdate > #" & Format(Date - 183, "mm/dd/yyyy") & "#"
gettxt6MET = DCount("UserID", "tblInput", strWhere)
End If
End Function

Is there a way to change this to calculate a fiscal year 1 April to 31 March

Try using:

.... AND Year(Inputdate) > Year(DateAdd("m", -4, Date))

 

[CaptR]

Thanks Marshall, sorry for my ignorance by my brain is fried. I have tried
your suggestion in just about every possible way to introduce it into the
formula but cannot get it to work. I know I'm missing something simple.

Thanks in advance.

 

[CaptR]

I ended up writing a second reply as I'm not sure the other one took
allright. Sorry for my ignorance and lack of knowledge but my brain is
fried. I have tried to insert this into the equation in just about every
conceivable way that I can think of and it just will not work. I know I'm
missing something obvious.

Thanks again.

 

[Marshall Barton]

I ended up writing a second reply as I'm not sure the other one took
allright. Sorry for my ignorance and lack of knowledge but my brain is
fried. I have tried to insert this into the equation in just about every
conceivable way that I can think of and it just will not work.

When you say "it just will not work", please post a
Copy/Paste of what you tried, the result and explain wjat is
wrong with the result.

Maybe it's my brain that's fried, that should have been:

.... AND Year(DateAdd("m", -4, Inputdate)) =
Year(DateAdd("m", -4, Date))

 

[CaptR]

Thanks Marshall,

Yes, that would make a world of difference. All I was getting was syntax
errors or errors on the form of Name. But I will try this again tomorrow. I
found another way but it is not quite working the way I need. I think I may
have to move it from the table it is pulling the info from and work out a
query. In my infinite wisdom I was trying to avoid loading the database with
all kinds of queries.

Hopefully I will be able to let you know how it works tomorrow.

Bingo, just tried it and it works.

Thanks