[SQL] Rottige Where voorwaarde

[Masch]

Jullie weten nog wel eens wat en ik zit hiermee helemaal vast dus zouden jullie mij hier even op een goed spoor willen zetten. Ik ben hier al een paar uur mee bezig en ik krijg het niet voor elkaar. Het gaat om onderstaande query;

code:

SELECT *
FROM Offerte AS O
WHERE O.[Datum in] Between IIf(IsNull(veld1), #1-1-1900# Or Null,veld1)
And iif(IsNull(veld2),#31-12-2999# Or Null, veld2);

Alles werkt goed in deze query, tenzij een van de velden [forms]![rapportage]![bdi] of [edi] leeg is. Dan moet hij dus (logischerwijs) alles pakken tussen 1-1-1900 en alle lege velden, maar dit werkt dus niet, hij geeft geen resultaat. Als ik echter 'Or Null' weghaal geeft hij wel het resultaat, maar de lege velden natuurlijk niet. Kan dit geheel wel of niet in SQL (daar begin ik nu aan te twijfelen) of hoe zouden jullie dit aanpakken..

Als jullie meer (achtergrond)info willen laat het dan ook even weten.

[ Voor 7% gewijzigd door Masch op 02-07-2003 10:07 ]

[justmental]

Je bedoelt dat 'datum in' ook null kan zijn?
Dan zou het zoiets worden:

code:

SELECT *
FROM Offerte AS O
WHERE O.[Datum in] Between IIf(IsNull([forms]![rapportage]![bdi]),
#1-1-1900#,[forms]![rapportage]![bdi]) 
And iif(IsNull([Forms]![Rapportage]![edi]),#31-12-2999#,
[Forms]![Rapportage]![edi])
Or O.[Datum in] IS NULL;

[Verwijderd]

Achter de eerste komma van IIF() staat wat voor waarde er gegeven moet worden als het statement waar is. Ik snap dan ook niet wat je met die "Or" wilt...

[OZ-Gump]

Waarom zou je er dan, als het veld ook leeg kan zijn, niet het volgende van maken...?

SQL:

  SELECT *
  FROM Offerte AS O
  WHERE O.[Datum in] Between
            Iif(IsNull(bdi), #01-01-1900#, bdi) 
        And Iif(IsNull(edi), #31-12-2999#, edi) 
  OR O.[datum in] is null;

[tip]
Probeer de volgende keer je query een beetje beter uit te lijnen, en gebruik eventueel fictieve namen in plaats van een verwijzing naar een edit van bijna een regel lang
[/tip]

[Masch]

Ja die had ik ook al gevonden, maar dan geeft hij ten alle tijden de lege velden mee in het resultaat. Hij moet dit echter alleen doen als de IIf voorwaarde waar is (dus als er niks is ingevuld op het formulier).
Het is namelijk voor het creeeren van een rapport. Gebruikers moeten kunnen aangeven dat ze offertes willen opzoeken tussen bepaalde data die ze aangeven op een formulier. Vullen ze echter niks in, dan moeten alle offertes worden getoond.

[justmental]

Masch schreef op 02 July 2003 @ 10:02:
Ja die had ik ook al gevonden, maar dan geeft hij ten alle tijden de lege velden mee in het resultaat. Hij moet dit echter alleen doen als de IIf voorwaarde waar is (dus als er niks is ingevuld op het formulier).
Het is namelijk voor het creeeren van een rapport. Gebruikers moeten kunnen aangeven dat ze offertes willen opzoeken tussen bepaalde data die ze aangeven op een formulier. Vullen ze echter niks in, dan moeten alle offertes worden getoond.

or (datum is null and parameter1 is null and parameter 2 is null) dan

[OZ-Gump]

Als ik eerlijk ben vind ik het een beetje viezig dat ze uberhaupt een offerte in kunnen geven zonder datum

Je zou in je selectiescherm (want ik neem aan dat je die eentje gemaakt hebt) in code kunnen controleren welke query je moet gebruiken. Dus of het veld is ingevuld of niet. Dan kunnen ook je Iif-jes er uit laten, die zijn dan niet nuttig meer. Niet zo heel moeilijk op te zetten: bij het klikken op 'Toon rapport' kijk je in de datum-velden. Als er iets is ingevuld, pak je de between-query, anders de Is Null-query (of dus eigenlijk een query zonder where op de datum)

Edit:
Jouw eerste oplossing resulteert in iets als where datum between #01-01-1900# OR Null And #31-12-2999# OR Null, dat vindt 'ie natuurlijk niet tof...

[ Voor 16% gewijzigd door OZ-Gump op 02-07-2003 10:09 ]

[Masch]

OZ-Gump schreef op 02 July 2003 @ 10:07:
Als ik eerlijk ben vind ik het een beetje viezig dat ze uberhaupt een offerte in kunnen geven zonder datum

lol
En terecht dat je dat vindt! In dit veld kan dat ook niet. Echter kan het wel met de velden datum uit, datum resultaat, etc. Ik was aan het uittesten en daarbij kwam dit aan het licht. Om mijn query uit te testen doe ik het even zo. (ikzelf kan de velden wel leeg halen)

Je zou in je selectiescherm (want ik neem aan dat je die eentje gemaakt hebt) in code kunnen controleren welke query je moet gebruiken. Dus of het veld is ingevuld of niet. Dan kunnen ook je Iif-jes er uit laten, die zijn dan niet nuttig meer. Niet zo heel moeilijk op te zetten: bij het klikken op 'Toon rapport' kijk je in de datum-velden. Als er iets is ingevuld, pak je de between-query, anders de Is Null-query (of dus eigenlijk een query zonder where op de datum)

Er zijn in totaal een 10 tal verschillende criteria waarvan 4 datum velden (ingeschteven, verstuurd, resultaat en geldigheid) en 2 prijsvelden (offertetotaal en opdrachtotaal). Hierdoor moet ik dan zoveel query's maken dat het geheel onoverzichterlijk gaat worden, denk ik.

justmental schreef op 02 juli 2003 @ 10:04:
[...]

or (datum is null and parameter1 is null and parameter 2 is null) dan

Deze snap ik niet helemaal..

Wat zou ik dan bij parameter1 en 2 in moeten vullen? Hij moet zoals ik aangaf, alleen null velden meegeven indien het veld in het formulier leeg is gelaten.

[ Voor 14% gewijzigd door Masch op 02-07-2003 10:17 ]

[justmental]

Masch schreef op 02 juli 2003 @ 10:14:
Deze snap ik niet helemaal..

Wat zou ik dan bij parameter1 en 2 in moeten vullen? Hij moet zoals ik aangaf, alleen null velden meegeven indien het veld in het formulier leeg is gelaten.

Deze krengen:
[Forms]![Rapportage]![bdi]
[Forms]![Rapportage]![edi]

[OZ-Gump]

Er zijn in totaal een 10 tal verschillende criteria waarvan 4 datum velden (ingeschteven, verstuurd, resultaat en geldigheid) en 2 prijsvelden (offertetotaal en opdrachtotaal). Hierdoor moet ik dan zoveel query's maken dat het geheel onoverzichterlijk gaat worden, denk ik.

Eens... een andere oplossing is dan dat je door je verschillende opties heenloopt in code en zo een dynamische query opbouwt:

code:

  strSQL := 'Select * from offerte';
  strWhere := ' where VeldA = 18';

  If Param1.Ingevuld then
    strWhere := strWhere + ' AND VeldX = ' + Param1;

  If Param2.Ingevuld then
    strWhere := strWhere + ' AND VeldY = ' + Param2;

  ....

  If (Param9.Ingevuld AND Param10.Ingevuld) then
    strWhere := strWhere + ' AND VeldZ between ' + Param9 + ' AND ' Param10;

  strQuery := strSQL + strWhere;

[ Voor 29% gewijzigd door OZ-Gump op 02-07-2003 10:23 ]

[Masch]

http://members.home.nl/m.a.schipper/rapport.jpg

Even ter info een plaatje van het formulier. Ik ga nu weer ff kijken of ik met jullie tips verder kom. Alvast bedankt.
Ik blijf het topic wel in de gaten houden, dus tips zijn altijd welkom!

[OZ-Gump]

gheh gheh... even ter info n.a.v. je screenie:
criteria is het meervoud van criterium. Criteria's

Enne... als ik dit zo bekijk zou ik dit met een dynamische query oplossen zoals ik die in mijn vorige post heb gezet. Het lijkt me iig niet verstandig te proberen dit in 1 query te vangen...

[Masch]

OZ-Gump schreef op 02 July 2003 @ 10:27:
gheh gheh... even ter info n.a.v. je screenie:
criteria is het meervoud van criterium. Criteria's ,

Auuww....

Gelukkig is het nog niet in gebruik genomen

Enne... als ik dit zo bekijk zou ik dit met een dynamische query oplossen zoals ik die in mijn vorige post heb gezet. Het lijkt me iig niet verstandig te proberen dit in 1 query te vangen...

Zou echter wel het mooiste zijn...

Ik ga het proberen op te lossen met de dynamische query.

[Masch]

Het is opgelost! Nu alleen nog verder uitwerken.

OZ-Gump, bedankt voor de info , dit is denk ik een hele mooie oplossing. Zie de oplossing hieronder;

code:

Dim db As Database
Dim objQuery As QueryDef
Dim strSQL, strWhere, strQuery As String

Set db = CurrentDb
strSQL = "Select * from offerte AS O"
strWhere = ""

'Datum in criteria's bepalen
If Not IsNull(Me![BDI]) And Not IsNull(Me![EDI]) Then
    strWhere = " where O.[datum in] between " & Me![BDI] & "and " & Me![EDI] & " "
ElseIf IsNull(Me![BDI]) And Not IsNull(Me![EDI]) Then
    strWhere = " where O.[datum in] between #1-1-1900# and " & Me![EDI] & " "
ElseIf Not IsNull(Me![BDI]) And IsNull(Me![EDI]) Then
    strWhere = " where O.[datum in] between #" & Me![BDI] & "# and #31-12-2999# "
End If

strQuery = strSQL + strWhere

Set objQuery = db.CreateQueryDef("Rapportage", strQuery)

[OZ-Gump]

Ziet er netjes uit Masch....
Mocht je echter nog meer van dit soort criteria willen toevoegen, dan moet je even bedenken waar je de 'where' gaat plaatsen in je strWhere, omdat je anders te vaak of te weinig where in je string hebt staan. If you get my drift...

Succes met verder uitwerken

[Masch]

OZ-Gump schreef op 02 July 2003 @ 11:37:
Ziet er netjes uit Masch....
Mocht je echter nog meer van dit soort criteria willen toevoegen, dan moet je even bedenken waar je de 'where' gaat plaatsen in je strWhere, omdat je anders te vaak of te weinig where in je string hebt staan. If you get my drift...

Succes met verder uitwerken

Ik snap wat je bedoeld. Dat is idd nog een bottleneck. Maar daar heb ik inmiddels al genoeg ervaring mee , dus het moet gaan lukken. Zoniet, dan weet ik je te vinden

[Masch]

Nog een laatste post met ook de laatste bottleneck d'r uit. (tenminste daar ga ik nu nog ff van uit )

code:

Dim db As Database
Dim objQuery As QueryDef
Dim intWhere As Boolean
Dim strSQL, strWhere, strQuery As String

Set db = CurrentDb
strSQL = "Select * from offerte AS O"
strWhere = " "
intWhere = False

'Datum in criteria's bepalen
If intWhere = False Then
    If Not IsNull(Me![BDV]) Or Not IsNull(Me![EDV]) Then
        strWhere = " where "
        intWhere = True
    Else
        'niks doen want de velden zijn leeg
    End If
ElseIf Not IsNull(Me![BDV]) Or Not IsNull(Me![EDV]) Then
    strWhere = strWhere + " And "
End If
If Not IsNull(Me![BDI]) And Not IsNull(Me![EDI]) Then
    strWhere = strWhere + "O.[datum in] between " & Me![BDI] & " and " & Me![EDI] & " "
ElseIf IsNull(Me![BDI]) And Not IsNull(Me![EDI]) Then
    strWhere = strWhere + "O.[datum in] between #1-1-1900# and " & Me![EDI] & " "
ElseIf Not IsNull(Me![BDI]) And IsNull(Me![EDI]) Then
    strWhere = strWhere + "O.[datum in] between #" & Me![BDI] & "# and #31-12-2999# "
End If

'Datum uit criteria's bepalen
If intWhere = False Then
    If Not IsNull(Me![BDV]) Or Not IsNull(Me![EDV]) Then
        strWhere = " where "
        intWhere = True
    Else
        'niks doen want de velden zijn leeg
    End If
ElseIf Not IsNull(Me![BDV]) Or Not IsNull(Me![EDV]) Then
    strWhere = strWhere + "And "
End If
If Not IsNull(Me![BDV]) And Not IsNull(Me![EDV]) Then
    strWhere = strWhere + "O.[datum uit] between " & Me![BDV] & " and " & Me![EDV] & " "
ElseIf IsNull(Me![BDV]) And Not IsNull(Me![EDV]) Then
    strWhere = strWhere + "O.[datum uit] between #1-1-1900# and " & Me![EDV] & " "
ElseIf Not IsNull(Me![BDV]) And IsNull(Me![EDV]) Then
    strWhere = strWhere + "O.[datum uit] between #" & Me![BDV] & "# and #31-12-2999# "
End If



strQuery = strSQL + strWhere


Set objQuery = db.CreateQueryDef("Rapportage", strQuery)

[ Voor 21% gewijzigd door Masch op 02-07-2003 12:23 ]

[Masch]

Nog een vraag waar ik nu niet uit kom. Ik heb de strQuery inmiddels uitgebreid met een strOrderBy, waarin ik de ORDER BY sectie zet van de query. Wat hierin komt te staan wordt bepaald door wat er in een keuzelijst met invoervak by (lijst met velden van tabel), genaamd sort by, staat.

code:

'Sortering bepalen
strOrderBy = "ORDER BY o.[" & Me![sort by] & "] "

'Query samenvoegen en aanmaken
strQuery = strSQL + strWhere + strOrderBy
Set objQuery = db.CreateQueryDef("Rapportage", strQuery)

Dit lijkt allemaal goed te werken, maar het rapport wordt niet goed gesorteerd. En wat ik ook probeer, ik krijg het niet voor elkaar om het rapport gesorteerd te krijgen op basis van het rapport. Wat heb ik allemaal geprobeerd? zie hieronder;

- bovenstaande manier (lijkt mij de juiste??)
- via eigenschappen rapport, Sorteren aan (zowel op ja las nee gezet) en Sorteren op (rechtstreeks laten verwijzen naar formulier)
- via 'sorteren en groeperen' - rechtstreeks laten verwijzen naar formulier

Alle drie methodes moeten volgens mij (en de help, etc) kunnen werken, maar dat doen ze dus niet. Het gekke is dat als ik er zelf een veld inzet (dus geen verwijzing) dat de sortering wel goed werkt , maar ik krijg het dus niet voor elkaar dat de gebruiker het mag gaan bepalen en dat zou stiekem wel heel mooi zijn

[whoami]

Begin eens met het begin en print je query eens naar het scherm ipv hem uit te voeren.
Zo kan je zien hoe hij er echt uit ziet, en kan je misschien zelf al zien wat er fout gaat.

[Boss]

Laat me raden: in je rapport maak je gebruik van groeperen.

Bij gegroepeerde rapporten wordt het stuk 'order by' compleet genegeerd.

In plaats daarvan moet je in het schermpje waar je de groepeer gegevens opgeeft het veld opgeven waar je op wilt sorteren. Alleen hoef je dan dus geen groep kop- en voettekst te maken voor dat deel.

Wil je dan nog kunnen sorteren op verschillende velden, zet dan in je SQL een veld 'SORTEER' wat je steeds laat wisselen.
Dus bijv:
... veld1 AS SORTEER ...
... veld2 AS SORTEER ...

[OZ-Gump]

Hmmzz...
Houd je er wel rekening mee dat, als het veld waar je op sorteert een string is, je hier ook quotes omheen moet zetten? En als het een veldnaam is die een spatie bevat, zet er dan [spekhaken] omheen...

[Masch]

@whoami; Had ik al gedaan. Hier wordt wel goed gesorteerd

Boss schreef op 10 July 2003 @ 14:55:
Laat me raden: in je rapport maak je gebruik van groeperen.

Bij gegroepeerde rapporten wordt het stuk 'order by' compleet genegeerd.

In plaats daarvan moet je in het schermpje waar je de groepeer gegevens opgeeft het veld opgeven waar je op wilt sorteren. Alleen hoef je dan dus geen groep kop- en voettekst te maken voor dat deel.

hmmmzz... Als ik het goed begrijp (en dat waag ik te betwijfelen ), bedoel je dat ik dus in het subrapport het sorteerveld moet invullen??

Dit heb ik gedaan en dat werkt. Als ik echter daar een verwijzing naar het rapport zet, dan werkt het niet meer

Wil je dan nog kunnen sorteren op verschillende velden, zet dan in je SQL een veld 'SORTEER' wat je steeds laat wisselen.
Dus bijv:
... veld1 AS SORTEER ...
... veld2 AS SORTEER ...

Dit is niet eens nodig, er hoeft maar op een veld te worden geselecteerd. Deze moet echter wel ingevuld worden op het formulier, daarom kan ik geen rechtsreekse verwijzing in het subformulier zetten.

OZ-Gump schreef op 10 July 2003 @ 14:57:
Hmmzz...
Houd je er wel rekening mee dat, als het veld waar je op sorteert een string is, je hier ook quotes omheen moet zetten? En als het een veldnaam is die een spatie bevat, zet er dan [spekhaken] omheen...

o.[" & Me![sort by] & "] " Let op de notering

[ Voor 15% gewijzigd door Masch op 10-07-2003 15:05 ]

[OZ-Gump]

* OZ-Gump voelt zich een beetje dom Maar ja, het is dan ook al vrijdagochtend voor me: morgenmiddag vrij!

Sorry Masch. De eerste opmerking slaat nergens op, de tweede had je al gedaan.

[ Voor 30% gewijzigd door OZ-Gump op 10-07-2003 15:07 ]

[Masch]

OZ-Gump schreef op 02 July 2003 @ 10:27:
gheh gheh... even ter info n.a.v. je screenie:
criteria is het meervoud van criterium. Criteria's

offtopic:
[schaammode]ach iedereen heeft zo zijn momenten [/schaammode]

Maar goed, ik snap d'r nog steeds niks van. Ik ga nog maar ff zoeken op de o zo gebruiksvriendelijke (NOT ) MSDN website

[ Voor 20% gewijzigd door Masch op 10-07-2003 15:15 ]

[Boss]

Niet in het subrapport, maar in het groepeer-scherm.
Deze:



Of heb je die niet gebruikt?

Ik weet niet wat voor soort rapport je maakt, maar vaak kan je groeperen gebruiken waar je nu (misschien) een sub rapport hebt.

Of denk ik nu dat jij iets anders denkt

[Masch]

Inderdaad, die heb ik (ook) gebruikt.

En dat werkt dus niet als ik een rechtstreekse verwijzing naar het formulier aanmaak. Dus bij veld/ expressie forms!rapportage![sort by] invul

edit

opgelost; dit werkt (bij mij) als enigste;

code:

OrderByOn = True
OrderBy = "" & Forms!rapportage![sort by] & " DESC

Dit bij het onopen event van het rapport zetten.

[ Voor 37% gewijzigd door Masch op 14-07-2003 12:04 . Reden: oplossing gepost ]