Why is this SQL code not working properly?

J

Joanna

I am running a parameter query from a form. I want to retrieve an entry that
matches all the criteria selected by the user (although the user does not
have to select all the criteria). Right now I have 12 criteria.

The code is given below. My problem is that strangely, the first three
criteria (University, Degree, and graduation year) are not working! All the
others seem to be working just fine...

What's happening here? Any insight will be greatly appreciated!

==========
SELECT students. ... (all the columns)...
FROM students
WHERE (((students.University ) Like
nz(Forms!mainForm!university,"*")) And ((students.Degree ) Like
nz(Forms!mainForm!degree,"*")) And ((students.Graduation_Year ) Like
nz(Forms!mainForm!gradYear1,"*"))And ((students.Current_Occupation) Like
nz(Forms!mainForm!currOcc,"*")) And ((students.Position_Seeking) Like
nz(Forms!mainForm!posSeek,"*")) And ((students.Field_of_study ) Like
nz(Forms!mainForm!fieldStud,"*")) And ((students.Majors ) Like
nz("*" & Forms!mainForm!majors & "*","*")) And ((students.Preferred_Industry)
Like nz("*" & Forms!mainForm!prefInd & "*","*")) And
((students.Previous_Experience) Like nz(Forms!mainForm!prevExp,"*")) And
((students.Languages ) Like nz("*" & Forms!mainForm!languages &
"*","*")) And ((students.Nationalities ) Like nz("*" &
Forms!mainForm!nationalities & "*","*")) And ((students.Resume_Keywords )
Like nz("*" & Forms!mainForm!resume & "*","*")));
 
A

Alex Ivanov

My first suggestion would be that
Forms!mainForm!university evaluates to "" rather than NULL in your SQL and
therefore nz does not work as expected.
Try to change format string of your textbox to something like @ that should
convert empty string to NULL.
 
Top