Type mismatch in Function

Hoping someone might be able to help. I have the following Function that I
have used in excel 2007 (and it works fine), that I am trying to now use in
MS Access. Basically you pass a date string to the function and it returns a
fiscal year quarter (our fiscal years starts on Nov 1). The code, when runs,
gets a type mismatch whenever it gets to one of the lines coded (entire
function at bottom):

FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) & strQtr

When I hover my mouse over inDate, I will see dates like I would expect
("11/14/2009"). All these fields are string, ALTHO when I used the
FiscalQtr() function in a query, the date I am passing is being converted
from a date format, to a string format, and that seems to be working fine.
Ie, in my query I have (design view), the following:

FiscalQ: fiscalqtr(Format$([sharepointlistname].[Prod (Month
Start)],'mm/dd/yyyy'))

Before I inserted the FiscalQtr function code, ie, had on they formatted
text, that output exactly what I expected. Here is the entire code.

Ideas? PatK

Option Compare Database

Function FiscalQtr(inDate As String) As String
Dim strQtr As String
Dim mm As Integer
Dim yeardays As Integer

If (DatePart("yyyy", inDate)) Mod 4 = 0 Then
yeardays = 364
Else
yeardays = 365
End If

strQtr = ""

Select Case DatePart("m", inDate)
Case 11, 12, 1
strQtr = " Q1"
Case 2, 3, 4
strQtr = " Q2"
Case 5, 6, 7
strQtr = " Q3"
Case 8, 9, 10
strQtr = " Q4"
Case Else
strQtr = ""
End Select
If strQtr " Q1" Then
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
Else
If DatePart("m", inDate) 1 Then
Debug.Print Right(DatePart("yyyy", inDate + yeardays), 2)
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) &
strQtr
Else
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
End If
End If


End Function

PatK wrote:
Hoping someone might be able to help. I have the following Function
that I have used in excel 2007 (and it works fine), that I am trying
to now use in MS Access. Basically you pass a date string to the
function and it returns a fiscal year quarter (our fiscal years
starts on Nov 1). The code, when runs, gets a type mismatch
whenever it gets to one of the lines coded (entire function at
bottom):

FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) &
strQtr

When I hover my mouse over inDate, I will see dates like I would
expect ("11/14/2009"). All these fields are string, ALTHO when I
used the FiscalQtr() function in a query, the date I am passing is
being converted from a date format, to a string format, and that
seems to be working fine. Ie, in my query I have (design view), the
following:

FiscalQ: fiscalqtr(Format$([sharepointlistname].[Prod (Month
Start)],'mm/dd/yyyy'))


No. Don't return a string to a function that is expecting a date/time. Use
CDate()

FiscalQtr = "FY" & _
Right(DatePart("yyyy", CDate(inDate) + yeardays), 2) & strQtr

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

You might try the following expression:

IIF(IsDate([sharepointlistname].[Prod (Month Start)]),
"FY" & Format(DateAdd("M",2,CDate([sharepointlistname].[Prod (Month
Start)])),"yy \Qq")
,Null)

IF you wanted you could modify your FiscalQtr function

Public Function FiscalQtr(vDateIn)
If IsDate(vDateIn) Then
FiscalQtr ="FY" & Format(DateAdd("M",2,CDate(vDateIn)),"yy \Qq")
Else
FiscalQtr = Null
End IF
End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

PatK wrote:
Hoping someone might be able to help. I have the following Function that I
have used in excel 2007 (and it works fine), that I am trying to now use in
MS Access. Basically you pass a date string to the function and it returns a
fiscal year quarter (our fiscal years starts on Nov 1). The code, when runs,
gets a type mismatch whenever it gets to one of the lines coded (entire
function at bottom):

FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) & strQtr

When I hover my mouse over inDate, I will see dates like I would expect
("11/14/2009"). All these fields are string, ALTHO when I used the
FiscalQtr() function in a query, the date I am passing is being converted
from a date format, to a string format, and that seems to be working fine.
Ie, in my query I have (design view), the following:

FiscalQ: fiscalqtr(Format$([sharepointlistname].[Prod (Month
Start)],'mm/dd/yyyy'))

Before I inserted the FiscalQtr function code, ie, had on they formatted
text, that output exactly what I expected. Here is the entire code.

Ideas? PatK

Option Compare Database

Function FiscalQtr(inDate As String) As String
Dim strQtr As String
Dim mm As Integer
Dim yeardays As Integer

If (DatePart("yyyy", inDate)) Mod 4 = 0 Then
yeardays = 364
Else
yeardays = 365
End If

strQtr = ""

