زبان Sql در فیلتر کردن رکوردها ( کوئری )

اشعار تشییع جنازه اسماعیل هنیه در دانشگاه تهران  مرداد ۱۴۰۳

مرگ بر سه ابلیس    آمریکا اسرائیل و انگلیس

مروری بر نوشته های قدیمی !!!

برای درک نوشتاری کد SQL باید کارکرد عملگرهای منطقی را بدانید . با جستجوی ساده می توانید این کار را انجام دهید .

با رفتن به وب سایت W3School می توانید Sql نویسی انجام دهید 

در تصویر زیر جدول Orders فیلد OrderID آن از شماره 10248 تا 10443 پر شده

از Val به این علت استفاده شده چون دیتا تایپ فیلد OrderID عددی است ( int )

Dim MyValue
MyValue = Val("2457")    ' Returns 2457.
MyValue = Val(" 2 45 7")    ' Returns 2457.
MyValue = Val("24 and 57")    ' Returns 24.

فرض کنید دو  تکست باکس دارید که میخواهید فقط اعدادی بین اینها لیست شوند ، در صورت وجود نداشتن یا Null شدن از فیلتر در بیاید و کل رکوردها نمایش داده شوند یا اگر تکست باکس اول پر شده بود اعداد بالاتر و خودش یا اگر  تکست باکس دوم فقط پر شده بود اعداد پائین تر از خودش فهرست شوند.

عبارت Sql زیر  رکوردهایی از جدول Orders را با تعیین شرطی نمایش می دهد.

w3schools

عبارت زیر را در لینک بالا باکس Sql Statement جایگزین کرده و امتحان نمائید.

Select * from Orders Where (orderID>=Val(10440) or False) And (OrderID<=Val('') or True)

لینک مورد نظر برای کسانیکه به سیستم دسترسی ندارند مناسب است  شما می توانید Sql نویسی را یاد بگیرید .

عبارت Sql زیر را نیز در باکسِ لینک W3Shool قرار دهید . رکوردهایی که دارای OrderID بزرگتر از  10280 بوده و شرط دیگر اینکه CustomerID آن بین 70 و 72 است را لیست میکند

Select * from Orders Where ((orderID>=Val(10280) or False) And (OrderID<=Val('') or True)) And ((CustomerID>=Val(70) Or False) And (CustomerID<=Val(72) Or False))

طبق تصویر زیر 8 رکورد یافت و فهرست شد.

Select * from Orders Where ((orderID>=Val('') or True) And (OrderID<=Val('') or True)) And ((CustomerID>=Val(88) Or False) And (CustomerID<=Val('') Or True))

عبارت بالا نیز تنها رکوردهایی از جدول Orders را نمایش میدهد که دارای CustomerID بالاتر از و خود 80 باشد.

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

در مورد True یا False هایی که در عبارات ذکر شده باید گفت که منظور از آنها در اصطلاح عامیانه خالی بودن یا نبودن تکست باکس ها در فرم است . اگر تکست باکس Null باشد نتیجه تابع ایزنال True و اگر تکست باکس درونش پرشده باشد False است.

با Between نمی توان اینچنین مانورهایی را داشت در نتیجه از عملگر کوچکتر و بزرگتر استفاده کردیم !!!

اگر بخواهید در Query Builder انجام دهید و با Between برای جستجوی بین دو تاریخ : 

فرمی تهیه شده که دارای  دو TextBox برای ورودی تاریخ است  بعد از زدن باتن Show Orders کوئری نمایش داده خواهد شد 

در کوئری ورودی زیر در ردیف criteria برای فیلد OrderDate ذکر می شود : 

آدرس دهی کرده که تکست باکس ها با نام RptStart و RptEnd در فرم frmRepirt_DateRange وجود دارد. عبارت زیر در قسمت Criteria فیلد OrderDate قرار داده شده است.

Between [Forms]![frmReport_DateRange].[RptStart] And [Forms]![frmReport_DateRange].[RptEnd]

کوئری همچنین خالی بودن باکس ها را بررسی میکند ( IsNull ) ، حتی با خالی بودن یک باکس کوئری تمام رکوردها را نمایش می دهد. یا از فیلتر تاریخ در می آید.

