Using Nz function

[scottian]

First off, im sorry for cross posting, ive just realized i used the wrong forum.

I have a query which im trying to get working. I have a form which displays the results but also has 3 unbound text boxes. the user types in the text box/boxes then the query uses this to filter the data. What im using is 'Like Nz()& "*"' which means they can put in part of the text. Everything works great for two of the boxes, but when i try all three i get strange results.

any help much appreciated

"My God! It's full of stars...

 

[traingamer]

Is the third box checking something other than a text field?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[scottian]

No,

The first box is 'Job Type' the second is 'Engineer Name' and the third is 'Job Number'. So the form opens with all the records and the user can filter. But i can only get it to work with Job Type and Engineer Name. But its more important that we can filter by the job number.

"My God! It's full of stars...

 

[randy700]

Please provide some code. If you show us what you have, we can help you fix it.

Randy

 

[lespaul]

Is JobNumber a numeric field in the table? And I bet Job Type and Engineer Name are string/text fields?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[scottian]

PHV has kindly provided this for me on my other post, suggesting the '+' instead of the '&'. Also it sticks extra brackets around the SQL, im not sure if this has an affect but just thought i'd mention it.

WHERE tblJobData.JobNumber Like Nz([Forms]![frmAllJobs]![JobNumber] + '*', tblJobData.JobNumber)
AND tblJobData.EngName Like Nz([Forms]![frmAllJobs]![EngName] + '*', tblJobData.EngName)
AND tblJobData.JobType Like Nz([Forms]![frmAllJobs]![JobType] + '*', tblJobData.JobType)

and LesPaul you are correct, is that the problem?

"My God! It's full of stars...