Select Case Statement Help!

[serino]

I have one option group on my form and I would like to add another. The form is based of a query. I want the user to be able to select either "Open", "Closed" or view "Both" from the first option group. The second option group the user would select either "Warranty", "Billable" or "Both".

The first option group worked just fine, but then I decided to add a second option group "FraInType" and thats where I ran into problems.

What am I doing wrong here.

[blue]
Private Function BuildWhereString() As String
Dim strWhere As String
Dim varItemSel As Variant

On Error Resume Next

strWhere = ""

Select Case Me.fraInStatus.Value
Case 1 '
strWhere = strWhere & "Status = 'Open'"
Case 2 '
strWhere = strWhere & "Status<>'Open'"
Case Else '
End Select

End With

Select Case Me.fraInType.Value
Case 1
strWhere = strWhere & "Warranty1 = 'Warranty'"
Case 2
strWhere = strWhere & "Warranty1<>'Warranty'"
Case Else

BuildWhereString = strWhere
Exit Function
End Function[/blue]

 

[HarleyQuinn]

You seem to have an End With that you don't need and you'll need to add an END SELECT for your second case statement (just above BuildWhereString = strWhere)

It may also be worth noting that you might want to add an and to the where clause in the second select e.g.

strWhere = strWhere & "and Warranty1 = 'Warranty'"

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[traingamer]

And is your string (strWhere) ending up as something like:

Status = 'Open'Warranty1<>'Warranty'

when you expected:

Status = 'Open'[b] and [/b]Warranty1<>'Warranty'

?

You need to consider inserting ' and ' if needed.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[serino]

Thank You both for your replies. I added the End select and took out the End with.

Where do I insert "and" ?

 

[serino]

I have inserted "And" statement below and I am not sure this is right. The option group is still not filtering records from either option group frames.

Any Ideas?

[blue]
Private Function BuildWhereString() As String
Dim strWhere As String
Dim varItemSel As Variant

On Error Resume Next

strWhere = ""

Select Case Me.fraInServ.Value
Case 1
strWhere = strWhere & "Status = 'Open'And"
Case 2
strWhere = strWhere & "Status<>'Open'And"
Case Else
End Select


Select Case Me.fraInServ1.Value
Case 1
strWhere = strWhere & "Warranty1 = 'Warranty'And"
' Case 2
' strWhere = strWhere & "Warranty1<>'Warranty'And"
Case Else
End Select
BuildWhereString = strWhere
Exit Function
End Function[/blue]

 

[traingamer]

You're on the right track. Be sure to put spaces before and after the And.

Then finish with something like

Case Else
End Select
if right(strWhere, 5) = " And " then
   strWhere = Left(strWhere, 1, Len(strWhere) - 5) ' to remove the trailing " And "
end if

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill