Alternatief voor dlookup

Bijgaand een voorbeeldje in Access XP. Moet gewoon via versie 2007 kunnen worden gebruikt. Heb me meer gericht op de dynamische opmaak van het rapport, dan op de daadwerkelijke inhoud.

Ben druk bezig geweest met de voorbeelden, heb alleen nog wel een paar vragen:

Voor het voorbeeld van OctaFish.
-Graag zal ik in de kruistabelquery alle artikelen al willen opnemen, zodat mijn query in 1 keer compleet is.
momenteel zijn nog niet alle artikelen in gebruik.
Nu heb ik in de kruistabelquery de Joineigenschap al op 2 gezet maar krijg dan de volgende melding:
De SQL-instructie kan niet worden uitgevoerd omdat deze niet eenduidige outer joins bevat.
Is hier een oplossing voor?

-Is er een makkelijkere manier om de aantallen optellen op regelniveau (horizontaal) of kan het alleen door veldnaam1+veldnaam2+veldnaam3 enz?


Voor het voorbeeld van Harry.
-Ben begonnen om het dynamisch rapport te gebruiken om een rapport te maken waar ik de omzetten
per klant per maand kan zien.
Alleen wat zet je dan in de code neer?
Het stukje om de kolommen onzichtbaar te maken snap ik, alleen in het voorbeeld worden de artikelen opgehaald uit de qry verkopen.
Dit komt te vervallen, maar moet je hier wat anders voor neer zetten?

-En hoe kan je hier ook op regelnivea optellen (horizontaal)?

Een dynamische tabel heeft één nadeel: je krijgt alleen die kolommen te zien waarvoor je ook waarden hebt. Om altijd alle artikelen te zien, ook die waarvoor nog geen data beschikbaar is, kun je de eigenschap <Kolomkoppen> aanpassen van het veld dat je benoemt hebt als Kolomkop van de kruistabel. In de eigenschap <Kolomkoptekst> van dequery zet je dan alle artikelen die je hebt, op de volgende manier: "Schoenvork";"Hooimes";"Scheerlepel";"Koffiemuts";"Theefluit". Als je de SQL code van de query bekijkt, zie je deze regel:
In mijn opzet heb je twee queries: beginvoorraad en Eindvoorraad. In beide queries zet je deze kolomkoptekst. Het resultaat is dan dat je dus alle artikelen hebt, en daar kun je dan je rapport op baseren.

Een rapport gebaseerd op een kruistabelquey met vaste kolomkoppen gebruik je eigenlijk alleen als het "vaste" gegevens betreft. Als het aantal artikelen wijzigt of de omschrijving van het artikel, dam moet de query en het rapport worden aangepast. Dat is dan meer een adhoc oplossing dan een structurele.

Bij een dynamisch rapport, wordt de opbouw bepaald aan de hand van de beschikbare artikelen, ongeacht of ze wel of niet worden gebruikt. Je kunt zelf bepalen wat je wilt zien, alle artikelen, alleen die gegevens bevatten of, met wat extra programeren, een keuze uit de artikelen. Artikelen die worden toegevoegd of verwijderd, waarvan de omschrijving verandert, het wordt meteen verwerkt in het rapport. Het maken van een dynamisch rapport vereist wel wat meer inzicht.

Plaats een voorbeeldje met een query waarin de maandtotalen worden berekend. Dan zal ik kijken om daar een dynamisch rapport van te maken.

Ik begrijp dat een een kruistabelquery met vaste kolomkoppen een adhoc oplossing is.
Het voorbeeld van OctaFish lijkt voor mij het makkelijkst omdat er eigenlijk geen programmeer werk aan te pas komt.
Ook kan ik de velden in een doorlopend formulier opnemen, volgens mij kan je een kruistabel niet op een doorlopend formulier kwijt.

Snap wel iets van programmeren en door zoeken kom je een heel eind, maar denk toch dat ik daar voorlopig nog iets tekort in schiet of zie ik het nu erg zwart wit.

Heb een voorbeeldje gemaakt voor een dynamisch rapport voor jaar totalen.
De vraag is:
-Wil je naar de code kijken wat er nog aangevuld/gewijzigd worden
-Voor de kolomtotaal kan je de jaren optellen door =Nz([Veld1];0)+Nz([Veld2];0) enz.
maar werkt dit ook als er velden onzichtbaar zijn? of is daar ook een stukje vba code voor?
Kan het nu niet testen want het rapport werkt niet.

Ik zal er vanavond naar kijken, i.v.m. de access versie. Wat betreft je vraag: zolang een veld op een formulier of rapport staat, kun je hem gebruiken in formules; het maakt dus niet uit of hij zichtbaar is of niet, of wijzigbaar of niet.

Heb het voorbeeld bestand gewijzigd naar access 2003 indeling.
Misschien handiger voor meer mensen ook.

Dat scheelt

Een rapport gebaseerd op een kruistabel met vaste kolomkoppen is een prima oplossing, als de kolomkoppen (artikelen) niet veranderen.
Weet je zeker dat het bij die 12 blijft kies dan voor zo'n rapport.

Ik heb jouw voorbeeldje aangepast zodat op basis van een kruistabel zonder vaste kolomkoppen, het rapport dynamisch wordt gegenereerd.

Het aardige van jaartallen als kolomkoppen, is dat je die altijd kunt terugbrengen tot vaste kolomkoppen.
Zie voorbeeldjes:
rptBedragen4Jaar
rptBedragenPerJaarVast

Zoals je ziet, kun je (dynamische) rapporten op allerlei manieren maken. Ook het 'schuiven' met velden en/of labels kan. De 2 schermafbeeldingen die ik in een eerdere bijdrage gaf zijn daar voorbeelden van.

Super die voorbeelden.
Is het ook mogelijk om een uitgebreide selectie mee te geven aan het rapport van jaren, maanden en klanten uit een keuzelijst.

Heb een formulier als voorbeeld gemaakt wat ik bedoel.
Wil dan beginnen met het formulier, selectie maken van de gegevens en vervolgens het rapport openen.

Je wilt dus nu als kolomkop jaar-maand?
B.v. als je kiest als jaar: 2008 en 2010, en als maanden: januari, maart en juni, dat je dan als kolomkoppen krijgt:
2008-januari; 2008-maart; 2008-juni; 2010-januari; 2010-maart; 2010-juni

Wat ik er mee bedoel:
Jaren als kolomkop: bijvoorbeeld 2007 en 2009 en 2011
Maand: Als ik jan,feb,mrt selecteer dat het bedrag van deze maanden opgeteld wordt. Zo heb je een eerlijk vergelijk van jaren.
Klanten: Wil alleen klant 1,4,6 zien.

Die jaren is misschien een heel raar voorbeeld, maar dan kan ik hem misschien ook eens toepassen als ik met artikelen gaat werken.
Wat je eigenlijk al aangaf Harry in een eerdere post (keuze van artikelen).

Niet helemaal af nog, want je wilt de lijsten uiteraard ook snel leeg kunnen poetsen, maar gezien de tijd alvast een voorproefje...

Bijgaand een voorbeeldje met een uitgebreide selectie.
Heb gebruik gemaakt van tijdelijke tabellen. Daarmee blijft de overige code redelijk beperkt. Zoals zo vaak in Access het kan wellicht ook anders.
Hier en daar ook nog wat controles ingebouwd.
Kijk maar eens of je er wat aan hebt.

Zelf geef ik liever de voorkeur aan dynamische lijsten; het heeft bijvoorbeeld geen zin om selecties te (kunnen) maken als er voor de betreffende selectie geen records zijn. Iets dat in de versie van Harry zonder problemen is te doen. Ook ben ik geen voorstander van extra hulptabellen; zeker niet als je die niet nodig hebt.

Daarom heb ik gekozen voor dynamische keuzelijsten, en een aanpassing van de query die op het rapport gebruikt wordt. Hierbij is het mogelijk om selectie te maken uit elke combinatie van de drie keuzelijsten, en kun je kiezen of alle voorwaarden moeten worden gebruikt de <EN> variant, of een <OF> selectie. Uiteindelijk wordt de selectie aan de query toegevoegd.

Hierbij wordt het het rapport opgebouwd op basis van de informatie die in de query is opgeslagen. Door dus de query aan te passen, pas je automatisch ook het rapport aan. En de hoeveelheid code is best te overzien...

Kijk maar eens of je snapt hoe het werkt...

Heb wat te doen van het weekend geloof ik :shocked:.
Snap het redelijk allemaal wat er staat.

Nog een paar vraagjes, voordat ik er mee aan de slag ga:

Als ik nu bijvoorbeeld de keuzelijst maand vervang voor 2 datum tekstvakken, datum begin en datum eind.
Hoe krijg ik dit dan verwerkt in het filter?

In het tweede voorbeeld bestand gebruik je de functie "or" en in het derde (laatste) gebruik je de funtie "in".
Wat is het verschil tussen deze 2? kon op internet over "in" niet veel vinden.

Als ik op het formulier van de filters een doorlopend subformulier invoeg.
Het doorlopend subformulier uiteraard niet gebasseerd op een kruistabelquery.
Kan je dan het subformulier met deze code ook een requery uitvoeren op het subformulier.
Kort gezegd: Filters aanklikken en op de knop filter drukken dat dan het subformulier ververst wordt met de opgegeven filters.

Als je records wilt filteren, kun je dat op een aantal manieren doen. Je kunt ook een reeks opgeven: >=12 AND <=45 bijvoorbeeld. Dat vertaalt naar: ID>=12 AND ID>=<=45. Hetzelfde bereik je met: BETWEEN 12 AND 45. De SQL daarvan is: ID BETWEEN 12 AND 45. Verder kun je waarden ook direct kiezen, door in het criteria iets te typen als: 12 or 32 or 56 etc. In SQL wordt dat dan: ID=12 or ID=32 or ID=56. Dat werkt op zich prima, maar kan, bij lange veldnamen, de SQL string snel lang maken. In dat geval is het handiger om de getallen te 'verzamelen'. En dat doe je met het IN commando. Het criterium wordt dan: IN(12, 32, 56). En dat ziet er in SQL zo uit: ID IN(12, 32, 56).

Alle filters worden opgebouwd met de IN variant. Dat is dus gedaan om te voorkomen dat de string heel snel heel lang wordt: I.p.v. (Klantnummer In(1,4,5)) zou je dan een filter krijgen dat er zo uitziet: (Klantnummer =1 OR Klantnummer =4 OR Klantnummer = 5). Hoe meer klanten, hoe langer de string.... Ik was overigens wel op deze manier begonnen, maar ik vond de string dus heel lang worden, en heb hem omgebouwd. Dat gebeurt dus hier:


Deze vorm van programmeren werkt prima als je met objecten werkt die een standaardnaam+nummering hebben. Je kunt dan heel simpel door alle objecten lopen, en ze uitlezen. Omdat elke keuzelijst een eigen veld 'voedt' moet je daar ook iets op verzinnen. En dat gebeurt in de eigenschap <Extra Info> van de keuzelijst. In het Engels heet deze eigenschap TAG, en die wordt dus in de loop uitgelezen. Voor de keuzelijsten 2 en drie heb ik daar de formule gezet die resp. het jaar en de maand van het veld [factuurdatum] berekenen. Dat scheelt een hoop extra checks, want je kunt anders in de loop niet zien wanneer de functie YEAR en de functie MONTH moet worden gebruikt. Dus die formule staat al klaar in de eigenschap <Extra Info>. Ook zit er in de loop nog een check op getallen of tekst; een filter op tekst is namelijk anders als een filter op getallen.
De loop leest dus alle keuzelijsten uit, en zet elk filter in een eigen 'container' in een matrixvariabele. Afhankelijk van de selectie op de keuzelijsten is de matrix dus leeg, of gevuld.

Omdat het filter nogal variabel is, je hoeft in mijn variant niet alle keuzelijsten te gebruiken, moet er dus nog een extra stap worden gezet: het filter dat nu is opgeslagen in drie verschillende variabelen (sFilter(1), sFilter(2) en sFilter(3)) en leeg kan zijn, moet worden samengesteld tot één filter. Bovendien heb je de mogelijkheid om met AND of OR te bepalen hoe je wilt filteren. Bijvoorbeeld Alles van Klant 1, en Alles van de jaren 2009 en 2011. Dus een volgende stap is een loop die alles doorloopt, en aan elkaar knoopt.


Na de loop is er een nieuwe string gemaakt: sCriteria. Die is leeg, of gevuld, afhankelijk van de keuzes. Als er iets is geselecteerd, dan moet het filter worden uitgebreid met het woord WHERE. Zo niet, dan moet het filter leegblijven. Hiervoor is gekozen omdat de hele SQL van de query elke keer opnieuw wordt opgebouwd. Als je een query filtert, heb je het woord WHERE nodig, anders uiteraard niet.
De SQL voor een Kruistabel query is verder verbluffend simpel, als je de code bekijkt. Vind ik zelf althans!

