none
comboboxes requery does not work RRS feed

  • Question

  • i have a form where the records of 1 day are displayed

    if the user chooses a name (zoeknaam combobox) or a location (zoekloc combobox ) records are not filtered on the date , but only on name  and/or location

    for the dates it works perfect

    for the names also

    but if a location is chosen , i always get the records of the chosen date together with the location records . i've looked all day to my query , but i can not find the mistake and i'm getting really desperate

    SELECT QFeesten.FSNO, QFeesten.FSdatum, QFeesten.FStijd, QFeesten.FSklant, Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![zoeknaam],0) AS Expr1, QFeesten.FSadres, QFeesten.Klanten_1.KLgemeente, Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![lzoekloc],0) AS Expr2, QFeesten.Klanten.KLnaam, QFeesten.Klanten_1.KLnaam, QFeesten.Klanten_1.KLgemeente, QFeesten.FSomschr, QFeesten.FSmenutype, QFeesten.FSdatumaanvraag, QFeesten.FSKoks, QFeesten.fsfeestafdrukken, QFeesten.fsfeestgewijzigd

    FROM QFeesten

    WHERE (((QFeesten.FSdatum) Between [Formulieren]![CD]![NavigatieSubformulier].[Form]![datum1] And [Formulieren]![CD]![NavigatieSubformulier].[Form]![datum2]) AND ((Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![zoeknaam],0))=0) AND ((Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![lzoekloc],0))=0)) OR (((Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![zoeknaam],0))=0) AND ((QFeesten.FSadres)=[Formulieren]![CD]![NavigatieSubformulier].[Form]![zoekloc])) OR (((QFeesten.FSklant)=[Formulieren]![CD]![NavigatieSubformulier].[Form]![zoeknaam]) AND ((QFeesten.FSadres)=[Formulieren]![CD]![NavigatieSubformulier].[Form]![zoekloc])) OR (((QFeesten.FSklant)=[Formulieren]![CD]![NavigatieSubformulier].[Form]![zoeknaam]) AND ((Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![lzoekloc],0))=0))

    ORDER BY QFeesten.FSdatum DESC , QFeesten.FStijd DESC , QFeesten.FSadres;

    Sunday, May 29, 2016 6:59 PM

Answers

  • Doing this in the QBE grid is perhaps possible, but too complicated for my brain. I would write a dynamic sql statement, composing the WHERE clause based on user selections.

    -Tom. Microsoft Access MVP

    • Marked as answer by tekoko10 Monday, May 30, 2016 7:18 AM
    Sunday, May 29, 2016 10:22 PM

All replies

  • De criteria for FSdatum should be copied to the "Of" rows in the query grid.

    That way the datum criterion will always be applied.


    -Tom. Microsoft Access MVP

    Sunday, May 29, 2016 7:19 PM
  • but that i do not want, if location or name is chosen , then there must be no criterium on the date

    Sunday, May 29, 2016 7:33 PM
  • Doing this in the QBE grid is perhaps possible, but too complicated for my brain. I would write a dynamic sql statement, composing the WHERE clause based on user selections.

    -Tom. Microsoft Access MVP

    • Marked as answer by tekoko10 Monday, May 30, 2016 7:18 AM
    Sunday, May 29, 2016 10:22 PM
  • >>>but that i do not want, if location or name is chosen , then there must be no criterium on the date

    According to your description, I suggest that you could follow Tom van Stiphout's suggestion. In addition you could use iif function with where clause.

    For more information, click here to refer about IIf Function

    Monday, May 30, 2016 2:07 AM
  • Perhaps , that is a good idea I was inspired by the combodemo of Ken Sheridan https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
    Monday, May 30, 2016 4:53 AM
  • the problem is somewhere else , sorry

    if i click the combobox location, the records are found, but in my recordsource all come strange characters in the zl - field ... before i 've putted  Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![lzoekloc],0) , so the field became 0 and the date -records are included.

    i don't understand where the characters come from

    Monday, May 30, 2016 6:46 AM
  • >>>if i click the combobox location, the records are found, but in my recordsource all come strange characters in the zl - field ... before i 've putted  Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![lzoekloc],0) , so the field became 0 and the date -records are included.<<<

    According to your description, I suggest that you could use parameters in queries it is always best to explicitly declare them. Otherwise, you run the risk of misinterpretation.
     
    PARAMETERS [Formulieren]![CD]![NavigatieSubformulier].[Form]![lzoekloc] Integer;
    SELECT ......, Nz([Formulieren]![CD]![NavigatieSubformulier].[Form]![lzoekloc],0) AS Expr1
    FROM ......;
    Then see if that straightens out the issue.
    Tuesday, May 31, 2016 7:04 AM