SQL Search Query not returning expected results

Hi Experts!
I am hoping this will be a quick fix.  I only need you to look at the "Search of FEIN field"  if this one will work then I will be able to get the Search ERN field to work.  I have an interface that has multiple checkboxes that will generate a query based on what the user selects.  Everything works fine except for if the user actually types in a federal id number (FEIN) or an employer registration number (ERN).  The data is in the database but nothing is return.  The rest of the query works perfectly!  Does anyone have any suggestions on how to fix my problem.  Both FEIN and ERN are varchar fields however the data is numeric.  It is the same as a social security number.  

Please let me know if I need to be a bit clearer on what I need.

Thanks
Miracle By Design
alter Procedure [dbo].[usp_Sel_ERSSearch]
(
    @Criteria varchar(1000),
	@SearchValue1 int = null, --FEIN checkbox value
	@SearchValue2 int = null, --ERN checkbox value
	@SearchValue3 int = null, --Legal Name checkbox value
	@SearchValue4 int = null, --DBA Name checkbox value
	@SearchValue5 int = null, --Owner/Officer checkbox value
	@SearchValue6 int = null  --Address checkbox value
)
AS
 
 SET NOCOUNT ON
 Begin	
	  Begin Transaction
		--Search of FEIN field
		If @SearchValue1 = 1 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue5 = 0 and @SearchValue6 = 0 
		Begin
			
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (FEIN = @Criteria and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (FEIN = @Criteria and a.Confirmation is not null)
			
				Order By FEIN
 
		End
		--Search on ERN field
		If @SearchValue2 = 1 and @SearchValue1 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue5 = 0 and @SearchValue6 = 0
		Begin
 
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (ERN = @Criteria and a.JTAppID is not null)
			
				
			Union all
 
				Select  Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (ERN = @Criteria and a.Confirmation is not null)
			
				Order By FEIN
 
		End
		--Search on Legal Name, DBA Name, Owner/Officer, Address fields
		If @SearchValue3 = 1 and @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on Legal Name, DBA Name, Owner/Officer fields
		If @SearchValue3 = 1 and @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on Legal Name, DBA Name fields
		If @SearchValue3 = 1 and @SearchValue4 = 1 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				 Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or DBAName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or DBAName Like '%'+@Criteria+'%'and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on Legal Name field
		If @SearchValue3 = 1 and @SearchValue4 = 0 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End 
		--Search on Legal Name, Owner/Officer fields
		If @SearchValue3 = 1 and @SearchValue5 = 1 and @SearchValue4 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( LegalName Like '%'+@Criteria+'%'
					   Or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on Legal Name, Address fields
		If @SearchValue3 = 1 and @SearchValue6 = 1 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or Address Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (LegalName Like '%'+@Criteria+'%'
					   Or Address Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on DBA Name, Owner/Officer, Address fields
		If @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on DBA Name, Owner/Officer fields
		If @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( DBAName Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( DBAName Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
 
		End
		--Search on DBA Name, Address fields
		If @SearchValue4 = 1 and @SearchValue6 = 1 and @SearchValue5 = 0 and @SearchValue3 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on DBA Name field
		If @SearchValue4 = 1 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue3 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (DBAName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (DBAName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
	
		--Search on Owner/Officer, Address fields
		If @SearchValue5 = 1 and @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
		End
		--Search on Owner/Officer field
		If @SearchValue5 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue6 = 0
		Begin
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE ( OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
 
		End
		--Search on Address field
		If @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue5 = 0 
		Begin
 
				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (Address Like '%'+@Criteria+'%' and a.JTAppID is not null)
			
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
				WHERE (Address Like '%'+@Criteria+'%' and a.Confirmation is not null)
			
				Order By FEIN
 
 
		End
		
		
	Commit Transaction

Open in new window

MiracleByDesignAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

k_rasuriCommented:
did u check the FEIN query directly providing the values...by replacing @Criteria with the actual value. do you see the results coming up.

Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN,
                        LegalName, DbaName, Address,
                        OFullName, assn.AssignmentStatusDsc, a.AssignmentType,
                        u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
                        Join Assignment a on a.JtAppID = es.JtAppID
                        Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
                        Left Join AdminUsers u on u.UserID = a.UserID
                        WHERE (FEIN = @Criteria and a.JTAppID is not null)
0
MiracleByDesignAuthor Commented:
Yes, I did replace the FEIN with the @Criteria and I still could not pull up the results I needed.  Thanks for the suggestion!
0
miketobCommented:
Hello,
Only a suggestion for improved readability (but certainly not as fast as your method):
I'd use something like:
SET @QryBegining='SELECT fields JOINS etc etc without the where clause"
and then building another string given your "SearchValue" parameters
SET @WhereClause= 'WHERE FEIN = ' + @Criteria + ' OR ERN='+@nCriteria

And at the end execute it  with:
EXEC (@QryBegining+@WhereClause)

Perhaps might help you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MiracleByDesignAuthor Commented:
I had your suggestion for readability in the beginning but since I am querying two separate tables it was a little too busy of a query and this must be able to be supported by junior level developers.  For some reason the only issue is with finding a match for the FEIN or ERN.  Thanks!
0
TheLastStrawCommented:
If your data is actually numeric why don't you try

WHERE (FEIN = CAST(@Criteria AS Integer) ...

I asume your checkbox also equates to 1 as commonly  -1 '@SearchValue1 = -1 ?
0
MiracleByDesignAuthor Commented:
My data is numeric however there is a chance the data was saved with dashes or was imported from an outside source with dashes. Example FEIN = 11-111111 or 555-55-5555.  I have to allow for searching with or without dashes.  The @SearchValues just pass either a 1 or 0 to determine if the box has been checked on not on the search page.  The real problem is just in the where clause for FEIN or ERN.  Thanks for the suggestion.  If possible.  I will need to get this question resolved by Tuesday.  Thanks!
0
MiracleByDesignAuthor Commented:
Does anyone else have any ideas why this is not working.  I hate deleting questions but I really need assistance on this by the end of business on Tuesday.
0
miketobCommented:
Based on the suggestion that k_rasuri gave you, and that you serched for the FEIN values with a simpler query and didn't return the values you looked for, I assume that you're probably having "collation" issues, try this:

WHERE FEIN collate database_default = @Criteria collate database_default

and use it on every varchar field in your where clause
Hope it helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.