Expressies opstellen

1. Leerdoel

De student moet de verschillende mogelijkheden, uitdrukkingen en operatoren voor het opstellen van criteriumexpressies en berekende velden kennen en kunnen toepassen.

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.

2.1. Exacte overeenkomst

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.

2.2. Jokertekens in criteria

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.

2.3. Criteria met vergelijkingsoperatoren

Bij het opstellen van expressies kan 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
'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).

2.4. Geen overeenkomst

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.

2.5. Criteria met Booleaanse operatoren

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

2.6. Criteria voor verschillende velden gelijktijdig laten gelden

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:

Criteria die je in verschillende velden op eenzelfde criteria-rij plaatst, worden met een 'AND'-operator verbonden.

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."

Criteria voor verschillende velden die je in verschillende criteria-rijen plaatst, worden met een OR-operator verbonden.

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 (ongeacht de woonplaats)

2.7. Functies gebruiken in expressies

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, en 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")
resultaat '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:

	1	zondag
	2	maandag
	3	dinsdag
	4	woensdag
	5	donderdag
	6	vrijdag
	7	zaterdag

Datediff(tijdsinterval;datum1;datum2)

DateDiff("yyyy";"01/05/1962";"16/09/2009")
resultaat: 47 (jaren)

DateDiff("m";"01/05/1962";"16/09/2009")
resultaat: 568 (maanden)

Je kan als argumenten ook de naam van een datumveld gebruiken tussen rechte haken of een datumfunctie:

DateDiff("yyyy";[datum in dienst];Now())
resultaat: het aantal jaren dat verlopen is tussen de datum van indiensttreding en de datum van vandaag.

Berekent het verschil tussen twee datums uitgedrukt in dagen, maanden, jaren,... Vereist volgende argumenten:

tijdsinterval

	yyyy	jaar
	q	kwartaal
	m	maand
	y	dag van het jaar
	d	dag

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")
resultaat:

indien het veld 'Land' de waarde 'Zweden' bevat: "Zweeds"

indien het veld "Land" een andere waarde bevat: "Andere taal"

Vereist drie argumenten:

Een expressie die moet geëvalueerd worden
Een waarde of expressie die als resultaat wordt 'teruggeven' wanneer de expressie 'waar' is
Een waarde of expressie die als resultaat wordt 'teruggeven' wanneer de expressie 'niet waar' is

3. Oefening 1

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 voorbeeld database 'PersoneelsdatabaseVoorbeeld.accdb' kan je een voorbeeld van deze query vinden.

4. Oefening 2

Maak op basis van dezelfde tabel een selectiequery die de naam en voornaam van de medewerkers weergeeft als bijvoorbeeld 'J. J' (een berekend veld met de eerste letter van de voornaam, een punt en spatie, en ten slotte de eerste letter van de familienaam).
Toon van elke medewerker de plaatsnaam en de postnummer en sorteer de medewerkers op postcode.
Selecteer enkel de medewerkers die woonachtig zijn in een gemeente met een postnummer groter dan 1999 en kleiner dan 3500.
Sla de query op onder de naam 'Medewerkers naar plaatsnaam'.

Ook van deze query kan je een voorbeeld in de voorbeeld database 'PersoneelsdatabaseVoorbeeld.accdb' vinden.