Devide ur code into 2 parts here is an example of my code
Public Function getQuery( ByVal objPatientEnquiryObjects As PatientEnquiryObjects) As String
sqlstr = "SELECT DISTINCT IPAIPNO_REG,IPAIPNO,REGPATNAME ||REGPATLASTNAME as PATNAME,REGHEADOFFAMILYNAME, T3.REGHNO, T3.REGSTREET,T3.REGCITY, BEDTRNOCCUSEX,BEDTRNPATAGE,IPADUMMY1,IPAADMDATE,IPAADMTIME, "
sqlstr = sqlstr & "WRDDESC,BEDTRNRMCODE,BEDTRNBEDNO,IPAPAYTYPE,IPASPCLINSTRU,IPAPOLCOMPCODE,IPAOPRID,' ' From TBL_IPA_TN_IPADMS T1,TBL_IPA_TN_BEDTRN T2, TBL_REG_TN_NEW T3,TBL_ADM_MS_DOCTOR T4,TBL_IPA_MS_WARD T5"
sqlstr = sqlstr & " WHERE T1.IPAIPNO_REG=T2.BEDTRNIPNO_REG AND T1.IPAIPNO_REG=T3.REGNO AND T1.IPAIPNO=T2.BEDTRNIPNO AND BEDTRNOCCBYPAT='Y' AND BEDTRNSTATUS='O' And T4.DOCCODE=T2.BEDTRNDOCCODE"
sqlstr = sqlstr & " AND T2.BEDTRNWRDCODE=T5.WRDCODE AND IPAIPNO IN (SELECT MAX(IPAIPNO) FROM TBL_IPA_TN_IPADMS GROUP BY IPAIPNO_REG)"
If objPatientEnquiryObjects.MRNo <> 0 Then
sqlstr = sqlstr & " AND IPAIPNO_REG LIKE '" & objPatientEnquiryObjects.MRNo & "%'"
End If
If objPatientEnquiryObjects.ipno <> 0 Then
sqlstr = sqlstr & " AND IPAIPNO LIKE '" & objPatientEnquiryObjects++.ipno & "%'"
End If
If objPatientEnquiryObjects.patName <> Nothing Then
sqlstr = sqlstr & " AND REGPATNAME LIKE '%" & objPatientEnquiryObjects.patName & "%'"
End If
If objPatientEnquiryObjects.BillPatType <> Nothing Then
sqlstr = sqlstr & " AND IPAPAYTYPE LIKE '" & objPatientEnquiryObjects.BillPatType & "%'"
End If
If objPatientEnquiryObjects.WARD <> Nothing Then
sqlstr = sqlstr & " AND WRDDESC LIKE '" & objPatientEnquiryObjects.WARD & "%'"
End If
If objPatientEnquiryObjects.room <> Nothing Then
sqlstr = sqlstr & " AND BEDTRNRMCODE LIKE '%" & objPatientEnquiryObjects.room & "%'"
End If
If objPatientEnquiryObjects.doctor <> Nothing Then
sqlstr = sqlstr & " AND IPADUMMY1 LIKE '" & objPatientEnquiryObjects.doctor & "%'"
End If
If objPatientEnquiryObjects.admissionFrom <> 0 Then
sqlstr = sqlstr & " AND IPAADMDATE >= '" & objPatientEnquiryObjects.admissionFrom & "'"
End If
If objPatientEnquiryObjects.admissionTo <> 0 Then
sqlstr = sqlstr & " AND IPAADMDATE <= '" & objPatientEnquiryObjects.admissionTo & "'"
End If
If objPatientEnquiryObjects.village <> Nothing Then
sqlstr = sqlstr & " AND REGSTREET LIKE '%" & objPatientEnquiryObjects.village & "%'"
End If
If objPatientEnquiryObjects.district <> 0 Then
sqlstr = sqlstr & " AND REGCITY LIKE '%" & objPatientEnquiryObjects.district & "%'"
End If
If objPatientEnquiryObjects.gender <> Nothing Then
sqlstr = sqlstr & " AND BEDTRNOCCUSEX LIKE '" & objPatientEnquiryObjects.gender & "%'"
End If
If objPatientEnquiryObjects.company <> Nothing Then
sqlstr = sqlstr & " AND IPAPOLCOMPCODE LIKE '%" & objPatientEnquiryObjects.company & "%'"
End If
sqlstr = sqlstr & " order by IPAIPNO desc"
return sqlstr
End Function
pass this quwry to ur code in place of members