query doesn't understand the * in combobox 1

[train2]

Hello forms fans,
I'm having probs with a combo box on a form.

I've added * at the top of a combo box with a Union query, so that users can select all teachers. I have a linked combobox which should return all related classes for that teacher - it works very nicely for every single teacher, but not for the wildcard.... it returns nothing.

Can anyone help? I vaguely remember an earlier thread with this question, but of course, can't find it now. I'm sure it's a small problem.

jr

 

[wtmckown]

So if the wildcard is specified in the combo box with the union query as a record source then the second combo box should list all classes for all teachers? Test for the value of the first combo box in the second combo box recordsource. Use a test like "=iif(cbo1="*",Like'*',cbo1) in the field criteria.


Here's an example of how I might use a union query as a combo box recordsource.


Select -1,&quot;<<All Facilities>>&quot; From tblFacility Union (SELECT tblFacility.iFacilityNumber, tblFacility.sDescription FROM tblFacility ORDER BY tblFacility.iFacilityNumber);

In the second combo box I would use the immediate if statement to test for a value of -1 in the first combo box and if true then not limit the records in the select to any facility number.

 

[train2]

Thanks for your reply - I've tried the IIF statement in the criteria for the query on which the second combo box is based. I've tried every possible way of adding the IIF statement as a criteria, but it still doesn't work. I've taken the * out of the combo box and replaced it with &quot;<All>&quot; simply so that it couldn't get confused. But to no avail.

so my criteria is now something like this;

IIf([forms]![F_Teacherschoice]![cboTeacher]=&quot;<All>&quot;,([T_Teacher].[T_Id]) Like '*',[forms]![F_Teacherschoice]![cboTeacher])


It added the
([T_Teacher].[T_Id])

bit all by istelf, but it's not working anyway, whether i leave it in, take it out, change to &quot; instead of ' etc.

I tried to add a new field and set that as the search criteria based on the first combo box, but that didn't help either.

Any more ideas?

it's almost weekend.

 

[wtmckown]

can you paste in the union query?

 

[train2]

This is the union query - I've since changed it to All with a space in front.

SELECT T_Teacher.T_Id, T_Teacher.T_Name FROM T_Teacher UNION SELECT &quot; All&quot; As T_Id, &quot; All&quot; As T_Name FROM T_Teacher ORDER BY T_Name;


Then the query for the 2nd combo box is:
SELECT T_Class.C_Id
FROM T_Teacher INNER JOIN T_Class ON T_Teacher.T_Id = T_Class.T_Id
WHERE (((T_Class.S_Id)<>&quot;Ga&quot; And (T_Class.S_Id)<>&quot;Se&quot AND ((T_Teacher.T_Id)=IIf([forms]![F_Teacherschoice]![cboTeacher]=&quot; All&quot;,([T_Teacher].[T_Id]) Like &quot;*&quot;,[forms]![F_Teacherschoice]![cboTeacher])));


But it is now officially the weekend, and I'm off to the pub with the little hair I have left after tearing it out. Don't loose any sleep over it - I'll look in again tomorrow morning.

Thanks for your interest - I'll be refreshed in the morning.
train2

 

[wtmckown]

Keep in mind that for union queries to work properly data types must match field for field. Your table T_Teacher has field T_Teacher.T_ID which corresponds to a text field in the first position in the second half of your union query. Is T_Teacher.T_Id a text field? If it is numeric then change query to:
SELECT T_Teacher.T_Id, T_Teacher.T_Name FROM T_Teacher UNION (SELECT -1, &quot;All&quot; As T_Name FROM T_Teacher ORDER BY T_Name);

If -1 is a valid or possible value in the table then use some number that is impossible to be used in the table. The combo box with the union query should be bound to the first column and you can test for a value of -1 in the second combo box recordsource query. Hope this helps you.

 

[train2]

I've now tried everything out - the T_ID is a text field so I've tried listing &quot;All&quot; and variations on this theme, also I tried the -1... it also worked.

My problem is just getting the 2nd combo box query to understand * as a criterion. I tested my 2nd query by substituting &quot;AC&quot; for Like &quot;*&quot; - and it worked. So the test in the IIF statement is working. It's just getting the syntax correct for the wildcard...

like '*'
like &quot;*&quot;

etc. I've tried and tried and can't get it working.
Any ideas how i can get round problem? i thought about checking for NOT ISNULL or something like that.

jr

 

[train2]

There you go... I knew I'd read about this before. I just found an old thread.... apparently the solution suggested was....


try adding a new column to your query as follows:

in the FIELD box type: IIf(IsNull([Forms]![form1]![Combo])=False,[YourFieldNameHere]=[Forms]![form1]![Combo],[YourFieldNameHere] Like &quot;*&quot

unclick the SHOW box and set your CRITERIA to <>False


I'm just off to try it out.

 

[train2]

And hey presto it works.

So I added a new column with the following....

IIf([forms]![F_Teacherschoice].[cboteacher]=&quot;<All>&quot;,[T_Id] Like &quot;*&quot;,[T_Id]=[forms]![F_teacherschoice]![cboTeacher])

I set Show to FALSE, and set the criteria to TRUE.


Works like a dream. I'd still be interested to know why the other method doesn't work though - logically it makes sense, but in practice i can't implement it.

thanks for you time - and I hope this helps others too.
jr