Difine Data Type in Module, or Help me find it

[BillieBurgess]

I have Access 2002
Last Year Paul Bricker helped me create a Function that listed the Fiscal Month. Our Month-end is the last Friday of the Month. I then wrote a code that listed the Fiscal Year (Oct - Sept). The Function works great in reports but not so great in Queries. Whenever I try to use it as a criteria I get this Error "Data Type Mismatch in Criteria Expression" and then all the fields in the query go bad (#Name?). I have tried enclosing the criteria in quotations for text data, number signs for date data, percent signs for variant data (desperate), but nothing seems to work. Both Modules are below, but I suspect the problem is the same for both. If someone could either help me better define the data type, find a way to query for them or help me fix whatever I have wrong I would sincerely appreciate it.

Function Fiscal_Month(dteInput As Date) 'Returns the last Friday of month for printing report.
Dim intDays As Integer
Dim x As Date
Dim MyDate As Date

intDays = DateSerial(Year(dteInput), Month(dteInput) + 1, Day(dteInput)) _
- DateSerial(Year(dteInput), Month(dteInput), Day(dteInput))
x = DatePart("m", dteInput) & "/" & intDays & "/" & DatePart("yyyy", dteInput)
Select Case Weekday(x)
Case 1
MyDate = x - 2
Case 2
MyDate = x - 3
Case 3
MyDate = x - 4
Case 4
MyDate = x - 5
Case 5
MyDate = x - 6
Case 7
MyDate = x - 1
Case Else
MyDate = x
End Select
If dteInput > MyDate Then
Fiscal_Month = Month(dteInput) + 1
If Fiscal_Month = 13 Then
Fiscal_Month = 1
End If
Else
Fiscal_Month = Month(dteInput)
End If

End Function


Function Fiscal_Year(dteInput As Date) 'Returns the last Friday of month for printing report.

Dim intDays As Integer
Dim intYear As Variant
Dim x As Date
Dim MyDate As Date


intDays1 = DateSerial(Year(dteInput), Month(dteInput) + 1, Day(dteInput)) _
- DateSerial(Year(dteInput), Month(dteInput), Day(dteInput))
x = DatePart("m", dteInput) & "/" & intDays1 & "/" & DatePart("yyyy", dteInput)
Select Case Weekday(x)
Case 1
MyDate = x - 2
Case 2
MyDate = x - 3
Case 3
MyDate = x - 4
Case 4
MyDate = x - 5
Case 5
MyDate = x - 6
Case 7
MyDate = x - 1
Case Else
MyDate = x
End Select
If dteInput > MyDate Then
Int_Days = Month(dteInput) + 1
Else
Int_Days = Month(dteInput)
End If


If Int_Days > 9 Then
Fiscal_Year = Year(dteInput) + 1
Else
Fiscal_Year = Year(dteInput)
End If
Exit Function

End Function

 

[vbajock]

Any chance you could post the SQL from the query you are trying to execute?

 

[BillieBurgess]

Yes, sorry about that. An ex: is below. I want the query to pull only the current fiscal year. However, whenever I try to put Year (Now ()) in the FY Criteria it would not work, so I tried 2004 instead and still got the Data type error. A date such as #10/01/04# does not work either.

Query SQL

SELECT [Trip DM].[Dow's DM#], [Trip DM].Date, [Trip DM].[Account No], [Trip DM].[Credit Invoice], [Trip Reimbursements].[RA #], [Trip Reimbursements].[Serial No], [Trip Reimbursements].[Date Issued], [Trip Reimbursements].[Coupon amount], [Trip Reimbursements].[SCR Approval No], Fiscal_Year([Date]) AS FY
FROM [Trip DM] INNER JOIN [Trip Reimbursements] ON [Trip DM].[DM ID] = [Trip Reimbursements].[DM ID]
WHERE ((([Trip DM].[Credit Invoice]) Is Not Null));

 

[PHV]

Have you tried strong typing the definition of your functions, like this ?
Function Fiscal_Month(dteInput As Date) As Integer
Function Fiscal_Year(dteInput As Date) As Integer

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vbajock]

Since it works in one but not the other my best guess is that the query processor is having a hard time with "Fiscal_Year([Date]) AS FY" because Date is a reserved word. First try Fiscal_Year([tblName].[Date]), next rename the field if you can.