Filter di dalam Microsoft Access [1/6]

Dear all my friends, kita belajar Filter yah! Filter merupakan fasilitas untuk menyaring data (sekaligus pencarian data), dengan kriteria yang dapat kita tentukan sendiri. Ia adalah "Yahoo!" nya atau "Google" nya Access. Filter adalah "search engine" untuk menyaring dan mencari berbagai data di dalam Access.

Mempelajari filter di dalam Access hukumnya adalah wajib, dan Insya Allah saya akan mencoba membahas penggunaan filter ini secara berkesinambungan (bertahap dari bagian 1 s/d 6). Bagi Anda yang sedang belajar ataupun sedang membuat program, berhentilah sejenak, dan marilah kita belajar sama-sama mengenai penggunaan filter di dalam Access ini secara sungguh-sungguh, karena ia sangat penting.

PENGGUNAAN FILTER DI DALAM ACCESS

Penggunaan filter di dalam Access dapat diaplikasikan pada 4 object utama, diantaranya Table, Query, Form dan Report.

Filter di dalam Access dapat dilakukan melalui empat metode sebagai berikut, yaitu:

1. Penggunaan Filter Secara Manual, yakni penggunaan filter secara langsung melalui toolbar atau menu filter yang tersedia ketika object Access sedang terbuka, ataupun melalui menu popup ketika data di dalam object Access di klik kanan.

2. Penggunaan Filter Melalui Property Object, yakni penggunaan filter yang diset secara langsung melalui property filter pada object yang tersedia.

3. Penggunaan Filter Melalui Macro, yakni penggunaan filter yang diakses melalui Macro.

4. Penggunaan Filter Melalui VBA, yakni penggunaan filter yang diakses melalui VBA.

Masing-masing metode filter diatas memiliki kelebihan tersendiri, dan setelah memahaminya, Insya Allah Anda akan mengetahui kapan dan dimana sebaiknya menggunakan keempat metode diatas. Penggunaan filter sangat erat kaitannya dengan "criteria" (kriteria/syarat). Selain itu, filter sering digabungkan pula dengan penggunaan "sort" (pengurutan data) atau biasa disebut juga "order" (Insya Allah mengenai pengunaan Order ini, saya akan membahasnya secara terpisah, setelah pembahasan mengenai filter kita selesaikan dengan sempurna). Sebelum mempelajari keempat metode filter diatas, saya hendak menyampaikan dahulu mengenai penggunaan "criteria" di dalam Access.

CRITERIA DI DALAM ACCESS

Criteria digunakan sebagai syarat penyaringan (filter) data sesuai yang kita inginkan. Filter adalah proses penyaringannya, sedangkan criteria adalah syarat yang menyatakan data mana saja yang hendak ditampilkan. Jadi criteria dan filter merupakan dua hal yang saling berhubungan, karena ketika membuat filter, kita pasti harus membuat criteria.

Criteria didalam Access dapat dibagi menjadi sembilan jenis, diantaranya:

1. Ranges of Values criteria, yaitu criteria yang menghasilkan record yang nilainya berada pada batasan tertentu. Ciri criteria jenis ini adalah menggunakan operator aritmatika didalamnya, yaitu:

- = : Sama dengan
- > : Lebih besar dari
- < : Lebih kecil dari
- >= : Lebih besar atau sama dengan
- <= : Lebih kecil atau sama dengan
- <> : Tidak sama dengan
- Between [...] And [...] : Diantara [...] dan [...]. Symbol [...] adalah nilai yang hendak dijadikan sebagai kriteria.

Contoh:

> 234, digunakan untuk field bertype number, akan menghasilkan record yang nilainya lebih besar dari 234.

< 1200.45, digunakan untuk field bertype number, akan menghasilkan record yang nilainya lebih kecil dari 1200.45.

>= "Jakarta", digunakan untuk field bertype text, akan menghasilkan record yang nilainya lebih besar atau sama dengan text "Jakarta".

Between #2/2/1999# And #12/1/1999#, digunakan untuk field bertype tanggal, akan menghasilkan record yang tanggalnya diantara 2-Feb-1999 s/d 1-Dec-1999 (ANSI-89).

Between '2/2/1999' And '12/1/1999', digunakan untuk field bertype tanggal, akan menghasilkan record yang tanggalnya diantara 2-Feb-1999 s/d 1-Dec-1999 (ANSI-92)

