Like "*" in query with if-then-else statement 1

[Pampers]

Hi Folks,
BUilding a little reporting application. Having trouble with a (totals)query that gets its criteria from a form. I can't get the Like "*" working in the If-then-else-statement in the criteria-field of the query builder. It returns nothing instead of all the records. Any ideas?

IIf([Forms]![frmOmzetRapportageMain]![Zoekcode]="Alle",Like "*","horeca")

Pampers
Keeping it simple can be complicated

 

[PHV]

In the criteria cell of the Query grid:
='horeca' OR [Forms]![frmOmzetRapportageMain]![Zoekcode]='Alle'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

Great PHV.
I used:

[Forms]![frmOmzetRapportageMain]![Zoekcode] Or [Forms]![frmOmzetRapportageMain]![Zoekcode]='Alle'

One thing tough, I don't understand it...

Is the logic: It is either the value in the field on the form or the field on the form is 'alle'? .... in which case nothing has to be in the criterium field?

Pampers
Keeping it simple can be complicated

 

[error123]

Hi everyone and Happy New Year!

I am trying to create a cool looking Alias, however it just doesn't want to happen.
I have a category field,a CAB field,Valve type of procedures(4 of them)field in the table.
I believe that whom created this query before used the logic, that if the Mitral Procedure field in the table cointained Replacement for the patient than that was aliased MVR. If Reconstruction was done without Annuloplasty it was aliased MVP. If CAB + Reconstruction without Annuloplasty was done it was aliased as CAB + MVR On the report the Mitral procedure clm header was Aliased as Category.
So it would look like:
Category
CAB + MVP
CAB + MVP
CAB + MVR
MVR
MVR <----depends on what type of procedure the patient
had, these are the aliases within the Category
(Aliased)CLM heading.
MVP

Someone else created these aliases and I am trying to remake it, but maybe another query was created before on valves than that was used with the CAB field.

Would you be so kind to let me know the best way of doing this from Access?

This shows how the actual category field looks in the table:

SELECT Demographics.Last_Name, Event_STS.Discharge_Date, Event_STS.Surgery_Date, STS_ValveSurgery.Mitral_Procedure AS CATEGORY, Event_STS.CABG AS CABWASDONE
FROM (Demographics INNER JOIN Event_STS ON Demographics.SS_Patient_ID = Event_STS.SS_Patient_ID) INNER JOIN (STS_252 INNER JOIN STS_ValveSurgery ON STS_252.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) ON (Event_STS.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) AND (Event_STS.SS_Event_STS_ID = STS_252.SS_Event_STS_ID)
WHERE (((Event_STS.Discharge_Date) Between [Start_Date] And [End_Date]) AND ((STS_ValveSurgery.Mitral_Procedure) Not Like "No") AND ((Event_STS.Valve)=1)) OR (((STS_ValveSurgery.Mitral_Procedure) Like "Replacemen AS MVR")) OR (((STS_ValveSurgery.Mitral_Procedure) Like "Reconstruction with Annuloplasty AS MVP"))
ORDER BY Demographics.Last_Name, Event_STS.Surgery_Date;

This is the 4 valve types:
SELECT Demographics.Last_Name, Event_STS.Discharge_Date, Event_STS.Surgery_Date, STS_ValveSurgery.Mitral_Procedure, Event_STS.CABG, STS_ValveSurgery.Aortic_Procedure, STS_ValveSurgery.Tricuspid_Procedure, STS_ValveSurgery.Pulmonic_Procedure, Event_STS.Valve
FROM ((Demographics INNER JOIN Event_STS ON Demographics.SS_Patient_ID = Event_STS.SS_Patient_ID) INNER JOIN (STS_252 INNER JOIN STS_ValveSurgery ON STS_252.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) ON (Event_STS.SS_Event_STS_ID = STS_252.SS_Event_STS_ID) AND (Event_STS.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID)) INNER JOIN STS_PostOp ON STS_252.SS_Event_STS_ID = STS_PostOp.SS_Event_STS_ID
WHERE (((Event_STS.Discharge_Date) Between [Start_Date] And [End_Date]) AND ((STS_ValveSurgery.Mitral_Procedure) Not Like "No") AND ((Event_STS.CABG) Not Like 0) AND ((Event_STS.Valve)=1))
ORDER BY Demographics.Last_Name, Event_STS.Surgery_Date;

And this is my humble code for looking for the Mitral Procedure clm Aliased as Category and within the CLM the fields aliased as CAB+MVR;CAB+MVP;MVP;MVR.

SELECT Demographics.Last_Name, Event_STS.Discharge_Date, Event_STS.Surgery_Date, STS_ValveSurgery.Mitral_Procedure, Event_STS.CABG, STS_252.RoboticTechAssist, STS_ValveSurgery.Aortic_Procedure, STS_ValveSurgery.Tricuspid_Procedure, STS_ValveSurgery.Pulmonic_Procedure, Event_STS.Valve
FROM ((Demographics INNER JOIN Event_STS ON Demographics.SS_Patient_ID = Event_STS.SS_Patient_ID) INNER JOIN (STS_252 INNER JOIN STS_ValveSurgery ON STS_252.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) ON (Event_STS.SS_Event_STS_ID = STS_252.SS_Event_STS_ID) AND (Event_STS.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID)) INNER JOIN STS_PostOp ON STS_252.SS_Event_STS_ID = STS_PostOp.SS_Event_STS_ID
WHERE (((Event_STS.Discharge_Date) Between [Start_Date] And [End_Date]) AND ((STS_ValveSurgery.Mitral_Procedure) Not Like "No") AND ((Event_STS.Valve)=1))
ORDER BY Demographics.Last_Name, Event_STS.Surgery_Date;

Thank you for your time on this.