CODEWITHSUNDEEP
sqlvbaselectwhere-clause
safron6
safron6

Reputation: 149

MS Access VBA, SQL Script: In(), <>, AND syntaxes

I have a script I need to show the genres and filter out nulls.

ReturnBooks = "Select * from [Books] where ([Type] IN ('B','K' ,'R' ,'C') AND [Type] <> 'No' AND [BookGenre] IS NOT NULL)) Order By [LName] ASC"

I need the BookGenre to be not null but the syntax returns nothing. Originally the code did not have that line and it did return some results. 

ReturnBooks = "Select * from [Books] where ([Type] IN ('B','K' ,'R' ,'C') AND [Type] <> 'No') Order By [LName] ASC"

Which returns the type of book.

How might I fit the IS NOT NULL in the script?

For example books table would return an author by last name depending on the genre. I also get a datatype mismatch from the script.

Upvotes: 1

Views: 52

Answers (2)

Developer
Developer

Reputation: 51

Let's check you where clause: ([Type] IN ('B','K' ,'R' ,'C') AND [Type] <> 'No') AND [BookGenre] IS NOT NULL))

If your [Type] in ('B','K' ,'R' ,'C') then it would never be 'No', so, the "And Type <> 'No'" is no use.

"IS NOT NULL" phare works well with mysql, sqlserver.

If you only just need the records that BookGenre is not null, just use this:

ReturnBooks = "SELECT * FROM [Books] WHERE [BookGenre] IS NOT NULL ORDER BY [LName] ASC"

If you need to filter the record with BookGenre is blank as well, try this:

ReturnBooks = "SELECT * FROM [Books] WHERE ([BookGenre] IS NOT NULL) AND ([BookGenre] <> '')  ORDER BY [LName] ASC"

Hoped this may help you.

Upvotes: 1

SergeyAn
SergeyAn

Reputation: 764

You have disbalance of parentheses in your query.

And you need to show some sample data from you table in your database.

Upvotes: 1

Related Questions