همانطور که در تصویر می بینید  آدرس دهی تکست باکس در فرم مربوطه ( فرم باز ) در سر ستون قرار داده شده و در قسمت Or ( پائین Criteria ) و زیر همان ستون تابع IsNull نوشته شده است.

در همین قسمت Query Design می توانید به Sql آن هم دسترسی داشته باشید که متوجه شوید ساختار نوشتاری را اکسس چه داده که یاد بگیرید. ولی عملگرهای منطقی را حتما یاد بگیرید هم And هم Or و هم Null

اگر شرطی را زیر هم بنویسید ( در یک Row نباشد )  به معنای  Or است  و اگر در یک ردیف بنویسید در زبان Sql به معنای And در نظر گرفته می شود و اگر حتی یک کدام True یا موجود باشد کوئری خالی نمایش داده می شود.چون نتیجه False خواهد بود در عملگر And 

Form.Requery Method

The Requery method updates the data underlying a specified form by requerying the source of data for the form.

فیلتر در اکسس

در تصویر زیر سه Toggle Button در OptionGroup با نام Frame5 قرار گرفته و ولیوی آنهای به ترتیب 0 تا 2 است با کلیک روی اولین باتن ،  تمام رکوردهایی که فیلد chk آنها تیک خورده یا نخورده در سابفرم آورده میشود Forms!Form1!Frame5=0 ، کلیک روی باتن دوم رکوردهایی که فیلد chk آنها غیر صفر است در سابفرم لیست میشود و باتن آخر هم لیست رکوردهایی  است که فیلد chk آنها صفر یا تیک ندارند 

Select-Query-Access-

filename=trysql_create_table

در تصویر زیر فرمی Simulate شده که سه کنترل CheckBox برای فیلتر کردن گرید A تا B ( فیلد Grade ) و دو کنترل CheckBox دیگر برای فیلتر کردن تیک خورده ها یا نخورده ها ( فیلد chk )، دارد چنانچه هیچکدام از ۵ چک باکس تیک نخورده باشند کل داده نمایان خواهد شد.( تعداد کل ورودی در تصویر ۹ رکورد بوده )

سمت راست تصویر چهار رکورد نمایش داده شده دقیقا تیک چک باکس گرید A و تیک چک باکس ( Unchecked مربوط به فیلد chk ) در فرم زده شده ...  طبق عبارت Sql کوئری شامل  رکوردهایی با گرید A که تیک  Chk آنها نخورده باشد.

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

?'A' AND FALSE=FALSE

?'A' AND TRUE=NULL

'A' OR TRUE=TRUE

'A' OR FALSE=0

'A' OR TRUE=1

FALSE OR TRUE=TRUE

TRUE OR FALSE=TRUE

TRUE AND FALSE=FALSE

FALSE AND FALSE=FALSE

FALSE OR FALSE=FALSE

NULL AND FALSE=FALSE

NULL AND TRUE=NULL

NULL AND NULL=NULL

برای ارسال داده ها به اکسل نام کوئری را در دستور DOCMD.OUTPUTTO قرار می دهید.

Method vba/api/access.docmd.outputto

در تصویر زیر ، تصویر اول تمام رکوردهای جدول t نمایش داده شده شامل ۹ رکورد ایجاد شده با INSERT INTO ، تصویر سوم که فیلد DATE دارد شامل رکوردهایی است که کوچکت مساوی یک تاریخ خاص و نیز کوچکتر مساوی عدد 105 است ، تصویر چهارم یا آخر در تصویر زیر ، از رکوردهای داخل تصویر سوم آنهایی که تیک چک فیلد CHK آنها زده شده ( یا باصطلاح غیر صفر است ) را نمایش میدهد که شامل دو رکورد است .

در لینک زیر روش بیان شده ، البته هزینه بر است  (کلا در چند خط خیلی کوتاه و شامل 4 تصویر که بعد از اعمال کد SQL جدول فیلترشده) . برای ارسال داکیومنت آن درخواست دهید و بعد از واریز مبلغ ده هزار تومان به ایمیل شخصی ارسال خواهدشد.