<> (10 * 2), Digunakan untuk field bertype number, akan menghasilkan record yang nilainya tidak sama dengan 20.

Catatan:

· ANSI (American National Standards Institute), merupakan standard penulisan yang ditetapkan untuk mendefiniskan penulisan kode SQL. Terdapat dua standard ANSI yaitu ANSI-89 dan ANSI-92. Namun ANSI yang paling sering digunakan adalah ANSI-92. Microsoft Access telah banyak menambahkan feature-feature dan kode-kode SQL baru didalam ANSI standard tersebut, dan terkadang feature-fature dan kode-kode SQL baru ini hanya dapat dijalankan di query Microsoft Access (Jet database), serta tidak dapat dijalankan di SQL Server, namun demikian sebagian besar sudah sama persis. Oleh karena itu bagi Anda yang sudah terbiasa bermain kode SQL di SQL Server dan lain-lain, mungkin akan merasa lebih mudah ketika bermain kode SQL di Access.

· Khusus untuk penulisan tanggal secara langsung di dalam kode SQL ataupun di dalam VBA harus menganut system mm-dd-yy (system tanggal English/USA/United Kingdom), yaitu bulan-tanggal-tahun.

2. NOT criteria, yaitu criteria yang menghasilkan record dengan nilai yang bukan atau selain dari criteria yang ditentukan.

Contoh:

Not "OPI", digunakan untuk field bertype text, akan menghasilkan record yang nilai didalamnya tidak terdapat text "OPI".

Not 2, digunakan untuk field bertype number, akan menghasilkan record yang nilainya selain 2.

Not T*, digunakan untuk field bertype text, akan menghasilkan record yang nilainya tidak diawali dengan huruf "T" (ANSI-89), misalnya akan menampilkan selain Tahu, Tempe, dan lain-lain.

Not T%, digunakan untuk field bertype text, akan menghasilkan record yang nilainya tidak diawali dengan huruf "T" (ANSI-92), misalnya akan menampilkan selain Tahu, Tempe, dan lain-lain.

4. IN criteria, yaitu criteria yang menghasilkan record yang nilainya terdapat di salah satu criteria yang ditentukan.

Contoh:

In("IND", "JPN"), digunakan untuk field bertype text, akan menghasilkan record yang nilainya sama dengan "IND" atau "JPN".

5. Text, partial, dan matching values criteria. Yaitu criteria yang diaplikasikan khusus untuk data type text dan dapat digunakan untuk mencari sebagian atau keseluruhan data. Semua contoh dibawah ini memperlihatkan criteria yang digunakan hanya untuk field bertype text:

"JKT", akan menghasilkan record yang nilainya sama dengan text "JKT".

"JKT" Or "BKS", akan menghasilkan record yang nilainya sama dengan "JKT" atau "BKS".

"JKT" And "BKS", akan menghasilkan record yang nilainya sama dengan "JKT" dan "BKS".

>="N", akan menghasilkan record yang nilainya lebih besar atau sama dengan "N".

Like "S*", akan menghasilkan record yang nilainya diawali dengan huruf "S", seperti Sofyan, Syamsudin, dan lain-lain (ANSI-89).

Like "*S*", akan menghasilkan record yang ditengah nilainya terdapat huruf "S", seperti Usman, Musik, dan lain-lain (ANSI-89).

Like "S%", akan menghasilkan record yang nilainya diawali dengan text "S", seperti Sofyan, Syamsudin, dan lain-lain (ANSI-92).

6. Expression criteria, yaitu criteria yang menggunakan fungsi (function) didalamnya, baik function tersebut bawaan dari Access maupun buatan sendiri.

Contoh:

Right([NamaField], 2) = "99", yakni menambahkan expression berupa fungsi RIGHT pada criteria. RIGHT digunakan untuk mengambil text dari kanan sebanyak nilai karakter yang ditentukan (pada contoh ini 2 karakter). Menggunakan contoh criteria ini akan menghasilkan record yang nilai akhirnya (sebanyak 2 karakter) sama dengan text "99"

Len([NamaField]) > Val(30), dimaan LEN ini digunakan untuk mengambil jumlah karakter pada suatu text. Contoh, LEN("OPI") = 3. Adapun VAL digunakan untuk mengambil sejumlah number dari string. Penggunaan criteria ini akan menghasilkan record yang jumlah karakternya lebih besar dari 30 karakter.

