sql query for MS access Db

Question

User447704385 posted

I am tryin to get records where certain fields are empty, but the following sql does not give me correct result. Please suggest corrections.

Thanks for your help.

SELECT [specimen Type], [Year], [Lab Number]
From [PET Fish] where

ltrim(rtrim([Diagnosis])) = '' or
ltrim(rtrim([Results])) = '' or
ltrim(rtrim([Sign out Date])) = ''

It should return all records, but it returns none.

Answer 1

User1828997928 posted

Are you certain the values are empty strings?  Is it possible they are null?

Answer 2

User826222609 posted

You can use like this

SELECT [specimen Type], [Year], [Lab Number]
From [PET Fish] where

ltrim( rtrim( ISNULL( [Diagnosis], '')) = '' or
ltrim( rtrim( ISNULL( [Results], ''))) = '' or
ltrim( rtrim( ISNULL( [Sign out Date], ''))) = ''