Select Case DatePart("m", inDate)
Case 11, 12, 1
strQtr = " Q1"
Case 2, 3, 4
strQtr = " Q2"
Case 5, 6, 7
strQtr = " Q3"
Case 8, 9, 10
strQtr = " Q4"
Case Else
strQtr = ""
End Select
If strQtr " Q1" Then
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
Else
If DatePart("m", inDate) 1 Then
Debug.Print Right(DatePart("yyyy", inDate + yeardays), 2)
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) &
strQtr
Else
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
End If
End If


End Function

Thanks to both of you on your replies. Question, John, for you: The
fiscalQtr funtion you provided below: That would have no notion of the start
of my fiscal year, right? That would only return standard "calendar" fiscal
quarters, right? Assume so, given I see no reference to that. But the code
intrigues me....THANKS!

patk

"John Spencer" wrote:

You might try the following expression:

IIF(IsDate([sharepointlistname].[Prod (Month Start)]),
"FY" & Format(DateAdd("M",2,CDate([sharepointlistname].[Prod (Month
Start)])),"yy \Qq")
,Null)

IF you wanted you could modify your FiscalQtr function

Public Function FiscalQtr(vDateIn)
If IsDate(vDateIn) Then
FiscalQtr ="FY" & Format(DateAdd("M",2,CDate(vDateIn)),"yy \Qq")
Else
FiscalQtr = Null
End IF
End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

PatK wrote:
Hoping someone might be able to help. I have the following Function that I
have used in excel 2007 (and it works fine), that I am trying to now use in
MS Access. Basically you pass a date string to the function and it returns a
fiscal year quarter (our fiscal years starts on Nov 1). The code, when runs,
gets a type mismatch whenever it gets to one of the lines coded (entire
function at bottom):

FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) & strQtr

When I hover my mouse over inDate, I will see dates like I would expect
("11/14/2009"). All these fields are string, ALTHO when I used the
FiscalQtr() function in a query, the date I am passing is being converted
from a date format, to a string format, and that seems to be working fine.
Ie, in my query I have (design view), the following:

FiscalQ: fiscalqtr(Format$([sharepointlistname].[Prod (Month
Start)],'mm/dd/yyyy'))

Before I inserted the FiscalQtr function code, ie, had on they formatted
text, that output exactly what I expected. Here is the entire code.

Ideas? PatK

Option Compare Database

Function FiscalQtr(inDate As String) As String
Dim strQtr As String
Dim mm As Integer
Dim yeardays As Integer

If (DatePart("yyyy", inDate)) Mod 4 = 0 Then
yeardays = 364
Else
yeardays = 365
End If

strQtr = ""

Select Case DatePart("m", inDate)
Case 11, 12, 1
strQtr = " Q1"
Case 2, 3, 4
strQtr = " Q2"
Case 5, 6, 7
strQtr = " Q3"
Case 8, 9, 10
strQtr = " Q4"
Case Else
strQtr = ""
End Select
If strQtr " Q1" Then
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
Else
If DatePart("m", inDate) 1 Then
Debug.Print Right(DatePart("yyyy", inDate + yeardays), 2)
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) &
strQtr
Else
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
End If
End If


End Function
.

When you add 2 months to the date you get a calculated date in the two months
in the future

So November 2009 dates yield a calculated date in January 2010.

If you use that calculated date you will get quarter 1 of year 2010 to show
for the date in November 2009

August 2010 will return 2010 Q3

So you should see your Fiscal Year and your Fiscal Quarter for any date.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

PatK wrote:
Thanks to both of you on your replies. Question, John, for you: The
fiscalQtr funtion you provided below: That would have no notion of the start
of my fiscal year, right? That would only return standard "calendar" fiscal
quarters, right? Assume so, given I see no reference to that. But the code
intrigues me....THANKS!

patk

"John Spencer" wrote:

You might try the following expression:

IIF(IsDate([sharepointlistname].[Prod (Month Start)]),
"FY" & Format(DateAdd("M",2,CDate([sharepointlistname].[Prod (Month
Start)])),"yy \Qq")
,Null)

IF you wanted you could modify your FiscalQtr function

Public Function FiscalQtr(vDateIn)
If IsDate(vDateIn) Then
FiscalQtr ="FY" & Format(DateAdd("M",2,CDate(vDateIn)),"yy \Qq")
Else
FiscalQtr = Null
End IF
End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

PatK wrote:
Hoping someone might be able to help. I have the following Function that I
have used in excel 2007 (and it works fine), that I am trying to now use in
MS Access. Basically you pass a date string to the function and it returns a
fiscal year quarter (our fiscal years starts on Nov 1). The code, when runs,
gets a type mismatch whenever it gets to one of the lines coded (entire
function at bottom):

FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) & strQtr