Date(), digunakan untuk field bertype tanggal, akan menghasilkan record yang nilainya sama dengan tanggal hari ini.

7. NULL criteria, yaitu criteria yang digunakan untuk menghasilkan record berdasarkan ada tidaknya nilai pada field tersebut.

Contoh:

Is Null, akan menghasilkan record yang kosong, tanpa nilai sedikitpun (Null atau blank atau kosong)

Is Not Null, akan menghasilkan record yang tidak kosong (terdapat nilainya).

" ", sama fungsinya dengan null. Criteria ini digunakan khusus untuk data type text yang kosong atau terdapat spasi kosong.

8. Boolean criteria, yaitu criteria yang digunakan khusus pada field dengan data type boolean (Yes/No). Ciri utama criteria jenis ini adalah menggunakan salah satu pasangan kata dari True atau False, -1 atau 0, ataupun Yes atau No didalam criterianya.

Contoh:

-1, akan menghasilkan record yang nilainya True atau Yes.

0, akan menghasilkan record yang nilainya False atau No.

9. Subquery criteria, yaitu criteria yang menggunakan kode SQL yang mengambil nilai criteria dari suatu table atau query lainnya. Contoh:

(SELECT [UnitPrice] FROM [Products] WHERE [ProductName] = "Aniseed Syrup"), akan menghasilkan record yang nilainya sama dengan field UnitPrice pada table Products, dimana UnitPrice yang diambil khusus untuk ProductName "Aniseed Syrup" saja.

> (SELECT AVG([UnitPrice]) FROM [Products]), akan menghasilkan record yang nilainya lebih besar dari nilai rata-rata pada field UnitPrice pada table Products.

> ALL (SELECT [Salary] FROM [Employees] WHERE ([Title] LIKE "*Manager*") OR ([Title] LIKE "*Vice President*")), akan menghasilkan record yang nilainya lebih besar dari seluruh nilai field Salary pada table Employees, dimana data salary yang diambil khusus untuk Title Manager dan Vice President saja.

> ALL (SELECT AVG([UnitPrice] * [Quantity]) FROM [Order Details]), akan menghasilkan record yang nilainya lebih besar dari seluruh nilai rata-rata field UnitPrice dikali field Quantity pada table Order Details.

PENGGUNAAN CRITERIA

Dalam pemrograman Access seringkali dibutuhkan citeria didalam operasinya, misalnya saja ketika Anda hendak melihat suatu data transaksi didalam periode tertentu, melihat sales penjualan hanya untuk kota tertentu saja, memfilter sejumlah record, dan lain sebagainya. Penggunaan criteria didalam Access dapat dibagi menjadi enam macam, yaitu:

· Criteria untuk tanggal (date)
· Criteria untuk angka (numeric/number)
· Criteria untuk text (string)
· Criteria untuk nilai null (kosong atau tanpa nilai)
· Criteria untuk Multiple Criteria (criteria yang lebih dari satu)
· Criteria yang merujuk ke suatu control pada Form

1. Criteria untuk tanggal (date)

