Kryteria wyszukiwania
W zależności od typu danych w danym polu kwerendy, kryteria wyszukiwania mogą być zdefiniowane w następujący sposób:
- Tekst
symbole zastępujące znak/ciągi znaków:
| * | dowolny znak lub ciąg znaków, również o zerowej długości |
| ? |
pojedynczy znak |
| # |
cyfra |
| [A-Z] |
pojedynczy znak z listy liter alfabetu |
| [!A-Z] |
pojedynczy znak spoza listy liter alfabetu |
| [0-9] |
pojedynczy znak z listy cyfr |
| [!0-9] | pojedynczy znak spoza listy cyfr |
| [ĄĘĆŚ,;] |
pojedynczy znak z samodzielnie zdefiniowanej listy znaków |
| [!ĄĘĆŚ,;] | pojedynczy znak spoza samodzielnie zdefiniowanej listy |
Jak to wygląda w praktyce? Np. tak:
Like “*a*” – rekordy zawierające literę a, wielkość liter nie ma znaczenia
Like “*#*” – rekordy zawierające cyfrę
Like “?1*2” – rekordy zawierające cyfrę na drugim miejscu i kończące się
cyfrą2
Like “*[ąęó]*” – rekordy zawierające jedną z liter wpisanych w nawiasie
- Data
kryteria wyboru daty muszą być umieszczone w hasztagach.
Np. chcąc wyfiltrować konkretną datę wpisujemy
#2020-11-29#.
Dla zakresu dat będzie to wyglądało tak:
>#2020-11-09# And <#2020-11-17#
znaki nierówności mogą być też nieostre czyli np.
>=#2020-11-09# And <=#2020-11-17#
Zakres dat można też ująć w zapisie:
between #2020-11-09# And <#2020-11-17#
– w tym przypadku daty krańcowe też są brane do wyszukiwania.
W wyszukiwaniu dat bardzo ważnym aspektem jest też format daty. Jeżeli daty w kolumnie są zapisane (niezależnie od tego, jaki jest format wyświetlania daty) również z datą i godziną, to kryterium wyszukiwania ograniczone do zapisu #2020-11-20# wyznaczy rekordy tylko z godziną 00:00 tego dnia, każda inna godzina nie spełnia kryterium. W takim przypadku konieczny jest zapis:
>=#2020-11-20# and <#2020-11-21#
– czyli od godziny zero 20-go do wartości dat mniejszych od godziny zero dnia 21-go.
Ewentualnie, pamiętając o tym, że data to liczba -można wstawić dodatkowe pole w kwerendzie oparte na formule =Clng([Pole daty]) i w tym polu wpisać kryterium:
CLng(#2020-04-16#) - Wartości liczbowe
kryteria wyboru są tu proste, znane z matematyki. Działają tu wszystkie operatory matematyczne typu > czy <.
W stosunku do wartości liczbowych można tez stosować konstrukcję Between…And… - Prawda/Fałsz
tu jako kryterium wystarczy wpisać po prostu Prawda lub Fałsz. Można też zastosować wartości liczbowe:
0 – fałsz
-1 – prawda
Niezależnie od typu danych często pojawia się konieczność wyszukania z brakiem wartości w danym polu. Kryterium dla takich rekordów to:
Is Null – dla pustych rekordów
Is Not Null – dla niepustych rekordów
Filtrowanie w kwerendzie wybierającej
Filtrowanie rekordów w kwerendzie wybierającej definiowane jest w oknie widoku projektu kwerendy.
Kryteria wyszukiwania rekordów wpisywane są w zaznaczone wiersze kwerendy. Obowiązuje tu zasada, że kryteria wpisane w tym samym wierszu muszą być spełnione łącznie. Kolejne wiersze kryteriów są dodawane do filtrowania jako alternatywa czyli ze słowem kluczowym LUB.
Np. dla tabeli ze screenu poniżej:
spróbujmy wyfiltrować rekordy, w których Autor to Agata Christie oraz cena jest wyższa niż 20zł. Zapis kwerendy to:
a wyfiltrowane dane (czyli kwerenda w widoku Arkusza) to:
Kwerenda pokazująca rekordy, w których Autor to Agata Christie lub cena jest wyższa niż 20zł to:
w widoku Arkusza:
Oczywiście kryteria wyszukiwania nie muszą być tak ściśle określone jak w powyższych przykładach. W zależności od typu danych można je zdefiniować bardziej wieloznacznie.
Kryteria wyszukiwania
Funkcja NZ
Funkcja NZ jest moją ulubioną funkcją Accessa, często ją wykorzystuję – zarówno w wyrażeniach jak i w kodzie VBA. Funkcję można opisać jako wartość jeśli null.
Argumenty funkcji to:
-
- wartość pola – wartość pobrana z tabeli, kwerendy lub wyrażenia.
Typ danych – variant. - wartość jeśli null – czyli co wstawić, jeżeli wartość pola będzie nullem.
Typ danych – variant, uzależniony od typu pola.
- wartość pola – wartość pobrana z tabeli, kwerendy lub wyrażenia.
W praktyce najczęściej stosuje się formuły:
-
- =nz(wartosc_pola;O) – dla wartości liczbowych
- =nz(wartosc_pola;””) – dla wartości tekstowych
Odpowiednikiem funkcji nz w VBA jest funkcja o tej samej nazwie.
Null to nie zero
Projektując bazę danych Access (choć nie tylko, tak jest praktycznie we wszystkich systemach bazodanowych) trzeba pamiętać o tym, że puste pole tabeli ma wartość null czyli brak wartości. Nie jest to odpowiednik pustej komórki w Excelu, gdzie w zależności od typu danych taka wartość jest uznawana za zero czy pusty ciąg tekstowy i w ten sposób może być argumentem funkcji. W Accessie jeśli w polu jest null, wstawiając pole do formuły trzeba zastosować funkcję IsNull lub nz i w ten sposób zabezpieczyć się przed błędem.
Dotyczy to zarówno wyrażeń jak i kodu VBA.
Przycisk polecenia w formularzu
Przycisk polecenia w formularzu to najczęściej stosowany wyzwalacz uruchamiający makro lub procedurę.Podobnie jak inne formanty, chcąc wstawić go do formularza wystarczy w widoku Projektu wybrać jego kontrolkę w grupie Formantów na karcie Projektowanie.
Przy włączonym przełączniku Użyj kreatora formantów otworzy się automatycznie formularz Kreatora przycisków poleceń, w którym można wybrać jeden z najczęściej stosowanych przycisków polecenia.
Można albo skorzystać z tego kreatora albo samodzielnie ustawiamy zdarzenie związane przyciskiem. W Arkuszu właściwości w zakładce zdarzenia wybieramy Procedurę zdarzenia (czyli procedurę w kodzie VBA) lub makro.
Warto zwrócić uwagę, że w tym przypadku możliwości oprogramowanych zdarzeń są szersze – nie tylko kliknięcie na przycisk.
A tu krótki filmik z mojego kanału YT o Accessie, ilustrujący wstawianie przycisk polecenia do formularza.
Sortowanie w kwerendzie wybierającej
Jak posortować dane w kwerendzie? Załóżmy, że mamy tabelę:
kliknij screen, aby powiększyć
Przenosimy wszystkie pola tabeli do kwerendy wybierającej:
kliknij screen, aby powiększyć
Dodatkowo można dołożyć pole obliczeniowe np.likwidujące wartość null w polu DataP. Nazwałam to pole DataPelna i wstawiłam tam funkcję DataPelna: nz(DataP;Date())
kliknij screen, aby powiększyć
Funkcja ta działa w ten sposób, że jeżeli w kolumnie DataP jest jakaś wartość, wstawia ją. Jeżeli natomiast pole jest puste – wstawia datę bieżącą. Teraz pora na sortowanie. Jeżeli chcemy posortować kwerendę wg kilku różnych kolumn – istotna jest ich kolejność. Im bardziej dana kolumna jest na lewo – tym wyższy poziom sortowania, a więc jest sortowana w pierwszej kolejności.
W przykładzie ustawiłam sortowanie rosnąco dla kolumny Dzial, a w drugiej kolejności dla kolumny DataPelna.
kliknij screen, aby powiększyć
Rezultat wygląda tak:
kliknij screen, aby powiększyć
Chcą zmienić kolejność sortowania czyli najpierw wg DataPelna, a następnie wg Dzialu – wystarczy przenieść kolumny w siatce kwerendy.
kliknij screen, aby powiększyć
Jeżeli posortowana kwerenda będzie ustawiona jako źródło danych dla formularza czy raportu – poszczególne rekordy będą wyświetlać się zgodnie z kolejnością sortowania kwerendy.
Lista świąt w Accessie
Wszędzie tam, gdzie mamy do czynienia z datami, istotną właściwością konkretnej daty jest to czy jest to dzień roboczy czy nie. Z zaznaczeniem sobót czy niedziel nie ma problemu – wystarczy skorzystać z funkcji WeekDay. Pozostaje jeszcze kwestia sprawdzenia, czy nie jest to dzień świąteczny.
Moim zdaniem – najprostszym rozwiązaniem jest stworzenie tabeli świąt, w której poszczególne rekordy są datami świąt kalendarzowych. Przykładowa tabela może wyglądać tak:
Mając taką tabelę – sprawdzenie, czy dana data jest świętem (i ewentualnie jakim) jest już proste. Można to zrobić np. w kwerendzie z wykorzystaniem funkcji DCount (lub Dlookup).
Samą tabelę świąt można oczywiście wypełnić danymi ręcznie, ale zdecydowanie szybciej i prościej można zrobić to za pomocą procedury VBA.
To moja propozycja kodu:
|
Public Sub WstawDaty() Dim DataWielkanoc As Date Dim RST As ADODB.Recordset Dim DataP As Date Dim Rok As Integer Set RST = New ADODB.Recordset RST.Open “TabDatySwiat”, CurrentProject.Connection, adOpenDynamic, adLockOptimistic With RST For Rok = 2020 To 2025 DataWielkanoc = Wielkanoc(Rok) .AddNew !Data = DateSerial(Rok, 1, 1) !nazwaswieta = “Nowy Rok” .Update .AddNew !Data = DateSerial(Rok, 1, 6) !nazwaswieta = “Trzech Króli” .Update .AddNew !Data = DataWielkanoc !nazwaswieta = “Wielkanoc” .Update .AddNew !Data = DataWielkanoc + 1 !nazwaswieta = “2 dzień Wielkanocy” .Update .AddNew !Data = DateSerial(Rok, 5, 1) !nazwaswieta = “1 Maj” .Update .AddNew !Data = DateSerial(Rok, 5, 3) !nazwaswieta = “3 Maj” .Update .AddNew !Data = DataWielkanoc + 60 !nazwaswieta = “Boże Ciało” .Update .AddNew !Data = DateSerial(Rok, 8, 15) !nazwaswieta = “Święto Wojska Polskiego” .Update .AddNew !Data = DateSerial(Rok, 11, 1) !nazwaswieta = “Wszystkich Świętych” .Update .AddNew !Data = DateSerial(Rok, 11, 11) !nazwaswieta = “Dzień Niepodległości” .Update .AddNew !Data = DateSerial(Rok, 12, 25) !nazwaswieta = “Boże Narodzenie” .Update .AddNew !Data = DateSerial(Rok, 12, 26) !nazwaswieta = “2 dzień Bożego Narodzenia” .Update Next Rok .Close End With Set RST = Nothing End Sub —————————————————- Private Function Wielkanoc(Rok As Integer) As Date Dim Liczba As Long Dim Krotnosc As Long Dim i As Integer Liczba = DateSerial(Rok, 5, Day(Minute(Rok / 38) / 2 + 56)) For i = 0 To 6 If (Liczba – i) Mod 7 = 0 Then Krotnosc = Liczba – i Exit For End If Next i Wielkanoc = CDate(Krotnosc – 34) End Function |
W tym przykładzie tworzona jest lista świąt na lata 2020 – 2025, można oczywiście zmodyfikować do własnych potrzeb.
Warto też zwrócić uwagę na funkcję Wielkanoc, wyznaczającą datę Wielkanocy (a tym samym powiązany z nią terminem Bożego Ciała). To święto jest nietypowe, uzależnione od terminu pierwszej wiosennej pełni księżyca w danym roku. Można je jednak też obliczyć, tak jak w podanym przykładzie funkcji.
A przy okazji – w Excelu podobna lista świąt wygląda tak:
Uniwersalna lista świąt w Excelu
Data i czas to liczby
Typ danych Data/Czas często sprawia problemy w prawidłowym korzystaniu z bazy danych. Wprawdzie Access ma mechanizmy zabezpieczające przed błędami przy wpisywaniu daty – tak, aby naprawdę była to data w rozumieniu informatycznym (nie tylko Microsoftu), ale możliwości użytkowników i tak są tu niewyczerpane. Warto więc ciągle przypominać: data to liczba! Konkretna data w kalendarzu to liczba dni od dnia 1 stycznia 1900r. Na przykład data 14 sierpnia 2020r. to liczba 44057. Oznacza to także, ze każdą datę można przekształcić na liczbę typu Long.
A czas? To liczba po przecinku. Wyznacza się ją w wyniku dzielenia godziny zegarowej (oczywiście może być z minutami i sekundami) przez 24 (czyli liczbę godzin w ciągu doby). Np. 44057,5 można przekształcić na 14.08.2020r. godz.12:00. Taką pełną datę wraz z czasem można przekształcić też na liczbę, choć w tym przypadku na typ Double. I odwrotnie – każdą taką liczbę można przekształcić na datę.
Generalnie wpisując w tabeli/formularzu wartość, która ma być datą, warto skorzystać z kalendarza:
A jeżeli już wpisujemy datę ręcznie – to w formacie RRRR-MM-DD (ewentualnie RRRR-MM-DD gg:mm). Wszystko inne da się załatwić formatowaniem.
Pole tekstowe w formularzu
Pole tekstowe w formularzu to ten formant, w którym wyświetlane (edytowane, dodawane) są dane. Najczęściej pobierane są z tabel/kwerend (związany format formularza), ale mogą być też zupełnie niezwiązane, oparte np. na formułach.
Chcąc wstawić Pole tekstowe do formularza wystarczyć przejść do widoku projektu formularza w karcie Narzędzia główne:
a następnie – przycisk w grupie Formanty na karcie Projektowanie.
No, a potem można już w pełni korzystać z Arkusza właściwości ustawiając odpowiednie formatowanie czy też programując zdarzenia związane z tym formantem.
A tu krótki filmik z mojego kanału YT o Accessie, ilustrujący wstawianie pola tekstowego do formularza.