A little SQL trouble with WHERE

[Drager]

Hi,

I am using the following SQL code

SELECT [Network Overview].[_First Name]
      ,[Network Overview].[_Last Name], 
      ,[Network Overview].[_STARS ID], 
      ,[Network Overview].Job, 
      ,[Dealer Code to Site ID].[Main / Car Dealer Code], 
      ,[All Sites Data].[Trading Title], 
      ,[All Sites Data].[Phone Number], 
      ,[Network Overview].[Combined Standards Information]

FROM ([Dealer Code to Site ID] 
INNER JOIN [All Sites Data] ON [Dealer Code to Site ID].[Site ID] = [All Sites Data].[Site ID]) 
INNER JOIN [Network Overview] ON ([Network Overview].[_Dealer Code] = [Dealer Code to Site ID].[Main / Car Dealer Code]) AND ([Dealer Code to Site ID].[Main / Car Dealer Code] = [Network Overview].[_Dealer Code])

WHERE [Network Overview].[Combined Standards Information] <> "#REF!" ;

This is getting rid of all Combined Standards Information rows containing #REF! as required, but also a few other fields, which don't contain #REF!. Any idea why this is?

 

[namliam]

Possibly it is getting rid of blank fields?
try adding: Or [Network Overview].[Combined Standards Information] is null

Notes:
- Dont splash SQL on the forum, please format it and use the # tags (button in your post menu) to wrap the sql to add to readablity.
To give you an idea....

SELECT [Network Overview].[_First Name]
     , [Network Overview].[_Last Name]
     , [Network Overview].[_STARS ID]
...
FROM ([Dealer Code to Site ID] 
INNER JOIN [All Sites Data] ON [Dealer Code to Site ID].[Site ID] = [All Sites Data].[Site ID]) 
INNER JOIN ...
WHERE [Network Overview].[Combined Standards Information] <> "#REF!" ;

- I realize it is probably a bit late for this project, but for future projects please keep in mind a naming convention... To have a decent idea of what that may be, please review:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention

 

[Drager]

Thanks for the answer, hope my formatting is better now. The naming convention is inherited, sadly, although reading the post you linked is very helpful as I am (pretty obviously) new to this, so that will help with fixing what I have.

Sadly I don't think your suggested fix will work as the fields it is getting rid of are not blank, they contain quite a lot of text.

 

[jdraw]

Try a new query in the query design wizard --SQL View --

Try this to see if you get the correct result

SELECT [Network Overview].[_First Name]
      ,[Network Overview].[_Last Name], 
      ,[Network Overview].[_STARS ID], 
      ,[Network Overview].Job, 
      ,[Dealer Code to Site ID].[Main / Car Dealer Code], 
      ,[All Sites Data].[Trading Title], 
      ,[All Sites Data].[Phone Number], 
      ,[Network Overview].[Combined Standards Information]
FROM 
[Dealer Code to Site ID] 
 ,[All Sites Data] 
 ,[Network Overview]  
WHERE 
[Dealer Code to Site ID].[Site ID] = [All Sites Data].[Site ID] and
[Network Overview].[_Dealer Code] = [Dealer Code to Site ID].[Main / Car Dealer Code] AND 
[Dealer Code to Site ID].[Main / Car Dealer Code] = [Network Overview].[_Dealer Code] and
[Network Overview].[Combined Standards Information] Not Like "*#REF!*" ;

Good luck