Good evening

I am trying to write an sql statement but its not producing the results I want.

This is the statement I am trying to use.


SELECT [Table Jan 1999].[Bkg No], [Table Jan 1999].[Reporting To], [Table Jan 1999].[Start Date], [Table Jan 1999].[End Date], [Table Jan 1999].[Reason for Booking], [Table Jan 1999].[Temp Name], [Table Jan 1999].[Cost Code], [Table Jan 1999].[Assignment Job Title], [Table Jan 1999].Department, [Table Jan 1999].Status
FROM [Table Jan 1999]
WHERE ((([Table Jan 1999].[End Date]) Is Null)
AND (([Table Jan 1999].[Cost Code])="DE600149"
Or ([Table Jan 1999].[Cost Code])="DG6022820" Or ([Table Jan 1999].[Cost Code])="600414") OR ((([Table Jan 1999].[Cost Code])="DG6022820"))
OR ((([Table Jan 1999].[Cost Code])="600415")) OR ((([Table Jan 1999].[Cost Code])="DG6022821")) OR ((([Table Jan 1999].[Cost Code])="DG6022832"))
OR ((([Table Jan 1999].[Cost Code])="600421")) OR ((([Table Jan 1999].[Cost Code])="600432")) OR ((([Table Jan 1999].[Cost Code])="600433"))
OR ((([Table Jan 1999].[Cost Code])="600435")) OR ((([Table Jan 1999].[Cost Code])="600576")) OR ((([Table Jan 1999].[Cost Code])="600713"))
OR ((([Table Jan 1999].[Cost Code])="600724")) OR ((([Table Jan 1999].[Cost Code])="600965")) OR ((([Table Jan 1999].[Cost Code])="602716"))
OR ((([Table Jan 1999].[Cost Code])="602741")) OR ((([Table Jan 1999].[Cost Code])="602744")) OR ((([Table Jan 1999].[Cost Code])="602788"))
OR ((([Table Jan 1999].[Cost Code])="602789")) OR ((([Table Jan 1999].[Cost Code])="602810")) OR ((([Table Jan 1999].[Cost Code])="602811"))
OR ((([Table Jan 1999].[Cost Code])="602821")) OR ((([Table Jan 1999].[Cost Code])="602835")) OR ((([Table Jan 1999].[Cost Code])="602949"))
OR ((([Table Jan 1999].[Cost Code])="602969")) OR ((([Table Jan 1999].[Cost Code])="602980")) OR ((([Table Jan 1999].[Cost Code])="602988"))
OR ((([Table Jan 1999].[Cost Code])="602991")) OR ((([Table Jan 1999].[Cost Code])="603005")) OR ((([Table Jan 1999].[Cost Code])="500DW"))
OR ((([Table Jan 1999].[Cost Code])="500KP")) OR ((([Table Jan 1999].[Cost Code])="500LG")) OR ((([Table Jan 1999].[Cost Code])="501TE"))
OR ((([Table Jan 1999].[Cost Code])="502M3")) OR ((([Table Jan 1999].[Cost Code])="502Q0")) OR ((([Table Jan 1999].[Cost Code])="504KU"))
OR ((([Table Jan 1999].[Cost Code])="505BG")) OR ((([Table Jan 1999].[Cost Code])="506J1")) OR ((([Table Jan 1999].[Cost Code])="506J2"))
OR ((([Table Jan 1999].[Cost Code])="506J4")) OR ((([Table Jan 1999].[Cost Code])="506J5")) OR ((([Table Jan 1999].[Cost Code])="507FQ"))
OR ((([Table Jan 1999].[Cost Code])="507GC")) OR ((([Table Jan 1999].[Cost Code])="507Q4")) OR ((([Table Jan 1999].[Cost Code])="508SR"))
OR ((([Table Jan 1999].[Cost Code])="509Q5")) OR ((([Table Jan 1999].[Cost Code])="509RM")) OR ((([Table Jan 1999].[Cost Code])="DG2820")));


My problem is with this part
WHERE ((([Table Jan 1999].[End Date]) Is Null)

I thought by having Is Null would give me only results that have no value, But....when I run the query I am getting results with values.


Can anyone tell me what I am doing wrong?


TIA

Problem is incorrect nesting of your AND and ORS.
I wouldn't be using OR anyway, instead use the IN operator:

SELECT [Table Jan 1999].[Bkg No], [Table Jan 1999].[Reporting To], [Table Jan 1999].[Start Date], [Table Jan 1999].[End Date], [Table Jan 1999].[Reason for Booking], [Table Jan 1999].[Temp Name], [Table Jan 1999].[Cost Code], [Table Jan 1999].[Assignment Job Title], [Table Jan 1999].Department, [Table Jan 1999].Status
FROM [Table Jan 1999]
WHERE [Table Jan 1999].[End Date] Is Null
AND [Table Jan 1999].[Cost Code]) IN ("DE600149", et cetera)

Another remark, have you by any chance got tables similar to this table, for instance a table called [Table Jan 2000]?
If so, don't, you're violating the principles of normalization and creating an inadequate and undesired database structure.

RV

Hi RV

Thanks for your help I have that working very nicely now :)

Regarding your other remark..... I have just started a new job for this company and they have asked me to have a look at their database and tidy it up a little...........
You dont have to worry about there been a table Jan 2000 as they only have 1 table with about 45 columns!!!! No normalization here!! Should be a pretty good project to work on tho


Thanks again!