Penggunaan criteria untuk data type tanggal harus menggunakan tanda pagar (#) pada awal dan akhir penulisan criteria. Untuk kasus tertentu, misalnya ketika regional setting yang kita gunakan tidak menggunakan system tanggal English/USA/United Kingdom (yaitu bulan-tanggal-tahun) maka data type tanggal tersebut sebelumnya harus di konversi dahulu menjadi data type text. Berikut ini adalah contoh penggunaan criteria tanggal untuk melihat karyawan yang lahirnya (misal nama fieldnya BirthDate) setelah tanggal 1 Januari 1960 pada form Employees, yang diaplikasikan melalui penggunaan Filter sebagai berikut:

Forms!Employees.Filter = "[BirthDate] >= #1-1-60#"

2. Criteria untuk angka (numeric/number)

Penggunaan criteria untuk jenis ini dapat dituliskan secara langsung, tanpa tanda tertentu. Contoh:

Forms!Employees.Filter = "[EmployeeID] = 7"

3. Criteria untuk text (string)

Penggunaan criteria untuk data type text harus menggunakan tanda petik satu (') pada awal dan akhir penulisan criteria. Contoh:

Forms!Employees.Filter = "[LastName] = 'Buchanan'"
Forms!Employees.Filter = "[LastName] <> 'Buchanan'"

Untuk penggunaan operator "Not" dan "Like", maka harus ditulis tanpa menggunakan tanda sama dengan (=). Contoh:

Forms!Employees.Filter = "[LastName] Like '*ucha*'"
Forms!Employees.Filter = "[LastName] Not Like '*ucha*'"

4. Criteria untuk nilai null (kosong atau tanpa nilai)

Sebagaimana pada penggunaan operator "Not" dan "Like", penggunaan criteria untuk data kosong (Is Null) mempunyai tata cara penulisan khusus yang berbeda dengan penulisan lainnya, yaitu nama field yang hendak dijadikan criteria ditulis secara langsung tanpa tanda sama dengan (=). Contoh:

Forms!Employees.Filter = "LastName Is Null"

Begitu juga jika Anda hendak melihat data yang bukan null (Is Not Null), maka penulisannya ditulis secara langsung dengan nama fieldnya tanpa tanda sama dengan (=). Contoh:

Forms!Employees.Filter = "LastName Is Not Null"

5. Criteria untuk Multiple Criteria (criteria yang lebih dari satu)

Dalam hal penggunaan criteria lebih dari satu, maka untuk menggabungkan berbagai criteria tersebut harus menggunakan salah satu dari tiga operator sebagai berikut:

· AND, digunakan untuk menggunakan suatu kriteria yang semua kriterianya harus terpenuhi. Jadi jika salah satu kriteria tidak terpenuhi, maka ia akan menolak.

· OR, digunakan untuk menggunakan suatu kriteria yang salah satunya harus terpenuhi. Jadi jika salah satu kriteria tidak terpenuhi, maka program masih dapat diteruskan.

· NOT, digunakan untuk menggunakan suatu kriteria yang bukan atau selain dari kriteria yang dituliskan.

Contoh:

Forms!Employees.Filter = _
"[HireDate] >= #1-1-93# AND [Title] = 'Sales Representative'"

Perhatikan kode AND yang terdapat cuplikan kode VBA diatas. Terlihat bahwa penulisan AND ini tidak dipisahkan dari string yang melingkupi filter diatas, jadi harus disatukan dengan string lainnya.

6. Criteria yang merujuk ke suatu control pada Form

Selain kelima jenis criteria diatas, Anda dapat pula mengambil criteria yang merujuk ke suatu control di dalam suatu Form. Misalnya saja ketika hendak mencetak (print ataupun print preview) suatu report, memproses suatu query, dan lain-lain inginnya data tersebut di filter berdasarkan kriteria tertentu yang telah di set pada suatu field di dalam suatu form yang sedang terbuka. Penggunaan criteria ini memerlukan tata cara penulisan sebagaimana yang terdapat pada criteria tanggal, numeric, text, dan multiple criteria. Contoh:

· Untuk jenis Tanggal, yaitu menggunakan tanda pagar (#):

Forms!Employees.Filter = "[HireDate] >= #" _
& Forms!Employees!HireDate & "#"

· Untuk jenis Numeric (tanpa menggunakan tanda apapun):

Forms!Employees.Filter = "[EmployeeID] = " _
& Forms!Orders!EmployeeID)

· Untuk jenis Text, yaitu menggunakan tanda petik satu ('):

Forms!Employees.Filter = "[LastName] = '" _
& Forms!Employees!LastName & "'")

· Untuk Multiple Criteria:

Forms!Employees.Filter = "[HireDate] >= #" & _
Forms!Employees!datHireDate & "# AND [Title] = '" & _
Forms!Employees!strTitle & "'"

Keterangan: Khusus untuk penggunaan filter pada form dan report, maka setelah mengaplikasikan suatu filter, properties "Filter On" harus di set menjadi ON (True/Yes). Di dalam form, untuk menset properties "Filter On" harus melalui VBA. Contoh:

Me.Form.FilterOn = True 'Contoh untuk form utama
NamaSubForm.Form.FilterOn = True 'Contoh untuk subform

Adapun untuk report, properties "Filter On" ini dapat di set secara langsung melalui properties "Filter On" = Yes.

Insya Allah pada pembahasan selanjutnya akan saya sampaikan mengenai "Penggunaan Filter Secara Manual"