کوئری جمع مبالغ رویهم در گروه خاص

موفق و موئد باشید دوستان 

فیلتر در اکسس

در تصویر زیر سه 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 و در دیگری عددی خارج از اعداد جدول ، زمان اجرای کوئری تمام ۱۱ رکورد نمایش داده شده.

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

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

( Select Query ( Access برای مشاهده محتویات نیازمند واریز مبلغ مورد نظر است

این مطلب توسط نویسنده‌اش رمزگذاری شده است و برای مشاهده‌ی آن احتیاج به وارد کردن رمز عبور دارید.

ران کردن کوئری ها یا اجرای عبارات Sql ( با سرعت خوب )

مثل RunSql هشدار نمیدهد ولی سرعت خوبی دارد اگر dbFailOnError استفاده شود در صورت خطا طبق گفته ی داکیومنت در زیر Roll Back میشود .

CurrentDB.Execute

شرط ساده در کوئری

جدول titles : 

title _id        type      price 
-------- --------- -----
T01      history    21.99
T02      history              19.95
T06      biography        19.95
T13      history         29.99
T12      biography      12.9

SELECT title_id, type, price
  FROM titles
  ('WHERE  (type  = 'history
     ('OR  type  = 'biography
     AND price < 20;

در Sql بالا شرط بدین شکل است تنها  رکوردهائی را نمایش میدهد که فیلد type شان history  یا biography  و قیمت شان هم کوچکتر از 20 باشد  و نتیجه : 

title_id type      price
-------- --------- -----
T06      biography  19.95
T02      history          19.95
T12      biography  12.99

فیلتر پیشرفته ( شرط کوئری )

فرض کنید کنترل چک باکسی در فرم ساخته اید به نام Check2 که در فرم اصلی با نام Form3 قرار گرفته میخواهید آن چک باکس را به رکوردسورس فرم وصل کنید به این منظور که هر زمان باتنی با نام فیلتر را فشردید بر حسب تگ آن چک باکس فیلتر در دیتیل  اعمال گردد. 

در تگ چک باکس  مقدار را وارد کنید (  فرض می کنیم میخواهیم فیلد تحصیلات در دیتیل با مقدار چک باکس فیلترشود  پس مقدار تگ را  دیپلم قرار می دهیم )  سپس به دیزاین فرم رفته و در رکورد سورس به کوئری بیلدر می رویم  ( با زدن سه نقطه کنار باکس رکوردسورس) . 

در قسمت Criteria فیلد Education  تایپ میکنیم 

Forms!Form3!Check2.Tag

حتما  در رویداد باتن فیلتر منبع فرم را ریکوئری کنید 

Me.Requery 

زمانیکه فرم باز شود دیتیل فرم طبق مقداری که در  پراپرتی تگ (  Tag ) چک باکس گذاشتید فیلتر میشود و امکان حذف فیلتر را ندارید. (مگر ساخت باتن دیگر و نوشتن Me.FilterOn=False )

اگر بخواهید زمان بازشدن فرم کل رکوردها نمایش داده شود و زمانیکه بر روی باتن فیلتر فشردیدآنوقت فیلترانجام شودباید دریک ستون دیگرفیلدEducation را انتخاب و توتالزرا Where کنید و سپس در کریتریای آن بنویسید 

(iif(Forms!Form3!Check2=True,Forms!Form3!Check2.Tag,False

موردبالا بعد از برداشتن تیک دیتیل را خالی نمایش میدهد که برای اینکار از Is Null آن چک باکس و  false در iff ذکرشده استفاده می کنیم.

فیلتر کردن تاریخ : 

-  دو تکست باکس به نام های Text12 و Text14در فرم دارید و می خواهید رکوردهای بین دو تاریخ درج شده در دو تکست باکس فیلترشود در همان کوئری بیلدر در شرط فیلد تاریخ می نویسید 

Between Forms!Form3!Text12 And 

Forms!Form3!Text14

*** Null معادل هیچ مقداری نیست  جمع مقادیر با Null همان Null میشود پس برای فیلد رکوردی که Null است یا کنترلی که Null است باید صفر در نظرگرفته شود تا جواب جمع Null نشود و ارور دریافت نکنید !!!

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

طبق داکیومنت اکسس بولین مقدار ندارد ولی برای فالز صفر و برای ترو هر عددی غیر از ترو می تواند باشد 

Between True And Null=Null 

Between Null And True=Null

Between Null And Null=Null

( یکسری از دوستان فقط بلدن کپی کنن !!! در عبارت بالا  فاصله 

رعایت شود )

نکته !!!   اگر در تکست باکسها  الگوی ورودی یا Input Mask استفاده کردید برای تاریخ حتما باید مثل Input Mask فیلد تاریخ باشد یعنی اگر در فیلد تاریخ جدول  از 0 ;0000/00/00  استفادا کردید که اسلش ها هم درنظر گرفته میشود اگر Inpust Mask تکست باکس ها را فقط 0000/00/00 در نظر بگیرید چون مچ نمی شود خالی نمایش میدهد  و یا اگر اینپوت ماسک شما در جدول بصورت 0000/00/00 ( که اسلش ها در نظر گرفته نمی شود ) باشد و در تکست باکس از اینپوت ماسک استفاده نکرده باشید و خودتان تاریخ را با اسلش تایپ کنید باز هم مچ نمیشود چون در جدول فرضا 13980120 ثبت شده ولی شما در تکست باکس 1398/01/20 وارد کرده اید . ( پس خالی بودن دیتیل فرم بدین معنیست که مچ نشده چه تاریخ مورد نظر در مقدار فیلد جدول نباشد یا اینکه الگوی شما با تکست باکس نخواند )

پس اول چک کنید که الگوها یکی باشند !!! بعد از تطبیق دادن اگر شما یکی از تکست باکس ها را خالی رها کنید زمانیکه باتن فیلتر را کلیک می کنید باز هم دیتیل را خالی نمایش می دهد برای اینکه اگر یکی از آنها خالی بود از حالت فیلتر خارج شود کافیست با آن آدرس تکست باکس عبارت Is Null را بکار ببرید  ( بافاصله !!! ). اشتباه نکنید در محیط وی بی تابع  IsNull  وجود دارد.

Between (Forms!Form3!Text12 Or Forms!Form3!Text12 is Null) And

 (Forms!Form3!Text14 Or Forms!Form3!Text14 is Null)

برای درک عبارت بالا  بهتر است حتما عملکرد عملگرها را بدانیم 

Boolean Operation

• not NULL = NULL

• NULL or false = NULL

• NULL or true = true

• NULL or NULL = NULL

• NULL and false = false

• NULL and true = NULL

• NULL and NULL = NULL

The Microsoft Access IsNull function returns TRUE if the expression is a null value. Otherwise, it returns FALSE.

فرضا در Text12 هیچ مقداری وجود ندارد ( Null )  یعنی  Null است و is Null برابر True ( چون  در اینجا تکست 12 Null است پس True را بر می گرداند ) پس طبق  بالا داریم   

Null Or True=True

A	β	AND(&&)	OR(| |)	NOT(exclA)
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	TRUE	FALSE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	TRUE	TRUE	TRUE	FALSE

حال طبق جدول عملگرهای  منطقی بالا اگر تکست ۱۲ نال باشد مقدار پرانتز اول True هست ( طبق Operation Boolean ) و مقدار پرانتز دوم که True Or False هست هم میشود True و True And True نتیجتا True میشود 

در عملگر And اگر دو مقدار بولین فالز باشد فالز را بر می گرداند ولی اگر جفت ترو باشد ترو را

-داکیومنت برای  مقدار Boolean  :

Type Conversions

When Visual Basic converts numeric data type values to Boolean, 0 becomes False and all other values become True. When Visual Basic converts Boolean values to numeric types, False becomes 0 and True becomes -1.

در مورد Between...And : 

If expr, value1, or value2 is Null, Between...Andreturns a Null value.

برای شرط تاریخ میتوان با Iif شرطی نوشت که اگر تکست باکس  شروع خالی بودبرای عملگر بزرگتر ( برای تکست باکس شروع ) First یا Min ( بهتر است استفاده شود ) فیلد تاریخ اعمال شود و برای عملگر کوچکتر ( برای  تکست باکس خاتمه ) اگر تکست باکس خاتمه خالی بود Last یا Max فیلد تاریخ ( که بهتر است )