select

[Wallie]

Hi,

I've created a query which is used for searching a table.
So when you have the searchform you can enter a number fields the search is based on. The problem is when a field is empty, I dont want to select the records inwhich the empty field occurs, but the all the records...

I hope it's clear....;-)

This is what I had in the query:
SELECT Id, name
FROM table
WHERE (((tblBedrijven.Id1)=IIf(IsNull([Forms]![form1]![listboxId1]),[Id1],[Forms]![form1]![listboxId1])) AND .... {the rest}

But it wont work this way....

 

[Jimbob]

How many fields are there?

 

[Wallie]

6 --> 4 comboboxes and 2 textfields.....

I was wondering if the function IsNull() also works on ComboBoxes, maybe that it the problem (??)
(When none of the values from a combobox is selected it should return all records --> it should ignore this field in the search)

[This message has been edited by Wallie (edited 04-04-2002).]

 

[RV]

Try this construction:

SELECT Id, name
FROM tblBedrijven
WHERE (tblBedrijven.Id1)
=[Forms]![formname]![listboxname]
OR [Forms]![formname]![listboxname] Is Null)
AND .... {the rest}

BTW, are you Dutch (tblBedrijven...").

Suc6,

RV

 

[Wallie]

Ben inderdaad nederlands, hoe kwam je daar zo ineens bij ;-).
Ervan uitgaande dat jij ook nls bent:
Wat je me hebt gegeven werkt toch niet helemaal lekker. Als er nu niks wordt geselecteerd haalt ie alle records op (perfect), maar als je wel een waarde selecteerd haalt ie nog steeds alle records op :-(
iig bedankt....

In english :
That won't work. When the combobox is left empty all records are shown (good) but when a value has been selected from the combobox it still shows all records
Thanx anyways....

 

[RV]

You'll have to refresh the combobox.
Try this post:
http://www.access-programmers.co.uk/ubb/Forum1/HTML/004714.html

Otherwise, search the forum for "Refresh combobox".

Greetings from a sunny Holland,

RV


[This message has been edited by RV (edited 04-04-2002).]