If.. ElseIf... Statement Not working???

[saross]

Hi. HAve constructed the following If statement but I get an 'Expected End Of Statement' compile error on the last ElseIf line:

If IsNull(Me.cmbSearchList) Then
MsgBox "Please make a valid selection", vbOKOnly
Else
If Me.txtSearchType = "Client" Then
intOrgID = Me.cmbSearchList.Value
strSQL = "OrgID = " & intOrgID & ""
ElseIf Me.txtSearchType = "ContractName" Then
lngContractID = Me.cmbSearchList.Value
strSQL = "ContractID = " & lngContractID & ""
ElseIf Me.txtSearchType = "Location" Then
lngContractID = Me.cmbSearchList.Value
strSQL = "ContractID = " & lngContractID & ""
ElseIf Me.txtSearchType = "PM" Then
intStaffID = Me.cmbSearchList.Value
strSQL = "SDPContractManagerID = " & intStaffID & ""
ElseIf Me.txtSearchType = "Staff" Then
intStaffID = Me.cmbSearchList.Value
strSQL = "StaffID = " & intStaffID & ""
ElseIf Me.txtSearchType = "Status" Then
intStatusID = Me.cmbSearchList.Value
strSQL = "ContractStatusID " & intStatusID & ""
ElseIf Me.txtSearchType = "SubCat" Then
intSubCatID = Me.cmbSearchList.Value
strSQL = "SubCatID " & intSubCatID & ""
ElseIf Me.txtSearchType = "ClientType" Then
intTypeID = Me.cmbSearchList.Value
strSQL = "TypeID = " & intTypeID & ""
ElseIf Me.txtSearchType - "Workstream" Then
intWorkstreamID = Me.cmbSearchList.Value
*** strSQL = "WorkstreamID = " & intWorkstreamID "" *** ERROR HERE
End If
End If

Can anyone explain to me why I get this error?

 

[bjackson]

could be the last elseif has a minus
ElseIf Me.txtSearchType - "Workstream" Then

ElseIf Me.txtSearchType = "Workstream" Then

 

[saross]

THanks... didn't spot that.

 

[Mile-O]

Urgh! Use the SELECT CASE structure instead.

Select Case Me.txtSearchType
    Case Is = "Client"
        ' do something
    Case Is = "ContactName"
        ' do something
    Case Else
        ' catch all
End Select

 

[saross]

ahhh... i spotted somewhere in a book or help file this but have never seen/used it before. I'll give it a go, thanks.

 

[bjackson]

Couldnt agree more, I almost always use select case,i think its a lot easier to follow and add to
Bjackson