Data type mismatch in criteria expression

[TimE]

I am getting "Data type mismatch in criteria expression" error with the following query and module:

Query Expression:
Age: Max(IIf(fDetermineAging(tblIST!SODate)>30,"Over 30",IIf(fDetermineAging(tblIST!SODate)>20,"21-30",IIf(fDetermineAging(tblIST!SODate)>10,"11-20","0-10"))))


Module fDetermineAging:

Public Function fDetermineAging(ByVal SODate As Date) As Integer

Dim iDte As Integer
Dim iAge As Integer

iDte = 0
iAge = 0

While SODate + iDte < Date
If OffDays(SODate + iDte) = False Then
iAge = iAge + 1
End If
iDte = iDte + 1
Wend

fDetermineAging = iAge

End Function


SODate is Date/Time field type in table.


ANY help would be greatly appreciated.

 

[RuralGuy]

Well let's start somewhere. It looks like your Age field in the query is looking for a Max of a string. What exactly were you looking for in that field?

 

[TimE]

To tell you the truth, I have inherited this database. I am assuming it was setup to look at the MAX Age of the SalesOrders. There will be multiple SalesOrders per order. It is grouped by SalesOrder. The AGE is the difference from the time I got the order and the date completed.

The form that calls this query uses this in a Value List Row Source (OrderType):
"New Hire";"Refresh";"Server";"Network";"Other"

OrderType: IIf(tblIST!RequestType="SV","Server",IIf(tblIST!RequestType="NW","Network",IIf(tblIST!RequestType="NH" Or tblIST!RequestType="JH","New Hire" & IIf(IsNull(tblIST!ReqNum),""," (" & tblIST!ReqNum & ")"),IIf(tblIST!RequestType="RF","Refresh","Other"))))


It seems to work when selecting these:
New Hire
Refresh
Server
Network


OTHER does not work and blank does not work. I would like to be able to pull ALL of these.

 

[RuralGuy]

Well right now the Max() function in the query has to pick from:
"Over 30", "21-30 , "11-20", "0-10"
depending on what returns from the fDetermineAging() function. I seriously doubt that Max() is needed or functional here. Start by removing the Max( from the beginning and one ")" from the end os the field formula. That should eliminate the "Data Type mismatch" in the query. Then post the code for the OffDays() function so we can review it.

 

[TimE]

Removing the MAX( and the last ) did not allow the Other or blank (ALL is what it should be) to work, still getting error.

I have looked everywhere and I can not find code for Offdays(). Any ideas where I should look?

 

[RuralGuy]

Use find under edit (^F) and select Current Project.

 

[TimE]

Public Function OffDays(dtDate As Date) As Boolean

If dtDate = #1/1/2002# Then
OffDays = True
ElseIf dtDate = #7/4/2002# Then
OffDays = True
ElseIf dtDate = #9/2/2002# Then
OffDays = True
ElseIf dtDate = #11/28/2002# Then
OffDays = True
ElseIf dtDate = #11/29/2002# Then
OffDays = True
ElseIf dtDate = #12/25/2002# Then
OffDays = True
ElseIf dtDate = #12/26/2002# Then
OffDays = True
ElseIf dtDate = #12/27/2002# Then
OffDays = True
ElseIf dtDate = #12/28/2002# Then
OffDays = True
ElseIf dtDate = #12/29/2002# Then
OffDays = True
ElseIf dtDate = #12/30/2002# Then
OffDays = True
ElseIf dtDate = #12/31/2002# Then
OffDays = True
ElseIf dtDate = #1/1/2003# Then
OffDays = True
ElseIf dtDate = #6/26/2003# Then
OffDays = True
ElseIf dtDate = #7/4/2003# Then
OffDays = True
ElseIf dtDate = #9/1/2003# Then
OffDays = True
ElseIf dtDate = #11/27/2003# Then
OffDays = True
ElseIf dtDate = #11/28/2003# Then
OffDays = True
ElseIf dtDate = #12/24/2003# Then
OffDays = True
ElseIf dtDate = #12/25/2003# Then
OffDays = True
ElseIf dtDate = #12/26/2003# Then
OffDays = True
ElseIf dtDate = #12/29/2003# Then
OffDays = True
ElseIf dtDate = #12/30/2003# Then
OffDays = True
ElseIf dtDate = #12/31/2003# Then
OffDays = True
ElseIf InStr(CStr(CInt(dtDate - #1/4/2002#) / 14), ".") = 0 Then
OffDays = True
ElseIf InStr(CStr(CInt(dtDate - #1/5/2002#) / 7), ".") = 0 Then
OffDays = True
ElseIf InStr(CStr(CInt(dtDate - #1/6/2002#) / 7), ".") = 0 Then
OffDays = True
Else
OffDays = False
End If

End Function

 

[RuralGuy]

Well OffDays() might return True if it were 2 or 3 years ago. Not much use today. We started with:

I am getting "Data type mismatch in criteria expression" error with the following query and module...

Is that still the case?

 

[TimE]

Yes....but only when I do not select anything (blank...assumed to be ALL) or select OTHER as the ordertype.

The form that calls this query uses this in a Value List Row Source (OrderType):
"New Hire";"Refresh";"Server";"Network";"Other"

OrderType: IIf(tblIST!RequestType="SV","Server",IIf(tblIST!RequestType="NW","Network",IIf(tblIST!RequestType="NH" Or tblIST!RequestType="JH","New Hire" & IIf(IsNull(tblIST!ReqNum),""," (" & tblIST!ReqNum & ")"),IIf(tblIST!RequestType="RF","Refresh","Other"))))


It seems to work when selecting these:
New Hire
Refresh
Server
Network


OTHER does not work and blank does not work. I would like to be able to pull ALL of these.

 

[RuralGuy]

I take it OrderType: is a field in your query. Can you run the query by itself successfully? What code is in the AfterUpdate event of this ComboBox?

 

[TimE]

Private Sub cmdDetail_Click()
On Error GoTo Err_cmdDetail_Click

Dim stDocName As String

stDocName = "rptAgingReport"
DoCmd.OpenReport stDocName, acPreview, , GenerateWhere()

Exit_cmdDetail_Click:
Exit Sub

Err_cmdDetail_Click:
MsgBox Err.Description
Resume Exit_cmdDetail_Click

End Sub


Private Function GenerateWhere() As String

If Not IsNull(Me.OrderType) And Me.OrderType <> "" Then
GenerateWhere = "[OrderType] = '" & Me.OrderType & "'"
Else
GenerateWhere = ""
End If

End Function

Private Sub OrderType_AfterUpdate()

End Sub

 

[RuralGuy]

When you run the query as stand alone do you get "Other" returned in the [OrderType] field? What else is returned in that field other than what you have already been looking for? Maybe a Null?