Trying to output a search form result into a listbox 1

[chaft]

I have a table of academic information

i have a search form comprised of textboxes, comboboxes where people select their options from what is available and the output is put into a listbox ( i think it looks nicer than a datasheet)

The boxes are are linked via the criteria section on the query.

For example put a name into the 'first name' box and then click 'search' produces the result in the listbox where all names matching that name are displayed.

That works.... but.


when I get onto the seventh or so textbox or combobox the search form stops working.

for several fields i can use the format:

Like IIf(IsNull(Forms!FormName![ControlName]), _
"*",[Forms]![FormName]![ControlName])

that is until i get to seventh field or so for example looking for a 'COURSE SUPERVISOR' that if the box is blank it displays nothing instead of displaying ALL records..I want to see all records if the boxes are blank. Empty box? then i want to see all data.

The difficulty is that unlike name, surname, date fields and coursename information the 'COURSE SUPERVISOR' is sometimes a field that isn't filled in all the time. So while all records have a name, not all records have a course supervisor.

Now I've tried to the limit of my knowledge and 'trial and error' skill I can't get the COURSE SUPERVISOR to display correctly without hiding fields when no input is it's box.

I've used

http://support.microsoft.com/kb/209645

as a guide but I can't seem to work out how to get it to work.

i can get what i want if i put course supervisor in a separate query on it's own and put code to it.But I can't seem to put it on the same query as the rest. I can't get it to work with fields that have data on every record without hiding information..

I will display the sql info if requested but do bear in mind that it is massive as there are seven or eight fields with lines of criteria written to them.

I hope someone can help me.. My extravagent work around is going to take weeks at my rate.

 

[Remou]

I think you are looking for:

Or Is Null

Alternatively use this format for Course Supervisor in the query so it is never null:

Trim([Course Supervisor] & "")

 

[chaft]

I don't want to make the course supervisor never null as it is natural that sometimes some course supervisors won't be assigned to some students.


I just want to filter out those with a course supervisor that matches the name in the box ([forms]![zz]![acads])

see the yousendit link for the jpeg of the criteria table i have. The picture shows the query with criteria for the the 'title expected to gain' field. The form works well at this point. Select the correct title and the listbox filters, if left blank it shows all titles..

but...

I don't know what to write under the criteria section for 'COURSE SUPERVISOR'. If I write the same code under Course supervisor as i had with Title expected to gain the form would not longer show correctly.

what do i need to put in the criteria in order so that i can search by course supervisor and title expected to gain at the same time? Which we also display all records if the boxes are left blank.

 

[chaft]

incidently the current sql of this simplified query is

SELECT isstudent.FirstName, isstudent.IsStudent, isstudent.Surname, isstudent.IsOrdinand, isstudent.AcademicCourseSupervisor
FROM isstudent
WHERE (((isstudent.IsStudent)="Yes") AND ((isstudent.TitleExpectedToGain)=[forms]![zz]![searchtitle])) OR (((isstudent.IsStudent)="Yes") AND (([forms]![zz]![searchtitle]) Is Null));


this query works but I need to find a way to get course supervisor working in the same way as searchtitle

 

[Remou]

I was not suggesting removing nulls in the table, merely formatting to remove nulls in the query. If you change the Field line for Course Supervisor to:

Trim([Course Supervisor] & "")

and set the criteria to:

Like Forms!Forms!FormName![ControlName] & "*"

I think it should work.

Another possiblity is to build the query string in code, which can then be used to create a query or as the recordsource of a form or report. Here is a FAQ:

http://tek-tips.com/faqs.cfm?fid=5497

 

[chaft]

I don't understand how trim works but it does..I thought trim just removed letters and spaces..so I'm confused as to why it works. but Thanks it does what I need for now..tomorrow i'm going to put the rest of the criterias in the table and give it a go. But thanks for giving me a good night sleep tonight remou

 

[Remou]

Trim is there to ensure that the formatted field contains only a zero-length string, thereby simplifing the comparison.