Return all records if IIF statement is False

[Badvoc]

HI
I have this in the criteria of a query field name priority
IIf([Forms]![frmTRinput]![Text103]=[priority],[forms]![frmtrinput]![text103],[priority])

[Forms]![frmTRinput]![Text103] will be any number 1-7 depending on user input.
Priority is in a table and might have any combination of numbers from 1-7 depending on tasks allocated.

What Im trying to achieve is, if a match is found show only matching records, if no match show all records that don't.
Heres the sql of the whole query

SELECT tbl_P2E_Priorities.Priority, Max(tbl_P2E_Priorities.Finish) AS MaxOfFinish, tbl_P2E_Priorities.[Resource Name] AS ID1, IIf([priority]<=[Forms]![frmTRinput]![Text103],[finish],Date()) AS Expr1
FROM tbl_P2E_Priorities
GROUP BY tbl_P2E_Priorities.Priority, tbl_P2E_Priorities.[Resource Name], IIf([priority]<=[Forms]![frmTRinput]![Text103],[finish],Date())
HAVING (((tbl_P2E_Priorities.Priority)=IIf([Forms]![frmTRinput]![Text103]=[priority],[forms]![frmtrinput]![text103],[priority])) AND ((tbl_P2E_Priorities.[Resource Name])="frank clarke"))
ORDER BY Max(tbl_P2E_Priorities.Finish);

I thought I’d find the answer in Dudleys post Return All Records if IIF statement is False (read: ["*"] in IIF) but Im get lost at the dlookup part

 

[pr2-eugin]

Try using Like instead of =.. something like..

[B]...[/B] (tbl_P2E_Priorities.Priority) LIKE IIf([Forms]![frmTRinput]![Text103] IN [priority],[forms]![frmtrinput]![text103],'*')

 

[pr2-eugin]

I am not sure what [priority] is.. is it a number between 1 and 7 ?? if so.. rewrite the code as..

... (tbl_P2E_Priorities.Priority) LIKE IIf([Forms]![frmTRinput]![Text103]>=1 AND [Forms]![frmTRinput]![Text103]<=7,[forms]![frmtrinput]![text103],'*')

 

[Badvoc]

I am not sure what [priority] is.. is it a number between 1 and 7 ?? if so.. rewrite the code as..

... (tbl_P2E_Priorities.Priority) LIKE IIf([Forms]![frmTRinput]![Text103]>=1 AND [Forms]![frmTRinput]![Text103]<=7,[forms]![frmtrinput]![text103],'*')

this returns no records.
if [Forms]![frmTRinput]![Text103] is 2 and theres no 2 in the priority field.
If [Forms]![frmTRinput]![Text103]=2 and theres no 2 in the priority field I want all records in the filed to show

 

[pr2-eugin]

Try code in post #2 with a = instead of IN

 

[Badvoc]

when I try that the true part returns all records when theres a match
very frustrating these queries

 

[Ari]

Hi

try

HAVING (((tbl_P2E_Priorities.Priority=[Forms]![frmTRinput]![Text103]) OR ([Forms]![frmTRinput]![Text103] is null)) AND (tbl_P2E_Priorities.[Resource Name]="frank clarke"))

Or

HAVING (((tbl_P2E_Priorities.Priority=[Forms]![frmTRinput]![Text103]) OR ((tbl_P2E_Priorities.Priority) like "*")) AND (tbl_P2E_Priorities.[Resource Name]="frank clarke"))

 

[Badvoc]

Hi Ari thanks
both only half work, first one works if the number is in list (priority field) but shows no records if its not a match.
second show all records if the number is in the list and all records if its not.

If the number is in the list, filter only for the number, if not, show the list.

 

[pr2-eugin]

Could you give us some sample data.. or a stripped down version of your DB??

 

[Badvoc]

im working on one now

 

[Badvoc]

heres a stripped down db

 

[Badvoc]

added .mdb file

 

[Badvoc]

Im still stumped on this

 

[Ari]

What are the expected results for the query, for the following values ​​text103?

1?

4?

null?

10?

Looking forward

 

[Badvoc]

in the case of frank clarke,
1 would return all in list 4,4,5 & 6
4 would return just the 4s
null will never happen nor will 10

 

[Ari]

Following modifications.

The field text105 was necessary to return query 4,4,5 & 6.

 

[Badvoc]

thanks but i need it to work in the query,
I cant believe ACCESS finds this so hard