Help with Type MisMatch Error

[penndro]

I could use some help trying to figure out why I am getting a type mismatch error.
My code:

Public Function GetHit(Digi As Variant, Draw As Variant)
Dim B1 As Variant
Dim B2 As Variant
Dim B3 As Variant
Dim Var1 As Variant
Dim Var2 As Variant
Dim Var3 As Variant
Dim Var4 As Variant
Dim Var5 As Variant
Dim Var6 As Variant

Set B1 = (Left(Draw, 1))
Set B2 = (Left(Right(Draw, 2), 1))
Set B3 = (Right(Draw, 3))


Set Var1 = Trim([B1] & [B2] & [B3])
Set Var2 = Trim([B1] & [B3] & [B2])
Set Var3 = Trim([B2] & [B1] & [B3])
Set Var4 = Trim([B2] & [B3] & [B1])
Set Var5 = Trim([B3] & [B2] & [B1])
Set Var6 = Trim([B3] & [B1] & [B2])

Select Case [Digi]
Case [Var1], [Var2], [Var3], [Var4], [Var5], [Var6]
[GetHit] = "HIT"
Case Else
[GetHit] = ""
End Select

End Function

Althought I try to use TRIM to make the results clearly text it won't work. I originally compiled all my variables to text but then the code would not compile so I made them all variant type.

Any help would be appreciated.

 

[PHV]

Get rid of the Set instruction:

Public Function GetHit(Digi As Variant, Draw As Variant)
Dim B1 As Variant
Dim B2 As Variant
Dim B3 As Variant
Dim Var1 As Variant
Dim Var2 As Variant
Dim Var3 As Variant
Dim Var4 As Variant
Dim Var5 As Variant
Dim Var6 As Variant

B1 = Left(Draw, 1)
B2 = Left(Right(Draw, 2), 1)
B3 = Right(Draw, 3)

Var1 = Trim(B1 & B2 & B3)
Var2 = Trim(B1 & B3 & B2)
Var3 = Trim(B2 & B1 & B3)
Var4 = Trim(B2 & B3 & B1)
Var5 = Trim(B3 & B2 & B1)
Var6 = Trim(B3 & B1 & B2)

Select Case Digi
Case Var1, Var2, Var3, Var4, Var5, Var6
    GetHit = "HIT"
Case Else
    GetHit = ""
End Select
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[penndro]

Thank you PHV. I changed that and also realised I could use the String variable type. The code complies fine but does not give the correct result. For instance when I check it in the immediate window I get:



[highlight #FCE94F]?GETHITS(123,012,321,213,231)[/highlight] [highlight #FCE94F]0[/highlight]

Here is the updated code:


Option Compare Database
Public Function GetHits(Digi As String, Draw As String, Draw2 As String, Draw3 As String, Draw4 As String)
Dim B1 As String
Dim B2 As String
Dim B3 As String
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
Dim Var4 As String
Dim Var5 As String
Dim Var6 As String


B1 = (Left(Draw, 1))
B2 = (Left(Right(Draw, 2), 1))
B3 = (Right(Draw, 3))

B4 = (Left(Draw2, 1))
B5 = (Left(Right(Draw2, 2), 1))
B6 = (Right(Draw2, 3))

B7 = (Left(Draw3, 1))
B8 = (Left(Right(Draw3, 2), 1))
B9 = (Right(Draw3, 3))

B10 = (Left(Draw4, 1))
B11 = (Left(Right(Draw4, 2), 1))
B12 = (Right(Draw4, 3))


Var1 = Trim([B1] & [B2] & [B3])
Var2 = Trim([B1] & [B3] & [B2])
Var3 = Trim([B2] & [B1] & [B3])
Var4 = Trim([B2] & [B3] & [B1])
Var5 = Trim([B3] & [B2] & [B1])
Var6 = Trim([B3] & [B1] & [B2])

Var7 = Trim([B4] & [B5] & [B6])
Var8 = Trim([B4] & [B6] & [B5])
Var9 = Trim([B5] & [B4] & [B6])
Var10 = Trim([B5] & [B6] & [B4])
Var11 = Trim([B6] & [B5] & [B4])
Var12 = Trim([B6] & [B4] & [B5])

Var13 = Trim([B7] & [B8] & [B9])
Var14 = Trim([B7] & [B9] & [B8])
Var15 = Trim([B8] & [B7] & [B9])
Var16 = Trim([B8] & [B9] & [B7])
Var17 = Trim([B9] & [B8] & [B7])
Var18 = Trim([B9] & [B7] & [B8])

Var19 = Trim([B10] & [B11] & [B12])
Var20 = Trim([B10] & [B12] & [B11])
Var21 = Trim([B11] & [B10] & [B12])
Var22 = Trim([B11] & [B12] & [B10])
Var23 = Trim([B12] & [B11] & [B10])
Var24 = Trim([B12] & [B10] & [B11])

Select Case [Digi]
Case Var1, Var7, Var13, Var19
[GetHits] = "STR HIT"
Case Var2, Var8, Var14, Var20
[GetHits] = "BX 1"
Case Var3, Var9, Var15, Var21
[GetHits] = "BX 2"
Case Var4, Var10, Var16, Var22
[GetHits] = "BX 3"
Case Var5, Var11, Var17, Var23
[GetHits] = "BX 4"
Case Var6, Var12, Var18, Var24
[GetHits] = "BX 5"
Case Else
[GetHits] = 0
End Select

End Function

 

[PHV]

All your VarX are string of 5 characters.
If the expected result was "BX 2" then replace this:
B3 = (Right(Draw, 3))
with this:
B3 = (Right(Draw, 1))
and so on.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886