De student moet de verschillende mogelijkheden, uitdrukkingen en operatoren voor het opstellen van criteriumexpressies en berekende velden kennen en kunnen toepassen, om aldus meer geavanceerde query's te kunnen bouwen.
In de vorige webpagina werd uitgelegd dat de rij 'Criteria' in het
ontwerpraster van een query het mogelijk maakt dat de resultaten van een query
enkel de records bevatten die aan bepaalde criteria (voorwaarden) voldoen.
Dergelijke voorwaarden worden door MS Access 'expressies' (uitdrukkingen)
genoemd.
Expressies worden niet enkel gebruikt om in een query een criterium in te stellen. Ook voor het invoegen van een berekend veld, kunnen expressies worden gebruikt.
De regels en voorwaarden, die voor het opstellen van expressies, in acht moeten worden genomen, zijn thans voorwerp van bespreking.
Voor het zoeken naar een specifieke waarde, die exact in het betreffende veld gevonden moet worden, kan je gewoon de gewenste (op te zoeken) waarde invullen.
Bijvoorbeeld: één of meerdere woorden (bijvoorbeeld: "Jan Janssen") of een
cijfer.
Het volstaat daarbij enkel de betreffende de waarde in te tikken omdat Access
dan zelf de aanhalingstekens
toevoegt, wat noodzakelijk is indien de waarde een tekst is en geen
cijfer.
Om te zoeken naar een waarde, waarvan je de schrijfwijze niet geheel kent, kunnen de in Windows gebruikelijke 'jokertekens' worden gebruikt. Onderstel dat we een naam van een medewerker willen opzoeken:
Jokerteken | Betekenis | Voorbeeld |
? | vervangt één teken | "M?ier" vindt: Maier, Meier, Moier, Muier,... |
* | vervangt een willekeurig aantal tekens | "M*" vindt: Maier, Meier, Moier, Muier,... maar ook Meester, Materman,... alle namen met beginletter 'M'. |
# | vervangt één cijfer | Like '1###' vindt alle duizendtallen beginnend met het cijfer 1 |
Like | vervangt een willekeurig aantal tekens | Like 'S*' vindt alle namen die met de letter S beginnen. Like '[A-D]*' vindt alle namen beginnend met A, B, C of D. |
Bij het opstellen van expressies kan daarenboven gebruik worden gemaakt van de volgende vergelijkingsoperatoren:
> | Groter dan |
< | Kleiner dan |
<= | Kleiner of gelijk aan |
>= | Groter of gelijk aan |
<> | Niet gelijk aan |
= | Gelijk aan |
Bijvoorbeeld:
Criterium in een veld | Expressie | Uitkomst: enkel records met |
'Geboortedatum': | > #31/12/1980# | geboortedatum groter dan 31/12/1980 Noot: bij het intikken van een datum in het ontwerpraster wordt deze automatisch tussen twee hekjes # geplaatst |
'Stagequotering' | =>10 | quotering groter of gelijk aan 10 |
'Plaatsnaam' | <>"Leuven" | plaatsnaam anders dan 'Leuven' |
In combinatie met jokertekens levert bijvoorbeeld de expressie '<=E*' alle namen op uit een naamveld die als beginletter de letter 'E' of een letter voorgaand aan de letter 'E' in het alfabet dragen (A, B, C, D).
Om een expressie op te bouwen met een waarde, die niet in het betreffende veld mag voorkomen, kan je eenvoudigweg de 'NOT' operator vóór de waarde plaatsen.
Bijvoorbeeld: de expressie 'NOT 3000' als criterium voor het veld 'Postnummer' sluit alle records uit met de waarde 3000 als postnummer.
Om op één veld meerdere criteria gelijktijdig toe te passen, kunnen deze verschillende criteria met elkaar verbonden worden door een zogenaamde Booleaanse operator.
Booleaanse operator |
Betekenis | Voorbeeld |
AND | 'En daarenboven' Aan alle criteria moet tegelijkertijd voldaan worden |
> #31/12/1980# AND < #1/01/1985# Datum moet groter zijn dan 31/12/1980 en daarenboven kleiner zijn dan 1/01/1985 |
OR | 'Of' Aan één van de gestelde criteria moet voldaan zijn |
Leuven OR Aarschot Plaatsnaam moet ofwel Leuven ofwel Aarschot zijn |
NOT | 'Niet' Aan het criterium mag niet voldaan zijn |
(>=3000 And <=3500) And Not 3200 Postcode moet groter of gelijk zijn aan 3000 én daarenboven kleiner of gelijk aan 3500, maar mag niet 3200 bedragen |
BETWEEN | 'Gelegen tussen' De waarde moet binnen een opgegeven reeks liggen |
Between #1-1-1980# and #1-1-1990# Datum moet tussen 1-1-1980 en 1-1-1990 gesitueerd zijn |
IN | 'Voorkomen in de reeks' De waarde moet in een opgegeven reeks voorkomen |
IN(Leuven;Aarschot;Wijgmaal) Plaatsnaam moet ofwel Leuven, ofwel Aarschot, ofwel Wijgmaal zijn |
In het ontwerpraster kunnen uiteraard ook voor verschillende velden criteria worden opgegeven, die allen gelijktijdig moeten worden toegepast bij het uitvoeren van de query.
Hierbij gelden twee principes:
Zo bevat bovenstaand ontwerpraster de zoekopdracht:
"Selecteer de werknemers van het vrouwelijke geslacht (code 1) en (AND) die daarenboven hetzij in Leuven, hetzij in Aarschot woonachtig zijn."
Dit ontwerpraster bevat bijgevolg de zoekopdracht: "Selecteer alle werknemers die ofwel in Leuven, ofwel in Aarschot woonachtig zijn, OF die van het vrouwelijke geslacht zijn (code 1 voor het veld 'Geslacht')."
De bovenstaande uitkomstentabel toont in dit geval:
- Alle medewerkers die in Leuven of Aarschot wonen (zowel mannen als vrouwen)
- Maar ook alle medewerkers van het vrouwelijke geslacht (code =1), ongeacht de woonplaats
Functies kwamen eerder reeds aan bod in dit zelfstudiepakket bij de bespreking van MS Excel. Klik hier voor meer achtergrondinformatie aangaande het gebruik van functies in MS Excel. MS Access bevat quasi dezelfde ingebouwde functies als MS Excel.
We zetten in onderstaande tabel de belangrijkste functies, die je bij het opbouwen van expressies in MS Access kan gebruiken, op lijst: de functienaam, een voorbeeld dat aangeeft hoe je de functie gebruikt en welk resultaat de functie oplevert, evenals een beschrijving van de gebruiksmogelijkheden.
Via de Help-functie van Access kan je meer info aangaande deze en andere functies vinden.
Functienaam |
Voorbeeld | Gebruiksmogelijkheden | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Datumfuncties |
|||||||||||||||||||||||
Year(datum) |
Year("05/06/2012") |
Vereist als argument een datum. Haalt het jaartal uit een datum. | |||||||||||||||||||||
Month(datum) |
Month("05/06/2012") resultaat: '6' |
Vereist als argument een datum. Haalt het maandnummer uit een datum: getal tussen 1 en 12. | |||||||||||||||||||||
Day(datum) |
Day("05/06/2012") resultaat: '5' |
Vereist als argument een datum. Haalt het dagnummer uit een datum: een getal tussen 1 en 31. | |||||||||||||||||||||
Date() |
Date() resultaat: 14/05/2012 |
Vereist geen argumenten. Geeft als uitkomst de huidige systeemdatum (de dag van vandaag) | |||||||||||||||||||||
Now() |
Now() resultaat: 14/05/2012 21:48:14 |
Vereist geen argument. Geeft als uitkomst de huidige systeemdatum en -tijd | |||||||||||||||||||||
Weekday(datum) |
Weekday("14/05/2012") resultaat: 5 |
Vereist als argument een datum. Geeft het nummer van dag weer, als volgt:
|
|||||||||||||||||||||
Datediff(tijdsinterval;datum1;datum2) |
DateDiff("yyyy";"01/05/1962";"16/09/2009") DateDiff("m";"01/05/1962";"16/09/2009") Je kan als argumenten ook de naam van een datumveld gebruiken tussen rechte haken of een datumfunctie: DateDiff("yyyy";[datum in dienst];Now()) |
Berekent het verschil tussen twee datums uitgedrukt in dagen, maanden, jaren,... Vereist volgende argumenten: tijdsinterval
datum1 Een ingevoerde datum, de naam van een datumveld of een datumfunctie die in een datum resulteert datum2 Een ingevoerde datum, de naam van een datumveld of een datumfunctie die in een datum resulteert |
|||||||||||||||||||||
Rekenkundige functies |
|||||||||||||||||||||||
ABS(getal) |
ABS(-100) resultaat: 100 |
Vereist als argument een getal. Geeft als resultaat de absolute waarde van een getal. | |||||||||||||||||||||
INT(getal) |
INT(10,27) resultaat: 10 |
Vereist als argument een getal. Geeft het gedeelte van een getal vóór het decimaalteken als resultaat | |||||||||||||||||||||
Round(getal;aantal decimalen) |
Round(10,92;1) resultaat: 10,9 |
Vereist als argument een getal én een getal dat het aantal weer te geven decimalen opgeeft. Geeft een getal als resultaat dat is afgerond tot op het opgegeven aantal decimalen. | |||||||||||||||||||||
Tekstfuncties |
|||||||||||||||||||||||
LCase(tekenreeks) |
LCase("WOORDEN") resultaat: woorden |
Vereist als argument een tekenreeks (woord of reeks woorden). Geeft een tekenreeks als resultaat die is geconverteerd naar kleine letters. | |||||||||||||||||||||
Left(tekenreeks;lengte) |
Left("woorden";3) resultaat: woo |
Vereist als argumenten een tekenreeks en het aantal te weerhouden tekens aan de linkerzijde van een tekenreeks. Geeft een tekenreeks als resultaat die een opgegeven aantal tekens vanaf de linkerkant van een tekenreeks bevat. |
|||||||||||||||||||||
Len(tekenreeks) |
Len("woorden") resultaat: 6 |
Vereist als argument een tekenreeks. Geeft een getal als resultaat dat het aantal tekens in een tekenreeks weergeeft (de lengte van een tekenreeks). | |||||||||||||||||||||
Right(tekenreeks;lengte) |
Right("woorden";4) resultaat: rden |
Vereist als argument een tekenreeks. Geeft een tekenreeks als resultaat die een opgegeven aantal tekens vanaf de rechterkant van een tekenreeks bevat. | |||||||||||||||||||||
UCase(tekenreeks) |
UCase("woorden") resultaat: WOORDEN |
Vereist als argument een tekenreeks. Geeft een tekenreeks als resultaat die de opgegeven tekenreeks bevat, omgezet in hoofdletters. | |||||||||||||||||||||
Andere functies |
|||||||||||||||||||||||
IIf(expressie; alswaar; alsonwaar) |
IIF([Land]="Zweden"; "Zweeds"; "Andere taal") indien het veld 'Land' de waarde 'Zweden' bevat: "Zweeds" indien het veld "Land" een andere waarde bevat: "Andere taal" |
Vereist drie argumenten:
|
Maak op basis van de tabel 'Werknemers Adressen' in de 'Personeelsdatabase'
op je USB-stick, harde schijf of Homedrive een nieuwe selectiequery waarin wordt weergegeven hoeveel
mannelijke medewerkers in de leeftijdsklassen 21 tot 30 jaar en 31 tot 40 jaar
voorkomen op het moment van indiensttreding (herinner je dat in een eerdere oefening aan deze leeftijdsklassen respectievelijk de codes 2 en 3 werden
toegekend). Sorteer de uitkomst op 'Leeftijdsklasse'.
Sla deze query op onder de naam 'Aantal mannelijke medewerkers tussen 21 en 40
jaar'.
In de voorbeelddatabase 'PersoneelsdatabaseVoorbeeld.accdb' kan je een voorbeeld van deze query vinden.
Ook van deze query kan je een voorbeeld in de voorbeelddatabase 'PersoneelsdatabaseVoorbeeld.accdb' vinden.
Klik hier om de oefening 'Aantal jaren dienst' te maken.
Klik hier om de oefening 'ICT-cursussen' te maken.
Helpprogramma bij Microsoft Access 2013: zoek op 'voorbeelden van expressies', 'query operator criterium'.