مشکل در جستجوی پیشرفته

[Mr.Unknown]

سلام دوستان بنده نرم افزار املاک نوشتم یک مشکلی در جستجوی پیشرفته برنامه دارم.به تصویر نگاه کنید تا متوجه شید منظورم چیه.من میخوام زمانی که هر کدام از تکست ها خالی باشه بر اساس تکست ها دیگه توی بانک جستجو کنه از این کد استفاده کردم ولی از if زیاد استفاده شده لطف کنید راهنمایی کنید راه دیگه ای هم وجود داره یا خیر؟؟!.

Dim S1, S2, S3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20 As Integer
S1 = 0: S2 = 0: S3 = 0: s4 = 0: s5 = 0: s6 = 0: s7 = 0: s8 = 0: s9 = 0: s10 = 0: s11 = 0: s12 = 0
If Ts1.Text <> "" And Ts2.Text <> "" Then S1 = 1 Else S1 = 0
If Tz1.Text <> "" And Tz2.Text <> "" Then S2 = 1 Else S2 = 0
If Tt1.Text <> "" And Tt2.Text <> "" Then s4 = 1 Else s4 = 0
If Tkh1.Text <> "" And Tkh2.Text <> "" Then s5 = 1 Else s5 = 0
If Tgh1.Text <> "" And Tgh2.Text <> "" Then s6 = 1 Else s6 = 0
If Cj.Text <> "" Then s7 = 1 Else s7 = 0
If Cv.Text <> "" Then s8 = 1 Else s8 = 0
If Ts1.Text <> "" And Ts2.Text <> "" And Tz1.Text <> "" And Tz2.Text <> "" Then S3 = 1 Else S3 = 0
If Ts1.Text <> "" And Ts2.Text <> "" And Tt1.Text <> "" And Tt2.Text <> "" Then s9 = 1 Else s9 = 0
If Ts1.Text <> "" And Ts2.Text <> "" And Tkh1.Text <> "" And Tkh2.Text <> "" Then s10 = 1 Else s10 = 0
If Ts1.Text <> "" And Ts2.Text <> "" And Tgh1.Text <> "" And Tgh2.Text <> "" Then s11 = 1 Else s11 = 0
If Ts1.Text <> "" And Ts2.Text <> "" And Cj.Text <> "" Then s12 = 1 Else s12 = 0
If Ts1.Text <> "" And Ts2.Text <> "" And Cv.Text <> "" Then s13 = 1 Else s12 = 0
If Tz1.Text <> "" And Tz2.Text <> "" And Tt1.Text <> "" And Tt2.Text <> "" Then s14 = 1 Else s14 = 0
If Tz1.Text <> "" And Tz2.Text <> "" And Tkh1.Text <> "" And Tkh2.Text <> "" Then s15 = 1 Else s15 = 0
If Tz1.Text <> "" And Tz2.Text <> "" And Tgh1.Text <> "" And Tgh2.Text <> "" Then s16 = 1 Else s16 = 0
If Tz1.Text <> "" And Tz2.Text <> "" And Cj.Text <> "" Then s17 = 1 Else s17 = 0
If Tz1.Text <> "" And Tz2.Text <> "" And Cv.Text <> "" Then s18 = 1 Else s18 = 0
If Tt1.Text <> "" And Tt2.Text <> "" And Tkh1.Text <> "" And Tkh2.Text <> "" Then s19 = 1 Else s19 = 0
If Tt1.Text <> "" And Tt2.Text <> "" And Tgh1.Text <> "" And Tgh2.Text <> "" Then s20 = 1 Else s20 = 0

