Queries Corrupted

  • Thread starter Thread starter Kathleen
  • Start date Start date
K

Kathleen

I have had several queries corrupt over a period of
months. The lastest one involves a very simple VB module
that converts one number to another using a case
statement. That value is returned in the query. When I
attempt to filter out the value of 3 for the query, I get
a data type mismatch error. Help!

Case Statement:

Option Compare Database
Function CalculateOverall(Question_5 As Integer) As Integer
Select Case Question_5
Case 1
TempValue = 5
Case 2
TempValue = 4
Case 3
TempValue = 3
Case 4
TempValue = 2
Case 5
TempValue = 1
End Select
CalculateOverall = TempValue

End Function

Query:

SELECT [LLC Customer Survey].[Submitted Date], [LLC
Customer Survey].[Activity Number], CalculateOverall
([Question_5]) AS Question5
FROM [LLC Customer Survey]
WHERE ((([LLC Customer Survey].[Submitted Date]) Between
#8/1/2003# And #8/31/2003#) AND ((CalculateOverall
([Question_5]))<>3) AND (([LLC Customer Survey].[Activity
Type ID]) Not Like "EX-*"));
 
Not what you're asking, but, you could replace the entire function
CalculateOverall with the simple expression:

6 - Question_5

TC
 
Function CalculateOverall(Question_5 As Integer) As Integer
Click to expand...

It's nearly always fatal not to define a Variant as an argument to a
function, if you are going to apply a SQL field to it directly. Start this
like

Function CalculateOverall (Question_5 As Variant) As Variant

If IsNull(Question_5) Then
CalculateOverall = Null

ElseIf Not IsNumeric(Question_5) Then
' probably redundant, but you get the picture
CalcluateOverall = Null

Else
Select Case CInt(Question_5)

....


End If


Hope that helps

Tim F
 
Back
Top