Need WHERE criteria syntax to load Listview through SQL

[boxboxbox]

Currently my records are in a continuous form set up for easy scrolling. I can requery the records with keystrokes from textboxes above. This is too slow, however, and I'd like to move to a listview, which seems to work very fast and nice.

The form is based on a query that combines four other queries. Each subquery has SQL similar to:

WHERE (((Book.Author) Like [forms]![sbinventory]![txtsearchauthor] & "*")) OR ((([forms]![sbinventory]![txtsearchauthor]) Is Null)) OR (((Book.Author) Like " " & [forms]![sbinventory]![txtsearchauthor] & " "));

The main query pulls these the searched field from these into one unit...

I need help with the SQL syntax for the Listview, specifically on WHERE.

My form is called SBInventory; I will have four text boxes, called TxtSearchAuthor, TxtSearchTitle, TxtSearchPublisher, TxtSearchID. They all need to be referenced, as I might have a string in the publisher and the title field at the same time...

It doesn't work to copy straight over the SQL from the queries, especially since there are four subqueries...

I have got this working for the Listview:

strSQL = "select itemnumber as [id], title as [title1], askingprice as [active] from book where title like " & """" & Forms!sbinventory!txtSearchAuthor & "*" & """" & " order by itemnumber asc;"

But I don't know how to add syntax for the qualifications for Null values and for spaces (I want to be able to type a space and have it filter appropriately) - these worked fine in the continuous forms query.

Any help with adding the Null and Space criteria, and how to add considerations for all four textboxes, would be most appreciated (would make me giddy with joy, rather).

Thank you!

 

[boxboxbox]

Okay, this works alright to filter title AND author without error, but it still won't take into account the spaces (If I type a space, it reloads the listview, but doesn't filter out records without a space in that field...)

I don't seem to need the null criteria as it works fine even if the field is null. Just the spaces I can't get.

It doesn't seem to matter if the & """" is before or after the & " " on either side of the form field name.

where ((author like " & """" & Forms!sbinventory!txtSearchAuthor & "*" & """" & ") OR (author like " & """" & " " & Forms!sbinventory!txtSearchAuthor & " " & """" & ")) AND ((title like " & """" & Forms!sbinventory!txtSearchTitle & "*" & """" & ") OR (title like " & """" & " " & Forms!sbinventory!txtSearchTitle & " " & """" & "))