Or Query AND NULL etc 2

[nickjar2]

I have a project table bound to a form. On this form are 6 bound fields relating to skills 1,2 etc with Skill 1 being the primary skill, skill 2 being the next best skill etc.
A project could have the following:
Field1 = "Access"
field2 = "VBA"
field3 = "Bus Obj"
field4 = null

I also have a people table holding info on the person and thier primary skills.

How can I write a query that will say show me all the people who any of the skills in fields 1 thru 4. At the mo, bewcause of the null value every record gets returned. I would normally do this a long winded way but because there are about 8 skill fields I was wondering if anyone knew of siomple method.

Hope this makes sense,

Nick

 

[jjonesal]

You could use the NZ function to return a zero when the value is null (thus getting rid of the NULL problem).....

Nz(fielda, 0)

returns 0 if fielda is null, otherwise returns the value of fielda


J. Jones
jjones@cybrtyme.com

 

[THoey]

I would suggest you look into a redesign. Take your project table and remove the skills. Create another table with the project ID and a skill field. For each project, you might have multiple records displaying the various skills needed.

Same with the people table. One with the person ID and other pertinent data and a second table with person ID and their skills. For each person, you might have multiple records displaying the various skills they have.

Doing this will make your queries easier to write and will eliminate people having NULL skills... Terry M. Hoey

 

[BillyKrow]

Use the "Is Not Null" function. This is easy in query design view. For each of the fields criteria add "Is Not Null" (No Quotes) Similar to first solution.