Type mismatch in Function

P

PatK

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
 
B

Bob Barrows

PatK said:
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'))
Click to expand...

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
 
J

John Spencer

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
 
P

PatK

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 said:
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
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
Click to expand...
.
Click to expand...
 
J

John Spencer

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
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 said:
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
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
Click to expand...
.
Click to expand...
Click to expand...
 
P

PatK

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 said:
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
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 said:
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
.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Insert Problem 4
Last Saturday of the Month 2
type mismatch error in macro in excel 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
grouping issue with ' is not null ' 1
Data type mismatch error --> text to date 1
Passing arguments to procedures 2

Top