Joker som kriterie i forespørgsel

where <felt> like '<input>%'
hvad der starter med <input> ;o)

det kan være at din iif sætningt fejler pgr. at der "jo" er et resultat? (true/false)

IIf("<test object>", "<true part>", "<false part>")

tak for din interesse a1

Det er i min <true part> at jeg skal have indsat en joker sådan at kriteriet for feltet i forespørgslen tillader alle poster at komme med.

Min false part virker som det skal, nemlig at der fremkommer et leverandørid som kriterie (filter).

Jeg har ikke læst indlæggene detaljeret og har ikke tid til det nu. Men en Like i en forespørgsel kan se således ud:

Like "*" & [text] & "*"

Tak for dit input mugs

Det er mærkeligt at nedenstående ikke virker for jeg kan godt se at når jeg bruger Like "*" i kriterie kommer alle med. Men jeg må nok prøve at løse det på en anden måde.


IIf((select leverandør from param where paramid = 1)="<Alle>";Like "*";(select leverandør from param where paramid = 1))

Hi Flemming
I'm rather puzzled as to what your trying to do. Is that a select statement yo have as the first and third parameters?

Yes

In short this is what i do:
If leverandørid = <Alle> then
I want to have Like "*" as my criteria in my query  else
If leverandørid Not = <Alle> then
I want to have leverandørid as my criteria in the query.

Hope it makes sense :-)

If I insert a specific leverandørid it works, for instance:

IIf((select leverandør from param where paramid = 1)="Alle";"XXXX";(select leverandør from param where paramid = 1))

but this not:
IIf((select leverandør from param where paramid = 1)="Alle";Like "*";(select leverandør from param where paramid = 1))

Am I right in saying that you want to get a value from another table which is NOT included in the SQL?

if Len(queryParameter) < 1 Then
sql ="select * from ...."
else
sql ="select * from ... where paramid=" & <value>
end if
? que

if Len(queryParameter) < 1 or queryParameter="<alle>" Then
sql ="select * from ...."
else
sql ="select * from ... where paramid=" & <value>
end if

"If I insert a specific leverandørid it works, for instance:" ...
Does it work for BOTH "Alle" and NOT "Alle"?

Yes the table param is not included in the query board....but in the sql statement it is included....

The false part of the IIF... works properly. But the tru part Like "*" gives zero records instead of what i expect namely all records

I'm off to see a bit of TV Flemming, if you dont get itsoleved this evening your welcome to send me your dB (or part of) and I'll take a look tomorrow morning.
ekspertenATsanthell.dk

AT=@

It works for Not = "Alle"

Thanks Terry

Have you sent the dB Flemming? I havent received anything.

Godday Terry.

I have just send you the db.

regards Flemming

Hi
Yes I've received it :o)

This took longer than expected :o)
Like IIf((select leverandør from param where paramid = 1)="Alle";"*";(select leverandør from param where paramid = 1))

Thanks very mouch... the solution looks beautyfull in its simplicity, but very hard to find... you are a true master :-)

Im very gratefull for your help Terry

Allso thanks to you a1 for your interest and inputs.

Give a "svar" and get your well deserved points

thanks for the points and kind comments :o)