Queries berekende velden

In een query kun je niet alleen de velden uit een tabel weergeven, maar ook bepaalde berekeningen toepassen op een of meer velden uit de query. De term berekeningen moet je ruim opvatten. Het gaat niet enkel om wiskundige berekeningen, maar ook om bewerkingen op datum- en tekstvelden.

De onderstaande voorbeelden zullen het nut en  het belang van berekende velden verduidelijken.

Op basis van de tabel tblArtikelsVerkoop maken we een query om het winstbedrag voor elk product te berekenen. Neem in de query de velden Naam artikel, Verkoopprijs artikel en Aankoopprijs artikel op. Om de winst te berekenen, moet je in een nieuwe kolom in de rij veld de volgende uitdrukking intikken.

[Verkoopprijs artikel]  - [Aankoopprijs artikel]

Als je op Enter drukt, zet Access de tekst Expr1 gevolgd door een dubbelpunt voor de uitdrukking. De betekenis van deze tekst zal verder duidelijk worden.

Merk op dat Expr1 die voor de uitdrukking stond de naam de kolom/veld krijgt. Je kan de tekst Expr1: veranderen in Winst:.

Als je later op basis van deze query een formulier of rapport maakt, verschijnt het berekende veld onder de naam Winst op dat formulier of rapport.

Mogelijke eigenschappen om in te stellen bij dit voorbeeld:

• de Beschrijving verschijnt in de statusbalk in het gegevensblad als het veld actief is;

• met Notatie, Aantal decimalen en Invoermasker kun je het uitzicht van het veld aanpassen. De eigenschap Aantal decimalen verschijnt pas als je de query al eens uitgevoerd hebt! Het resultaat van de berekening wordt gewoonlijk afgerond op twee cijfers na de komma (standaardinstelling bij het onderdeel Landinstellingen in het configuratiescherm van Windows).

• het bijschrift vervangt, net zoals in het tabelontwerp, de veldnaam in het gegevensblad. 

Bij een query gemaakt op basis van tabel tblPersoneelsleden zijn de velden Naam en Voornaam samengevoegd worden. Daarnaast moet je ook nog de velden Straat en nummer, Postnummer, Gemeente en Gsm-nummer weergeven. 

In het queryraster wort de uitdrukking Personeelslid: [Naam] & “ “ & [Voornaam] ingevoerd, waarbij Personeelslid de naam van het berekende veld is. Merk op dat je ook op basis van een berekend veld kunt sorteren.

We willen bij de lijst van persoonsleden dat alle woonplaatsen in hoofdletters verschijnt, zodat deze duidelijker op brieven geplaatst kunnen worden. We kunnen vorige query uitbreiden in queryraster en wijzigen bij het veld Gemeente de uitdrukking in Woonplaats: UCase([Gemeente]). (UCase staat voor het Engelse Uppercase)

Een parameterquery die personeelsleden toont die in een bepaald jaar geboren zijn. Daarvoor moet je uit het veld Geboortedatum het jaar afzonderen door in het queryraster de expressie Geboortejaar: Year([Geboortedatum]) in te tikken. 

Accces bevat enorm veel functies. Het grootste deel hiervan wordt vrij zelden gebruikt. Hier krijg je een overzicht van de meeste gebruikte functies. Omwille van de compatibiliteit met de programmeeromgeving (Microsoft Visual Basic) zijn de functienamen in het Engels.

Elke computer beschikt over een ingebouwde klok waarin de datum en tijd bijgehouden worden. Via de onderstaande functies kun je de systeem en/of –datum laten weergeven.

Now() Weergave van de systeemdatum en -tijd.

Date() Weergave van de systeemdatum.

Time() Weergave van de systeemtijd.

In sommige gevallen kan het nuttig zijn om de dag, de maand of het jaar uit een datum af te zonderen. Ook het uur, de minuut of de seconde kan uit een tijd geïsoleerd worden. Voor die bewerkingen beschikt Base over een aantal functies.

Year(datum) Het jaar van de opgegeven datum wordt weergegeven. 

Month(datum) De maand van de opgegeven datum wordt weergegeven. 

Day(datum) De dag van de opgegeven datum wordt weergegeven. 

Hour(time) Het uur van de opgegeven tijd wordt weergegeven.

Minute(time) De minuut van de opgegeven tijd wordt weergegeven.

