Crosstab Query

[errolf]

Iam using a crosstab query that uses a function that should return a possible 3 values Commercial Retail or Not Valid the function code is as follows
Function RetGrp(MyGrp As Variant)
Select Case MyGrp
Case 1 To 11: RetGrp = "Retail"
Case 22: RetGrp = "Retail"
Case 25: RetGrp = "Retail"
Case 12 To 21: RetGrp = "Commercial"
Case 23 To 24: RetGrp = "Commercial"
Case Else: RetGrp = "Not Valid"
RetGrp = RetGrp

End Select

End Function
The Sql is
TRANSFORM Sum([nett]-[gst]) AS Expr2
SELECT RetGrp([class]) AS Expr1
FROM SCEX
WHERE (((SCEX.INV_DATE) Between #12/1/2002# And #12/31/2002#) AND ((RetGrp([class]))="retail&quot)
GROUP BY RetGrp([class])
PIVOT Format([Inv_Date],"mmm-yy"
The query only returns "Commercial" values when no criteria is selected but will return the criteria selected eg "Retail" or "Not Valid"

What am I doing wrong?

Errol

 

[ssecca]

Try this as teh function...

Function RetGrp(MyGrp As Variant)as variant
Select Case MyGrp
Case 1 To 11
RetGrp = "Retail"
Case 22
RetGrp = "Retail"
Case 25
RetGrp = "Retail"
Case 12 To 21
RetGrp = "Commercial"
Case 23 To 24
RetGrp = "Commercial"
Case Else
RetGrp = "Not Valid"
End Select

End Function

If [Class] can ever be a null value then you need to modify your SQL like so...

TRANSFORM Sum([nett]-[gst]) AS Expr2
SELECT RetGrp(nz([class],0) AS Expr1
FROM SCEX
WHERE (((SCEX.INV_DATE) Between #12/1/2002# And #12/31/2002#) AND ((RetGrp([class]))="retail&quot)
GROUP BY RetGrp([class])
PIVOT Format([Inv_Date],"mmm-yy"


Good Luck
ssecca

 

[ssecca]

Sorry left out a pren.

TRANSFORM Sum([nett]-[gst]) AS Expr2
SELECT RetGrp(nz([class],0)) AS Expr1
FROM SCEX
WHERE (((SCEX.INV_DATE) Between #12/1/2002# And #12/31/2002#) AND ((RetGrp([class]))="retail&quot)
GROUP BY RetGrp([class])
PIVOT Format([Inv_Date],"mmm-yy"

ssecca

 

[errolf]

Thanks for you reply, I tried your suggestion but still get the same result when running the function and SQL, "Have you any further suggestions"?

Errolf