Multi Filter  Select-Query-Access-

در تصویر زیر ، رکوردهایی که نام محصول به les ختم می شود لیست شده البته در SQL بجای علامت * در اکسس از % استفاده شده.اگر در اکسس عبارت جستجو بین دو * قرار بگیرد تمام رکوردهایی که نام محصول شامل آن عبارت است را می آورد چه آخر باشد چه هر جای دیگر رشته در فیلد نام محصول.

MULTIFILTER IN ACCESS : 

در تصویر زیر اینطور تصور شده که فرمی با دو چک باکس برای فیلتر کردن فیلد Available و یک کنترل تکست باکس برای فیلتر کردن محتویات فیلد GradeNo موجود است.

در تصویر در صورت تیک داشتن یا نداشتن جفت تکست باکس کل رکوردها که 11 رکورد با Insert Into ایجاد شده در سابفرم نمایش داده میشود. در صورت تیک چک باکس اول رکوردهای دارای  مقدار صفر یا False فیلد Available و تیک چک باکس دوم مقادیر غیر صفر یا True لیست میشوند . البته سابفرم باید Requery شود تا نتیجه فیلترشدن را ببینید.

برای اینکه در سابفرم تمام رکوردهای False و True فیلد Available نمایش داده شود می بایست  : 

Available=False  Or Available=True

حال اگر قرار باشد فیلد Available در کوئری بیلدر مقادیر ولیوی چک باکس ها در فرم را بگیرد میشود :( در صورتیکه جفت چک باکس ها تیک نخورده باشند = False )

Available=False  Or Available=False

و در اینصورت فقط  مقادیر صفر  یا False ها نمایش داده میشود ولی ما می خواهیم در صورت تیک نداشتن دو چک باکس و داشتن ولیوی صفر دو مقدار False  و True را مشاهده بنمائیم.پس باید یکی از طرفین مخاف False  شود.

FALSE OR  <>FALSE=TRUE   نمایش همه

TRUE  OR   <>FALSE = هاTRUE نمایش 

FALSE OR  <>TRUE= ها FALSE نمایش

TRUE  OR  <>TRUE=TRUE نمایش همه

در تصویر زیر دو تکست باکس تصور شده که در صورت تایپ عدد در این دو باکس اعدادی بین این دو که در جدول باشد نمایش داده میشود ، در صورتیکه یک باکس یا هر دو خالی بود از فیلتر در می آید . البته بدین شکل که اگر باکس دوم خالی باشد همه ی رکوردها و اگر باکس اول خالی باشد رکوردها تا مقدار باکس دوم مشاهده میشود در این دو تکست باکس چک میشود که اگر PartNo  در جدول نباشد ارور بدهد در رویداد LOST FOCUS می توان با DlookUp چک کرد اگر نبود  پراپرتی OldValue  مقدار Value  تکست باکسی شود که در حال Exit از آن هستید از رویداد Exit  هم میشود بهره برد.

حال در تصویر زیر  تکست باکس اول در فرم Null و تکست باکس دوم 1003 است ( جدول کلا 11 رکورد دارد )

(GradeNo>='' Or True) And (GradeNo<='1003' Or False) 

عبارت بالا برای زمانی نیست که در دو تکست باکس اعدادی تایپ شود که در جدول وجود ندارد یا حتی در آنها کاراکتر غیر عددی وارد شود اگر صحت موجود بودن مقادیر در جدول چک نشود این عبارت False را بر می گرداند و هیچ رکوردی نمایش داده نخواهد شد.

(GradeNo='123' Or False)  AND (GradeNo='896' Or False) 

False And False=False  ' No Record

بررسی عبارات زیر : 

(GradeNo Between '458' And  '678' Or False Or False)

458 , 678 Not Exist in Above Table

FALSE AND FALSE OR FALSE OR  FALSE=FALSE

پس چون نتیجه FALSE است رکوردی نمایش داده نخواهدشد.

(GradeNo Between Null And  '678' Or True Or False)

678 Not Exist in Above Table

NULL AND FALSE OR TRUE OR  FALSE=TRUE

