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.


 

Kurs Access - formularze i raporty

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.


 

Kurs Access - 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

 


Kurs Access - programowanie w VBA

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.


 

Kurs Access - formularze i raporty

Kreator formularzy

Tworząc aplikację Access w dużym stopniu można zautomatyzować poprzez wykorzystanie wbudowanych w system kreatorów. Oczywiście nie jest to szczyt możliwości, stanowi raczej podstawę do rozbudowy i modyfikacji.

Jednym z takich kreatorów jest Kreator Formularzy – karta Tworzenie –> Formularze –> Kreator formularz.

Wszystkie formularze służą przede wszystkim do odczytu, modyfikacji, usuwania i dodawania danych. Są tak ważną częścią aplikacji Access, że mam w planach napisanie wielu notek im poświęconym.

A na początek krótki filmik z mojego kanału YT o Accessie, ilustrujący wykorzystanie kreatora formularzy.


 

Import tabeli Excela do Accessa

Praktyka pokazuje, że zbyt duża ilość danych w tabeli Excela, powiązanych dodatkowo formułami, filtrami i stworzonymi na ich podstawie wykresami powoduje znaczne zwolnienie pracy i aż się prosi o optymalizację. Doskonałym wyjściem jest tu przejście do pracy w Accessie, który doskonale radzi sobie z przechowaniem i obróbką wielu rekordów.

Importy danych do Accessa dokonujemy w bazie danych Access, korzystając z przycisku Excel na wstędze Dane zewnętrzne.

Warto u zwrócić uwagę na wybór rodzaju pobierania danych. Do wyboru są 3 opcje importu danych:

      • utworzenie nowej tabeli
      • dołączenie danych do istniejącej tabeli
      • połączenie 

Zaimportowaną tabelę można zmodyfikować, ustawiając w zależności od potrzeb poszczególne jej właściwości. W stosunku do tabeli połączonej możliwości takich jest mniej.

A tu krótki filmik z mojego kanału YT o Accessie, pokazujący taki import w praktyce.

Filtrowanie raportu

Konkretny przykład do rozwiązania: jak wygenerować raport w Accessie z danymi wyfiltrowanymi na podstawie danych zawartych w polach formularza niezwiązanego?

Załóżmy, że mamy tabelę z katalogiem książek:

raport oparty na podstawie tej tabeli:

oraz prosty formularz  niezwiązany (o nazwie FormularzSzukaj) z formantami JakiDzial i JakaCena:

Po wpisaniu w te pola np.sensacja i 15 – chcemy otrzymać raport pokazujący te książki z działu sensacja, których cena wynosi co najmniej 15zł.  Pod zdarzenie przy kliknięciu przycisku Otwórz raport wprowadzamy makro:

                                          kliknij rysunek, aby powiększyć

Warunek WHERE dla makra to w tym przypadku:

[Dzial]=[Formularze]![FormularzSzukaj]![JakiDzial] And [Cena]>=[Formularze]![FormularzSzukaj]![JakaCena]

Chcąc otworzyć nie raport tylko podgląd wydruku raportu – wystarczy zmienić atrybut Widok na Podgląd wydruku.


                                          kliknij rysunek, aby powiększyć

Można też wybrać tu Wydruk, aby nie otwierając raportu, od razu wysłać go na drukarkę.

Zamiast makra pod przyciskiem generowania raportu można też użyć procedury VBA.

Procedura otwarcia raportu:

Private Sub Polecenie5_Click()
Dim JakiDzial As String
Dim JakaCena As Currency
JakiDzial = Me.JakiDzial
JakaCena = Me.JakaCena
DoCmd.OpenReport “Raportksiazek”, acViewReport, , “dzial='” & JakiDzial & “‘ and cena>=” & JakaCena
End Sub

Procedura otwarcia podglądu wydruku raportu:

Private Sub Polecenie5_Click()
Dim JakiDzial As String
Dim JakaCena As Currency
JakiDzial = Me.JakiDzial
JakaCena = Me.JakaCena
DoCmd.OpenReport “Raportksiazek”, acViewPreview, , “dzial='” & JakiDzial & “‘ and cena>=” & JakaCena
End Sub

Procedura bezpośredniego wydruku raportu:

Private Sub Polecenie5_Click()
Dim JakiDzial As String
Dim JakaCena As Currency
JakiDzial = Me.JakiDzial
JakaCena = Me.JakaCena
DoCmd.OpenReport “Raportksiazek”, acViewNormal, , “dzial='” & JakiDzial & “‘ and cena>=” & JakaCena
End Sub

 


Kurs Access - formularze i raporty

Tabela Access – przydatne uwagi

Tabela Access to miejsce, w  którym przechowywane są dane bazy danych.  Warto tu pamiętać, że w odróżnieniu od innych aplikacji Microsoft Office – wprowadzonych zmian w poszczególnych polach (chodzi o dane, nie o strukturę tabeli) nie trzeba zapisywać, od razu są zatwierdzane. Nie da się tak jak np.w Excelu – wyjść bez zapisywania, aby cofnąć zmiany.

A tu kilka uwag, które sprawdzają się przy projektowaniu tabeli. Wprawdzie nie są obowiązkowe, ale doświadczenie wskazuje, że ułatwiają projektowanie i pracę w aplikacji.

    • nazwy kolumn powinny być jednowyrazowe, unikamy w ten sposób stosowania bardziej złożonych odwołań do tabeli z poziomu innych obiektów, kreatora wyrażeń czy kodu VBA. Prościej jest operować nazwą np.MNP, MojaNazwaPola niż [Moja Nazwa Pola].
      Chcąc, aby np.w formularzu czy raporcie wyglądało to porządnie, wykorzystajmy właściwość Tytuł pola tabeli. Tu są znacznie szersze możliwości w wykorzystywaniu również znaków specjalnych, a zdecydowanie ułatwi pracę i nie wywoła konfliktów. Jeżeli Tytuł jest pusty – w widoku tabeli, czy w innych obiektach opartych na danej tabeli jest zastępowany Nazwą pola.

      w widoku tabeli:

       

    • polskie znaki diaktryczne też lepiej stosować tylko w tytule kolumny niż w jej nazwie
    • zdecydowanie nie polecam stosowania typu danych obliczeniowych.

      Jeżeli dane w którymś z pól mają być wynikiem działania jakiejś funkcji, lepiej zrezygnować z umieszczania takiego pola w tabeli i przenieść je do kwerendy. Efekt będzie taki sam, ale w ten sposób zmniejszymy rozmiar bazy danych. Tabele powinny służyć tylko do zapisywania danych.

    • warto definiować poszczególne właściwości pola tabeli, w zależności od typu danych i oczekiwanych wartości. Pozwoli to zmniejszenie rozmiaru bazy, usprawni jej działanie i pozwoli na wyeliminowanie błędów przy wprowadzaniu danych

 


Kurs Access 2013 od podstaw