Hi,

I am using the following type of query:

"SELECT mouseid from mousedata where (gene1 like '" & gene1 & "' or gene1 is null)"

where gene1 is appended by %(wildcard) in the following way:
gene1=string.concat(gene1,"%")

My problem is when I use this query and enter some value for gene1 I get all records having that value and also all records having null.
If I remove the is null criteria then I do not get the null fields when I leave the search criteria blank which I want.

I thought a wildcard like % will take any value. But it doesnt take NULL.
I looked into it and tried using the NZ function in acces. It works well when the query is directly written in access but gives oledb error in visual basic.net
Thanks

Can you explain what you want your search to return? I'm a little confused by your post. It sounds like you are looking for "give me all rows that contain my search string, or if no search string, give me the null rows". Is this what you want?

Search should bve like

"give me all rows that contain my search string, or if no search string, give me the all rows in the table including rows having null".
Thanks for your concern

I think your best bet is to use two different queries. One for when you have search criteria and one when you don't.

For the "all rows" query, you don't need anything special at all:
"SELECT mouseid from mousedata"

The search query will need to look like this:
"SELECT mouseid from mousedata where gene1 like '%" & gene1 & "%'"
or better still...
String.Format("SELECT mouseid from mousedata where gene1 like '%{0}%'", gene1)

Peter
------------------------------------------------------
Work smarter, not harder.