پس چون نتیجه TRUE است تمام رکوردها نمایش داده خواهدشد.

(GradeNo Between Null And  Null Or True Or  True)

Two Boxes Are Null

NULL AND NULL OR TRUE OR  TRUE=TRUE

پس چون نتیجه TRUE است تمام رکوردها نمایش داده خواهدشد.

برای اینکه مقادیر وارد شده در دو تکست باکس را صحت سنجی نکنیم و در صورت تایپ مقداری خارج از جدول می بایست یک عبارت دیگر به شرط اضافع کرد مثل 

OR (TRUE AND FALSE) 

که در صورت پر بودن تکست باکس ها ،دو TRUE و FALSE داخل پرانتز TRUE شوند و در نتیجه کل رکوردها نمایش داده شوند و در اینجا جستجو کننده پی خواهد برد که یکی از مقادیر تایپ شده یا هر دو در جدول وجود ندارد البته جلوگیری از تایپ عددی یا تاریخی خارج از محتویات فیلد کار خوب و استانداردی است ولی خوب  کوئری نویسی آسان بنظر میرسد ولی می بینید به همین سادگی که گفته شد نیست مگر اینکه عملگرهای منطقی را کاملا فرا بگیرید.

>>>>>> nullguide-oper

 اگر دو تکست باکس  پر باشند ولی در جدول ناموجود می توانید عبارت بالا را اضافه کنید . ( ... BETWEEN....AND...OR...OR)

(NULL(FALSE) AND NULL(FALSE) OR TRUE OR TRUE )OR (TRUE AND TRUE)=TRUE   (NULL OR TRUE=TRUE)

تکست باکس اول NULL است و تکست باکس دوم مقداری که در فیلد GRADENO وجود ندارد یا حاوی کاراکتر غیر عددیست.

(NULL(FALSE) AND FALSE OR FALSE OR TRUE) OR (TRUE AND FALSE)=TRUE

NULL AND FALSE=FALSE

FALSE OR FALSE OR TRUE=TRUE

TRUE OR (TRUE AND FALSE)=TRUE OR FALSE=TRUE

 جستجو بین دو تاریخ : 

