|
|
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
|
|
CaptR wrote:
[Quoted Text] >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))
--
Marsh
MVP [MS Access]
|
|
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.
"Marshall Barton" wrote:
[Quoted Text] > CaptR wrote:
>
> >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))
>
> --
> Marsh
> MVP [MS Access]
>
|
|
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" wrote:
[Quoted Text] > CaptR wrote:
>
> >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))
>
> --
> Marsh
> MVP [MS Access]
>
|
|
CaptR wrote:
[Quoted Text] >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))
--
Marsh
MVP [MS Access]
|
|
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
"Marshall Barton" wrote:
[Quoted Text] > CaptR wrote:
>
> >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))
>
> --
> Marsh
> MVP [MS Access]
>
|
|
|