If S1 = 1 And S2 = 1 Then S1 = 0: S2 = 0
If S1 = 1 And s4 = 1 Then S1 = 0: s4 = 0
If S1 = 1 And s5 = 1 Then S1 = 0: s5 = 0
If S1 = 1 And s6 = 1 Then S1 = 0: s6 = 0
If S1 = 1 And s7 = 1 Then S1 = 0: s7 = 0
If S1 = 1 And s8 = 1 Then S1 = 0: s8 = 0
If S2 = 1 And s4 = 1 Then S2 = 0: s4 = 0
If S2 = 1 And s5 = 1 Then S2 = 0: s5 = 0
If S2 = 1 And s6 = 1 Then S2 = 0: s6 = 0
If S2 = 1 And s7 = 1 Then S2 = 0: s7 = 0
If S2 = 1 And s8 = 1 Then S2 = 0: s8 = 0
If s4 = 1 And s5 = 1 Then s4 = 0: s5 = 0
If s4 = 1 And s6 = 1 Then s4 = 0: s6 = 0
If S1 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Age between '" & Ts1.Text & "' and '" & Ts2.Text & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If S2 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Zirbana between '" & Tz1.Text & "' and '" & Tz2.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If S3 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Zirbana > '" & Tz1.Text & "' and zirbana<= '" & Tz2.Text & "' and age> '" & Ts1.Text & "' and age<= '" & Ts2.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s4 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Tabaghe between '" & Tt1.Text & "' and '" & Tt2.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s5 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Bed between '" & Tkh1.Text & "' and '" & Tkh2.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s6 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Price between '" & Val(Vall(Tgh1.Text)) & "' and '" & Val(Vall(Tgh2.Text)) & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s7 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where PropertyD = '" & Cj.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s8 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where VahedD = '" & Cv.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s9 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Age > '" & Ts1.Text & "' and Age<='" & Ts2.Text & "' and Tabaghe > '" & Tt1.Text & "' and Tabaghe <='" & Tt2.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s10 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Age > '" & Ts1.Text & "' and Age<='" & Ts2.Text & "' and Bed > '" & Tkh1.Text & "' and Bed <='" & Tkh2.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s11 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Age > '" & Ts1.Text & "' and Age<='" & Ts2.Text & "' and Price >'" & Val(Vall(Tgh1.Text)) & "' and Price <= '" & Val(Vall(Tgh2.Text)) & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s12 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Age > '" & Ts1.Text & "' and Age<='" & Ts2.Text & "' and PropertyD ='" & Cj.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s13 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Age >= '" & Ts1.Text & "' and Age<='" & Ts2.Text & "' and VahedD ='" & Cv.Text & "'and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s14 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Zirbana >= '" & Tz1.Text & "' and Zirbana<='" & Tz2.Text & "' and Tabaghe >='" & Tt1.Text & "' and Tabaghe<='" & Tt2.Text & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s15 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Zirbana >= '" & Tz1.Text & "' and Zirbana<='" & Tz2.Text & "' and Bed >='" & Tkh1.Text & "' and Bed<='" & Tkh2.Text & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s16 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Zirbana >= '" & Tz1.Text & "' and Zirbana<='" & Tz2.Text & "' and Price >='" & Val(Vall(Tgh1.Text)) & "' and Price<='" & Val(Vall(Tgh2.Text)) & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s17 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Zirbana >= '" & Tz1.Text & "' and Zirbana<='" & Tz2.Text & "' and PropertyD='" & Cj.Text & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s18 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Zirbana >= '" & Tz1.Text & "' and Zirbana<='" & Tz2.Text & "' and VahedD='" & Cv.Text & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s19 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Tabaghe >= '" & Tt1.Text & "' and Tabaghe<='" & Tt2.Text & "' and Bed>='" & Tkh1.Text & "' and Bed<='" & Tkh2.Text & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If
If s20 = 1 Then
If RsSearch.State = 1 Then RsSearch.Close
RsSearch.CursorLocation = adUseClient
RsSearch.Open "Select Type,PId,Zirbana,Address,Tabaghe,Age,Kafpoosh,Cabi net,Price from Apartment where Tabaghe >= '" & Tt1.Text & "' and Tabaghe<='" & Tt2.Text & "' and Price >='" & Val(Vall(Tgh1.Text)) & "' and Price<='" & Val(Vall(Tgh2.Text)) & "' and Type='" & CTy.Text & "'", CN, adOpenStatic, adLockOptimistic
Set Vg.DataSource = RsSearch
CountRec
End If

[M.T.P]

بله امکانش هست.
می تونی تمامی کدهای بالا رو تو یک خط دستور SQL خلاصه کنی...
به دستور SQL زیر نگاه کن.


STR_SQL = "Select * From Customers Where " & _
"Acc Like '" & TxtAcc.Text & "%' Or " & _
"FName Like '" & TxtFName.Text & "%' Or " & _
"LName Like '" & TxtLName.Text & "%'"

البته مطابق نام جدول و فیلدها و تکس باکس هات تغییرات لازم رو انجام بده.

[Mr.Unknown]

به عنوان مثال توی تصویر بالا توجه کنید من اگر زیر بنا رو وارد کردم در رکوردها وجود داشت نمایش بده ولی اگر سن بنا و جهت ملکی وارد شد یکی از آنها در رکورد موجود نبود هیچ رکوردی نشون نشده این دستوری که شما دادین فقط 1 فیلد رو به سرچ میکنه
1 مثال دیگه.اگر زیر بنا و تعداد خواب رو وارد کردم زیر بنا در رکورد بود اما تعداد خواب در رکورد نبود هیچ رکوردی نشون نده
من دستور and رو برای همین وارد کردم که چند تا آیتمی که توی تکست ها قرار میدم اگر در رکورد ها وجود داشته باشه رو نمایش بده اگر یکی هم وجود نداشت هیچی نشون نده

[M.T.P]

جستجویی که من کدش رو گذاشتم جستجوی الاماشااله هست :لبخند: ، یعنی اینکه اگه شما بخوای دنبال واژه کلنگی داخل فیلدها بگردی حرف ک رو که وارد کنی هر رکوردی که یکی از فیلدهاش حرف اولش ک باشه رو برات میریزه وسط ، اما اگه شما می خواید جستجو دقیق باشه باید علامت = رو جایگزین کلمه Like کنید و همچنین % ها رو هم حذف کنید.
اگه موفق نشدید تا یک نمونه ضمیمه کنم.
امیدوارم به نتیجه برسید. :چشمک:

[Mr.Unknown]

بله لطف کنید یک نمونه ضمیمه کنید.
من هم یک نمومنه میزارم تا کامل متوجه منظورم بشید.لطف کنید کدهاشم نگاه کنید.

[Mr.Unknown]

کجایید لطفا سریعتر راهنمایی کنید