دو کنترل تکست باکس به نام های TXTDATEFROM و TXTDATETO . (   سعی کنید دو تا باکس را چک کنید اگر تاریخ اولی از باکس دومی بزرگتر بود خطائی صادر کرده یا باکس را NULL در نظر بگیرد.

BETWEEN TXTDATEFROM AND TXTDATETO OR FORMS!FORM1!TXTDATEFROM IS NULL OR FORMS!FORM1!TXTDATETO IS NULL

فرضا در جدول تاریخ های 1397/01/30 و 1398/02/01 نداریم تکست باکس ها را با این دو رشته پر می کنیم بعد از خروج از تکست باکس ها پراپرتی VALUE میشود مقادیری که گفته شد.چون دو تاریخ در جدول نیست پس برای هر کدام FALSE را بر می گرداند و نتیجه آخر FALSE است و هیچ رکوردی نمایش داده نخواهد شد.

(FALSE AND FALSE OR FALSE OR FALSE) =FALSE OR FALSE=FALSE  ' NO RESULT

اگر تکست باکس اول NULL باشد و تکست باکس دوم دارای رشته ای باشد که داخل جدول نباشد( =FALSE) و بالعکس تمام رکوردها لیست خواهند شد :

(NULL AND FALSE OR TRUE OR FALSE)=FALSE OR TRUE OR FALSE=TRUE 'SHOW ALL RECORDS

تصویر زیر ، خود گویای توضیحات ارائه شده بالا است . جدول شامل کلا ۱۱ رکورد است یکی از تکست باکس ها خالیست و برابر NULL و در دیگری عددی خارج از اعداد جدول ، زمان اجرای کوئری تمام ۱۱ رکورد نمایش داده شده.

دوستان فراگیر ، مطالبی که در این یادداشت ها قرار داده میشود کاملا توضیح داده شده از کپی کاری پرهیز و سعی کنید برای خواندن مطالب وقت گذاشته ، به داکیومنت آفیس که مرجع است مراجعه کنید . لطفا در نظر سنجی هم شرکت کنید ( از انتخاب منو گوشه راست بالا ). بنده برنامه نویس نیستم فقط یادداشت هایی که از سایتهای خارجی استخراج کرده را برای خودم و شما به اشتراک خواهم گذاشت انشاءا... .کلام آخر ، کد آماده به درد تازه واردها نخواهد خورد سعی کنید مطالب را از همین بلاگ یاد بگیرید.

نظرات باز است در زیر همین یادداشت هم می توانید اعلام نظر بفرمائید و نظرسنجی فراموش نشود !!!

Logical Operator

:Table

Orders

OrderID	Payment Type	Ship State/Province
1	Check	IL
2	Check	NY
3	Credit Card	NY
4	Credit CardCredit	IL
5	Check	IL

:Query

Qr_Orders

OrderID	Payment Type	Ship State/Province	expr1
1	Check	IL	-1
2	Check	NY	-1
3	Credit Card	NY	0
4	Credit Card	IL	0
5	Check	IL	-1

expr1: ([orders]![payment type]="check" And [orders]![ship state/province]="ny") Or ([orders]![ship

(" state/province]="il" And Not [orders]![payment type]="credit card

طبق کوئری بالا و عبارت Expr1  رکورد یک :  چون در اینجا بهم ریختگی هنگام نمابش وجود دارد جای نقطه پرانتز بگذارید 

(pay="Check" (True ) And State="ny" (False) Or ..... State="il" (True)  And Not Pay="Credit Card (True

.True And False.   Or .True And True.

False Or True 

True =-1

طبق کوئری بالا و عبارت Expr1  رکورد سه :

(pay="Check" (False ) And State="ny" (True) Or ..... State="il" (False)  And Not Pay="Credit Card (False

.False And True. Or .False And False.

False Or False 

Flase=0

طبق کوئری بالا و عبارت Expr1  رکورد چهار :

(pay="Check" (False ) And State="ny" (False) Or ..... State="il" (True)  And Not Pay="Credit Card (False

.False And False. Or .True And False.

False Or False 

Flase=0

:Query

Qr_Orders

OrderID	Payment Type	Ship State/Province	expr1
1	Check	IL	-1
2	Check	NY	-1
4	Credit CardCredit	IL	-1
5	Check	IL	-1

مثال دوم  :

:Query

Copy Of Qr_Orders

OrderID	Payment Type	Ship State/Province	expr1
1	Check	IL	-1
2	Check	NY	-1
3	Credit Card	NY	0
4	Credit Card	IL	0
5	Check	IL	-1
6	Check	NX	0
7	Check	NZ	0
8	Check	IL	-1

expr1: ([orders]![payment type]="check") And ([orders]![ship state/province]="ny" Or [orders]![ship

(" state/province]="il

طبق Expr1 در گرید بالا : 

رکورد یک

 (Pay="Check" (True) .... And  State="ny" (False) Or State="il" (True 

.True And .False Or True 

True And True

True=-1

رکورد دو

 (Pay="Check" (True) .... And  State="ny" (True) Or State="il" (False 

.True And .True Or False 

True And True

True=-1

رکورد 6 

 (Pay="Check" (True) .... And  State="ny" (False) Or State="il" (False 

.True And .False Or False 

True And False

False=0

:Query

Copy Of Qr_Orders

OrderID	Payment Type	Ship State/Province	expr1
1	Check	IL	0
2	Check	NY	-1
3	Credit Card	NY	-1
4	Credit Card	IL	0
5	Check	IL	0
6	Check	NX	0
7	Check	NZ	0
8	Check	IL	0

expr1: ([orders]![payment type]="check" Or [orders]![payment type]="credit card") And [orders]![ship

("state/province]="ny

رکورد اول 

pay="check" True Or Pay="Credit Card" False.... And State="ny" False

True Or False. And False.

True And False

False=0

رکورد دوم 

pay="check" True Or Pay="Credit Card" False.... And State="ny" True

True Or False. And True.

True And True

True=-1

Copy Of Qr_Orders

OrderID	Payment Type	Ship State/Province	expr1
2	Check	NY	-1
3	Credit Card	NY	-1