Ok, who is good at SQL?

[Sam Summers]

OK, here is the scenario,

I have three Comboboxes - TradeName1 and TradeName2 and Level

My problem is this, i am still getting results of people who do not have for example TradeName2 and Level?
I have obviously not quite got the SQL correct?
I have just run the query again after taking all the 'Is Null' s out but it is still giving me spurious results?

***************************************************************************

SELECT EmpTrade.EmployeeID, Employee.FirstName, Employee.Surname, Employee.MobDate, Employee.DeMobDate, EmpTrade.TradeID, Trade.TradeName, Employee.National, Employee.Level

FROM Trade INNER JOIN (Employee INNER JOIN EmpTrade ON Employee.EmployeeID = EmpTrade.EmployeeID) ON Trade.TradeID = EmpTrade.TradeID

WHERE (((Employee.DeMobDate)<Date()) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1]) AND ((Employee.Level)=[Forms]![ViewJob]![Level]))

OR (((Employee.DeMobDate)<Date()) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1] And (EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName2]) AND ((Employee.Level)=[Forms]![ViewJob]![Level]))

OR (((Employee.DeMobDate) Is Null) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1]) AND ((Employee.Level)=[Forms]![ViewJob]![Level]))

OR (((Employee.DeMobDate) Is Null) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1] And (EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName2]) AND ((Employee.Level)=[Forms]![ViewJob]![Level]))

OR (((Employee.DeMobDate)<Date()) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1]) AND ((Employee.Level) Is Null))

OR (((Employee.DeMobDate)<Date()) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1] And (EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName2]) AND ((Employee.Level) Is Null))

OR (((Employee.DeMobDate) Is Null) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1]) AND ((Employee.Level) Is Null))

OR (((Employee.DeMobDate) Is Null) AND ((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1] And (EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName2]) AND ((Employee.Level) Is Null))

ORDER BY Employee.FirstName, Employee.National DESC;

**************************************************************************

Many thanks in advance

 

[pr2-eugin]

Simple hack, they might not be Null in the first place, See the table properties if you have set the Allow Zero Length, if it is set to Yes then there is a possibility they might be a Zero Length String.. So it will fail the Is Null test..

 

[jdraw]

What do you mean by "spurious"?

What do you want this query to do - in plain English?

Have you tried removing pieces of the query (some of the OR conditions) to ensure the basics are working?

 

[Sam Summers]

Thanks guys
They are set to zero length so i will play with that.

I basically want to either select:
1 Trade only
Or both Trades only
Or 1 Trade And Level
Or both Trades And Level

 

[jdraw]

And what about Employee.DeMobDate

Just curious - is this condition possible??
((EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName1] And (EmpTrade.TradeID)=[Forms]![ViewJob]![TradeName2])

I doubt that
EmpTrade.TradeID can be equal to 2 fields at the same time, but perhaps I'm missing something (It's early here)

 

[Sam Summers]

That is me trying to find personnel who are de-mobbed so that should be persons where the de-mob date is today or before today or where the date is blank

 

[jdraw]

Sam, I was posting/editing at same time as you. I added more info in the post.

 

[Sam Summers]

Ah thank you,

Yes that condition is as i have created a many to many relationship between Employee and Trade with the EmpTrade Table as 1 employee can have many trades and a Job can require many trades.

I have just found out that the Level part is not functioning i.e. i have three Levels I, II and III
When i select this in the ComboBox it is bringing up everyone with all levels?

I think this is where the problem is?

 

[jdraw]

Good luck.

 

[Sam Summers]

I'm stumped at the moment?

 

[pr2-eugin]

I know this is not what you want, but I would start from the basic again.. First include the fields you wish to see, then add one condition at a time, see if you are getting the required result, if yes proceed to the next condition.. One baby step at a time..

 

[Sam Summers]

Thanks Paul.

It appears to be the Value List of Level?

The query isn't seeing it for some reason?

The trade names are working fine.

 

[pr2-eugin]

So what is the control of Level in the ViewJob form? Normal Text box?

 

[Sam Summers]

I have nothing for the control? This may be it?
I will just try now

 

[Sam Summers]

I think i have the answer?

The ViewJob forms source is Job but the TradeName's and Level are related to personnel so i need to create a SubForm with them on it instead of what i have done is insert them into the ViewJob form?

Although the 2 TradeName combo's work fine?
Or maybe if i create a Lookup table with the Levels I, II and III in it and then set the Level ComboBox's Control to that?