Second(time) De seconde van de opgegeven tijd wordt weergegeven.

Voorbeeld

Het nut van zulke functies wordt duidelijk als je de volgende query (op basis van de tabel Ontleningen) bekijkt. De uitdrukkingen selecteren het jaar en de maand van de datum van de ontlening.

Jaar van ontlening: Year([Datum ontlening])

Maand van ontlening: Month([Datum ontlening])

Als je deze functies niet kent, zou je in de rij Criteria bij het veld Datum van ontlening het volgende intikken: 

Like “2009-09*“

Dat lijkt ook een goede oplossing, maar het heeft één nadeel: zodra de datumnotatie in het configuratiescherm van Windows verandert, is deze voorwaarde niet langer correct. Als je daarentegen de functies Year en Month gebruikt, speelt de datumnotatie geen enkele rol.

Tip: Je kunt deze functies ook gebruiken in combinatie met de functies Date, Time en Now om een deel van de systeemdatum of –tijd weer te geven.

Naast deze functies is er ook nog de functie DatePart die de mogelijkheden van de vorige functies combineert.

DatePart(interval;datum)

Deze functie geeft als resultaat een deel van de opgegeven datum. De syntaxis van deze functie zou door de volgende voorbeelden duidelijk moeten worden.

DatePart(“d”;#2009-12-25#) ==> 25

DatePart(“y”;#2009-12-25#) ==> 359

DatePart(“m”;#2009-12-25#) ==> 12

DatePart(“yyyy”;#2009-12-25#) ==> 2009

DatePart(“q”;#2009-12-25#) ==> 4

DatePart(“ww”;#2009-12-25#) ==> 52

In het argument interval wordt bepaald welk deel van de datum of tijd weergegeven moet worden. (interval / tijdseendheid)

yyyy jaar

q kwartaal

m maand

y dag van het jaar

d dag van de maand

ww week

h uur

m minuut

s seconde

Tip: Ook hier kun je de functie DatePart combineren met de functies Date, Time en Now.

Voorbeeld

Het volgende voorbeeld geeft een goed beeld van de mogelijkheden van de functie DatePart. Met de expressies worden het jaar en de maand uit de datum van ontlening afgezonderd. Deze query levert hetzelfde resultaat op als het vorige voorbeeld.

Jaar van ontlening: DatePart(“yyyy”; [Datum ontlening])

Maand van ontlening: DatePart(“m”;[Datum ontlening])

Soms kan het nodig zijn om een bepaalde periode bij een datum te tellen of ervan af te trekken. Ook het aantal dagen tussen twee datums moet soms berekend worden. Voor die bewerkingen heeft Access eveneens de geschikte functie.

DateAdd(interval;getal;datum)

Bekijk aandachtig de onderstaande  voorbeelden.

DateAdd(“yyyy”;1;#2009-01-30#) 2010-01-30

DateAdd(“yyyy”;3;#2008-02-29#) 2011-02-28

DateAdd(“m”;1;#2009-01-30#) 2009-02-28 

DateAdd(“m”;1;#2008-01-30#) 2008-02-29 

DateAdd(“m”;2;#2008-01-30#) 2008-03-30 

DateAdd(“m”;-2;#2009-01-31#) 2008-11-30

DateAdd(“d”;30;#2009-01-31#) 2009-03-02

DateAdd(“y”;30;#2009-01-31#) 2009-03-02

DateAdd(“ww”;1;#2009-01-31#) 2009-02-07

De mogelijkheden voor het argument interval zijn dezelfde als bij de functie DatePart. Bij het argument getal kan je zowel positieve als negatieve getallen invullen. Bij positieve getalen word het interval bij de datum gevoegd in het geval dat het getal negatief is wordt het interval van de datum afgetrokken.

Merk op wanneer de datum op het einde van de maand valt en je telt er een of meer maanden bij of je trekt er een of meer maanden van af, dat je dan de laatste dag van de maand krijgt als de maand minder dagen telt dan de maand van de oorspronkelijke datum.

De functie DateAdd wordt vaak gebruikt bij het opstellen van facturen om op basis van de factuurdatum de vervaldatum van de factuur te berekenen. Hieronder vind je twee mogelijke expressies. 2 voobeelden:

Vervaldatum: DateAdd(“m“;1;[Factuurdatum])     De vervaldatum is 1 maand na de factuurdatum

Vervaldatum: DateAdd(“m“;1;[Factuurdatum])     De vervaldatum is 30 dagen na factuurdatum

DateDiff(Interval; datum1;  datum2)

Deze functie berekent het verschil tussen twee datums. Met de parameter interval kun je opgeven hoe dat verschil uitgedrukt moet worden: in jaren,  weken, dagen  … Voor het argument interval zijn de dezelfde waarde toegelaten als hierboven.

DateDiff(“d“;#1967-08-13#;Date()) De vervaldatum is 1 maand na de factuurdatum

DateDiff(“s“;#2010-01-01#;#2010-09-30#) 23500800

DateDiff(“yyyy“;[Geboordatum];Date()) Deze expressie berekent de leeftijd in jaren

Een voorbeeld van het gebruik van de functie DateDiff vind je in de onderstaande query.

Via een expressie Aantal dagen ontleend: DateDiff(“d”; [Datum ontlening]; #2009-09-30#) wordt berekend hoeveel dagen de artikels op 30 september 2009 al ontleend  zijn. Met behulp van de criteria kan je tonen welke ontleende artikels nog niet teruggebracht zijn en die langer dan drie dagen ontleend zijn.

Naast de gewone rekenkundige bewerkingen met operatoren zoals +, -, / en * bestaan er in Access enkele speciale rekenkundige functies. De meeste van die functies worden vrij weinig gebruikt. Toch bespreken we er enkele.

Abs(getal)

Deze functie berekent de absolute waarde van een getal.

ABS(-10) 10

ABS(10) 10

Int(getal)

Deze functie berekent de integer van een getal. De integer is het deel van een getal voor de komma. De cijfers na de komma worden dus weggelaten (er wordt niet afgerond, maar afgekapt).

Int(10,4) 10

Int(10,9) 10

Int(-10,4) -11

Int(-10,9) -11

Round(getal;aantal decimalen)

Deze functie rondt een numerieke waarde af op een aantal cijfers na de komma. Als het aantal decimalen niet opgegeven is, wordt er afgerond op nul cijfers na de komma.

Round(3,3333;2) 3,33

Round(6,6666;2) 6,67

Round(6,6666) 7

Zoals de naam al laat vermoeden, kun je met tekstfuncties bepaalde bewerkingen op een veld van het type tekst uitvoeren.

Bij de voorbeelden van dit hoofdstuk heb je kunnen kennismaken met de functie UCase. Die functie zet een stukje tekst (=tekenreeks) in hoofdletters om. De tegenhanger is LCase.

UCase(tekensreeks) De tekst wordt in hoofdletters omgezet. 

LCase(tekenreeks) De tekst wordt in kleine letters omgezet (Engels; lowercase).

Voorbeelden

LCase("IN KLEINE LETTERS") in kleine letters                                                                     .

UCase("in hoofdletters") in hoofdletters                                                                      .

LCase([Voornaam]) het veld voornaam wordt in kleine letters geschreven .

UCase([Naam])   het veld naam wordt in hoofdletters geschreven            

Left(tekenreeks;aantal) Deze functie geeft als resultaat het eerste aantal tekens van de tekenreeks

Right(tekenreeks;aantal) Deze functie geeft als resultaat het laatste aantal tekens van de tekenreeks

Mid(tekenreeks;start;lengte) Deze functie isoleert een deel van de tekenreeks, beginnend vanaf een bepaalde positie en lengte.

Als de lengte niet opgegeven wordt, krijg je alle tekens vanaf een bepaalde positie.

Voorbeelden

Left(“ABCDEF”;3) ==> ABC

Left(“Voornaam”;1) ==> Geeft de eerste letter van de voornaam                                          .

Right(“ABCDEF”;3)     ==> DEF

Right([Personeelsnummer];3) ==> Geeft het volgnummer per vestiging                                               .

Mid(“ABCDEF”;2;3)     ==> BCD

Mid(“ABCDEF”;2) ==> BCDEF

Voorbeeld

Het personeelsnummer bestaat uit twee delen: het vestigingsnummer en het volgnummer per vestiging. Beide delen zijn van elkaar gescheiden door een slash (/). Met de volgende query worden die twee delen van elkaar gescheiden.

De expressie Vestiging: Left([Personeelsnummer];2) haal de code van de vestiging uit het veld Personeels-nummer. De andere expressie Vestiging: Right([Personeelsnummer];3) haalt de laatste drie tekens uit het personeelsnummer. Met de functie Mid kun je hetzelfde resultaat krijgen.

Space(getal)

Deze functie creëert een reeks spaties. Het aantal spaties wordt opgegeven in het argument getal.

“Test” & Space(5)  &  “test" ==> Test     test

String(getal;tekenreeks)

Met deze functie kun je een bepaald teken of een bepaalde tekenreeks herhalen. Het argument getal geeft het aantal herhalingen op, terwijl in het argument tekenreeks opgegeven wordt wat herhaald moet worden.

String(10;”@”) ==> @@@@@@@@@@

Length(tekenreeks)

Deze functie heeft de lengte van de tekenreeks als resultaat (spaties worden ook meegerekend).

Len(“Deze  tekst telt 26 tekens.”) ==> 26

Len([Naam])         ==> Geeft het aantal karakters van de familienaam           .

De functie Format wordt vooral gebuikt om de opmaak van een datum aan te passen. 

Voorbeelden

yyyy 4 jaar digits Format(#2007-01-01#; “yyyy“) = 2007

yy Laatste 2 digits van het jaar Format(#2007-01-01#; “yy“) = 07

y Hoeveelste dag van het jaar Format(#2009-09-01#; “y“) = 244

m of mm 1 of 2 digits van de maand Format(#2009-09-01#; “m“) = 9   of  Format(#2009-09-01#; “mm“) = 09

mmm Afkorting van de maand Format(#2009-09-01#; “mmm“) = sep

mmmm Naam van de maand Format(#2009-09-01#; “mmmm“) = September

w Week van het jaar Format(#2009-09-01#; “ww“) = 36

q Welk kwartaal de datum in valt Format(#2009-09-09#; “q“) = 3

d Dag van de maand Format(#2009-09-09#; “d“) = 9

dd Dag van de maand als 2 digits Format(#2009-09-09#; “dd“) = 09

ddd Afkorting van de weekdag Format(#2009-09-09#; “ddd“) = wo

dddd Dag van de week voluit in tekst Format(#2009-09-09#; “dddd“) = woensdag

ddddd Korte datumnotatie (volgens configuratiesccherm) Format(#2009-09-09#; “ddddd“)

ddddd Lange datumnotatie (volgens configuratiescherm) Format(#2009-09-09#; “dddddd“)

Je kunt ook notaties combineren.

Format(#2009-01-09#; “dddd d mmmm yyyy“) ==> vrijdag 9 januari 2009

Format(#2009-01-09#; “yyyy-mm-dd“) ==> 2009-01-09

Format(#2009-01-09#; “d-m-yy“) ==> 9-1-09

Format(#2009-01-09#; “d mmm yyyy“) ==> 9 jan 2009

IIf(voorwaarde;waar;onwaar)

De functie IIf bevat een logische test of voorwaarde. Naargelang die voorwaarde waar of onwaar is, verandert het resultaat. Deze functie is dezelfde als de logische functie ALS in een rekenblad (Excel, Spreadsheet, ...) .

IIf([Gehuwd]=Ja;”Gehuwd”;”Ongehuwd”)

IIf([Betaald]=Ja;”Betaald”;”Niet betaald”)

IIf([Geslacht]=”Man”;”De heer”;” IIf([Gehuwd]=Ja;”Mevrouw”;”Mejuffrouw”))

De laatste uitdrukking zal op basis van het geslacht en de huwelijks status aangeven welke aanspreking er gehanteerd moet worden.

Switch(voorwaarde1;expressie1;voorwaarde2;expressie2;… )

Deze functie evalueerteen lijst met voorwaarden en geeft de overeenkomstige waarde.

Switch([Statuut]=”B”;”Bediende”;[Statuut]=”T”;”Technicus”;[Statuut]=”V”;”Verkoper”;[Statuut]=”Z”;”Zaakvoerder”)

Switch([Geslacht]=”Man”;”De heer”;[Geslacht]=”Vrouw” And [Gehuwd]=Ja;”Mevrouw”;[Geslacht]=”Vrouw” And [Gehuwd]=Nee;”Mejuffrouw”)