When I hover my mouse over inDate, I will see dates like I would expect
("11/14/2009"). All these fields are string, ALTHO when I used the
FiscalQtr() function in a query, the date I am passing is being converted
from a date format, to a string format, and that seems to be working fine.
Ie, in my query I have (design view), the following:

FiscalQ: fiscalqtr(Format$([sharepointlistname].[Prod (Month
Start)],'mm/dd/yyyy'))

Before I inserted the FiscalQtr function code, ie, had on they formatted
text, that output exactly what I expected. Here is the entire code.

Ideas? PatK

Option Compare Database

Function FiscalQtr(inDate As String) As String
Dim strQtr As String
Dim mm As Integer
Dim yeardays As Integer

If (DatePart("yyyy", inDate)) Mod 4 = 0 Then
yeardays = 364
Else
yeardays = 365
End If

strQtr = ""

Select Case DatePart("m", inDate)
Case 11, 12, 1
strQtr = " Q1"
Case 2, 3, 4
strQtr = " Q2"
Case 5, 6, 7
strQtr = " Q3"
Case 8, 9, 10
strQtr = " Q4"
Case Else
strQtr = ""
End Select
If strQtr " Q1" Then
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
Else
If DatePart("m", inDate) 1 Then
Debug.Print Right(DatePart("yyyy", inDate + yeardays), 2)
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) &
strQtr
Else
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
End If
End If


End Function
.

This is "SO" cool. Makes so much sense. Neat way to use DateAdd. Works
great in my Excel code, as well. It's a beautiful thing. Thanks for opening
my eyes!

"John Spencer" wrote:

When you add 2 months to the date you get a calculated date in the two months
in the future

So November 2009 dates yield a calculated date in January 2010.

If you use that calculated date you will get quarter 1 of year 2010 to show
for the date in November 2009

August 2010 will return 2010 Q3

So you should see your Fiscal Year and your Fiscal Quarter for any date.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

PatK wrote:
Thanks to both of you on your replies. Question, John, for you: The
fiscalQtr funtion you provided below: That would have no notion of the start
of my fiscal year, right? That would only return standard "calendar" fiscal
quarters, right? Assume so, given I see no reference to that. But the code
intrigues me....THANKS!

patk

"John Spencer" wrote:

You might try the following expression:

IIF(IsDate([sharepointlistname].[Prod (Month Start)]),
"FY" & Format(DateAdd("M",2,CDate([sharepointlistname].[Prod (Month
Start)])),"yy \Qq")
,Null)

IF you wanted you could modify your FiscalQtr function

Public Function FiscalQtr(vDateIn)
If IsDate(vDateIn) Then
FiscalQtr ="FY" & Format(DateAdd("M",2,CDate(vDateIn)),"yy \Qq")
Else
FiscalQtr = Null
End IF
End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

PatK wrote:
Hoping someone might be able to help. I have the following Function that I
have used in excel 2007 (and it works fine), that I am trying to now use in
MS Access. Basically you pass a date string to the function and it returns a
fiscal year quarter (our fiscal years starts on Nov 1). The code, when runs,
gets a type mismatch whenever it gets to one of the lines coded (entire
function at bottom):

FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) & strQtr

When I hover my mouse over inDate, I will see dates like I would expect
("11/14/2009"). All these fields are string, ALTHO when I used the
FiscalQtr() function in a query, the date I am passing is being converted
from a date format, to a string format, and that seems to be working fine.
Ie, in my query I have (design view), the following:

FiscalQ: fiscalqtr(Format$([sharepointlistname].[Prod (Month
Start)],'mm/dd/yyyy'))

Before I inserted the FiscalQtr function code, ie, had on they formatted
text, that output exactly what I expected. Here is the entire code.

Ideas? PatK

Option Compare Database

Function FiscalQtr(inDate As String) As String
Dim strQtr As String
Dim mm As Integer
Dim yeardays As Integer

If (DatePart("yyyy", inDate)) Mod 4 = 0 Then
yeardays = 364
Else
yeardays = 365
End If

strQtr = ""

Select Case DatePart("m", inDate)
Case 11, 12, 1
strQtr = " Q1"
Case 2, 3, 4
strQtr = " Q2"
Case 5, 6, 7
strQtr = " Q3"
Case 8, 9, 10
strQtr = " Q4"
Case Else
strQtr = ""
End Select
If strQtr " Q1" Then
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
Else
If DatePart("m", inDate) 1 Then
Debug.Print Right(DatePart("yyyy", inDate + yeardays), 2)
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate + yeardays), 2) &
strQtr
Else
FiscalQtr = "FY" & Right(DatePart("yyyy", inDate), 2) & strQtr
End If
End If


End Function
.

.