Combo box filter subform with wildcards

[mcinnes01]

Hi,

I am quite new with access and I am trying to filter a subform. The idea is that multiple combo boxes will apply filters to the subform, then the user will select from the subform and this choice will be used on the form.

Unfortunately I am not quite there yet... I have the subform working so when no selection is made, the whole table is displayed in the list. I also have it so that I can filter by job title (combo52) however I have set the list in the combo box purposely to remove duplicates and also to remove the unique identifiers at the end of the name. I will explain...

The database system this pulls data from and which will ultimately be updated by, requires that anyone who has an authoriser position, must have their own unique job code in order for automated authorisation workflows to work correctly. E.g. if you had 2 people on the same job code and a subordinate made an expenses claim, it could end up going to the wrong person to authorise.

So if there were 10 administrators in a department and 1 on them authorised expenses for the others I have renamed their job as follows.

Administrator (the other 9 people)
Administrator AA (the authoriser)

This continues on where there may be groups within a division

Administrator AB
Administrator AC etc

What I have done on the jobs table is make a column called "List Jobs". This holds just the job title, so they would all be "Administrator" and I use this with distinct in combo 52.

Currently when I select administrator from the combo box, it looks up from the database view of the jobs table (this doesn't have my List jobs reference column). So it only displays jobs with "Administrator" as the title and non of the "Administrator AA, AB etc.

SELECT TMC_PSDET.POSITION, TMC_PSDET.TITLE, TMC_PSDET.SEGM, TMC_PSDET.DIVISION, TMC_PSDET.CONTRACT_TYPE, TMC_PSDET.STANDARD_HRS, TMC_PSDET.WEEKS_YR, TMC_PSDET.ANALYSIS_GRP, TMC_PSDET.COST_GRP, TMC_PSDET.LGPS_ELI, IIf(IsNull([TMC_PSDET]![LEA_NO]),"Y","") AS [TP Eligible]
FROM TMC_PSDET
WHERE (((TMC_PSDET.TITLE)=IIf(IsNull([Forms]![HR]![Combo52]),"*",[Forms]![HR]![Combo52])) AND ((TMC_PSDET.EFFECTIVE)=#12/21/1951#)) OR (((TMC_PSDET.EFFECTIVE)=#12/21/1951#) AND (([Forms]![HR]![Combo52]) Is Null));

I tried using wild cards e.g.

SELECT TMC_PSDET.POSITION, TMC_PSDET.TITLE, TMC_PSDET.SEGM, TMC_PSDET.DIVISION, TMC_PSDET.CONTRACT_TYPE, TMC_PSDET.STANDARD_HRS, TMC_PSDET.WEEKS_YR, TMC_PSDET.ANALYSIS_GRP, TMC_PSDET.COST_GRP, TMC_PSDET.LGPS_ELI, IIf(IsNull([TMC_PSDET]![LEA_NO]),"Y","") AS [TP Eligible]
FROM TMC_PSDET
WHERE (((TMC_PSDET.TITLE)=IIf(IsNull([Forms]![HR]![Combo52]),"*",([TMC_PSDET].[TITLE]) Like [Forms]![HR]![Combo52] & "*")) AND ((TMC_PSDET.EFFECTIVE)=#12/21/1951#)) OR (((TMC_PSDET.EFFECTIVE)=#12/21/1951#) AND (([Forms]![HR]![Combo52]) Is Null));

Also my attempts to filter division by combo1 and contract_type by combo53 have completely failed. I only want the filter to apply if a selection is made, so if it is blank it will display all from that field. I think I have achieved this on the jobs option with the use of iif(isnull(forms!hr!comboX),"*",.... so I guess a continuation of this will work can you advice me what I need to do in order to achieve what I am trying to do?


Thanks in advance,


Andy

 

[mcinnes01]

Ok so I have the filter working on for job title now, so if I select administrator from combo52 it displays all variations of administrator

Administrator
Administrator AA
Administrator AB
etc etc

This is the Query:

SELECT TMC_PSDET.TITLE, TMC_PSDET.DIVISION, TMC_PSDET.CONTRACT_TYPE, TMC_PSDET.STANDARD_HRS, TMC_PSDET.WEEKS_YR, TMC_PSDET.ANALYSIS_GRP, TMC_PSDET.COST_GRP, TMC_PSDET.LGPS_ELI, IIf(IsNull([TMC_PSDET]![LEA_NO]),"Y","") AS [TP Eligible]
FROM TMC_PSDET
WHERE (((TMC_PSDET.TITLE)=IIf(IsNull([Forms]![HR]![Combo52]),"*",([TMC_PSDET].[TITLE]) Like [Forms]![HR]![Combo52] & "*")) AND ((TMC_PSDET.EFFECTIVE)=#12/21/1951#)) OR (((TMC_PSDET.EFFECTIVE)=#12/21/1951#) AND (([Forms]![HR]![Combo52]) Is Null));

Now I want to try and get other combo boxes to filter the subform as well, still on the same principal that if they are empty it returns "*".

I tried this by replicating what I did in title, however my selection from combo1 made no change in terms of filtering the subform.

Any ideas?

Also I want to select a result from the subform and use this, but I don't want this choice until the user clicks one, e.g. it doesn't auto select the record it is on after the requery.

Hope you can help,

Andy