Nu je laatste vraag: filteren op datums. Dat kan uiteraard ook wel; het filter zou ik dan maken op basis van twee tekstvelden die begin- en einddatum bevatten en BETWEEN. Het filter wordt dan: ([Factuurdatum] Between [StartDatum] AND [Einddatum])
Maar je kunt hem dan niet netjes meer gebruiken in combinatie met de jaarselectie, en eigenlijk ook niet de maandselectie. Dus ik zou die optie wel toevoegen op het formulier, maar met een knop schakelen tussen de verschillende selectie methodes. Dus kies je voor de keuzelijsten, dan zie je de datumselectievakjes niet, en kies je voor Begin- en Einddatum, dan zie je de keuzelijsten niet. Op basis daarvan kun je dan het filter op laten bouwen.

Ben terug van 3 weken vakantie en heb nu weer wat tijd om verder te puzzelen.

Met de rapporten lukt het wel aardig, beetje code geknipt en geplakt.
en ben inmiddels met wat doorlopende formulieren begonnen.

Om het rapport te filteren maak je (OctaFish) gebruik van:

Hiermee zet je de criteria in de query en vervolgens open je het rapport.


Dit wilde ik ook gebruiken om mijn subformulier te filteren.
Gegevens aanklikken waar op gefiltert moest worden, criteria in de query zetten
en vervolgens een requery uitvoeren van het subformulier.

Dit werkte niet. Hij zet wel netjes de criteria in de query, maar verversen of requery van het
subformulier werkte niet.
Pas als ik het formulier sluit en opent zag ik de gefilterde gegevens.

Heb het nu opgelost door het als filter mee te geven aan het formulier.


--------------
Alleen heb ik nu wat vragen/problemen:

-Op het formulier fDoorlopendFormulier wil ik ook op datum filteren. (between)
Heb nu van alles al geprobeerd, maar zonder resultaat.
Er staat al wat code in.

-Op het formulier FDoorlopendFormulierKruistabel wil ik ook op kleur filteren.
Alleen de kleur hoef ik niet te zien op het formulier.
De kleur is wel in de query qDoorlopendFormulierKruistabel opgenomen.
Als ik dan filter op kleur werkt het niet.

-Waarom werkt set qDF = ..enz niet op een doorlopend formulier?
De criteria wordt wel in de query gezet, maar na een requery zie je niet de gefilterde gegevens.

-Waarom geef je geen filter mee bij het openen van het rapport i.p.v set qDF.

-Kan je een keuzelijst vergroten door op een knop te klikken.
Bijvoorbeeld: De keuzelijst is nu 4 bij 4 en zie je 10 records.
Druk je op de knop wordt hij 20 bij 4 en kan je 50 records zien.
Hierdoor kan je dan wat makkelijker meerdere regels selecteren.

-------------

Ik weet bij de datum dat dan het maand en jaar filter niet zo netjes werken op deze manier.
Maar heb mijn vragen verwerkt in het huidige voorbeeld.
In mijn eigendatabase maak bijvoorbeeld het ene formulier gebruik van de filters klant, jaar, maand en het
andere formulier van klant, datumbegin, datum eind.


Alvast bedankt als iemand hier naar wil kijken!

Eerst maar een oplossing voor je datumprobleem:

Met een kleine aanpassing werkt hij als volgt:

-Waarom werkt set qDF = ..enz niet op een doorlopend formulier?

Klik om te vergroten...

Zou moeten werken; ik vermoed dat je de requery verkeerd doet. Maar omdat je de code hebt verwijderd, kan ik niet zien wat je gedaan hebt.

-Waarom geef je geen filter mee bij het openen van het rapport i.p.v set qDF.

Klik om te vergroten...

Kan ook. Beide opties werken
Een filter is zelfs wat veiliger in een MDE bestand.

-Kan je een keuzelijst vergroten door op een knop te klikken.

Klik om te vergroten...

Yep. De hoogte van een lijst is een eigenschap die je kunt aanpassen.
Met zoiets bijvoorbeeld:

Dit is de code die ik gebruik heb met set qDF:


De hoogte instellen met .listrows lukt niet met een keuzelijst met invoervak.
Heb wel me.keuzelijst.height gevonden, alleen moet ik nog ff zoeken hoe ik dan
de keuzelijst naar de voorgrond krijg.
De keuzelijst wordt nu wel groter, alleen krijg ik de tekst van de andere keuzelijsten die eronder zitten door de tekst heen.