The SQL has multiple WHERE filters, and some fields are blank.

[DRowland]

Here is a portion of the SQL
WHERE (((bdd.dbaname) LIKE '%%%findname%%%') AND ((bdd.member) LIKE '%%%findmember%%%') AND ((bdd.tob) LIKE '%%%findtob%%%') AND ((bdd.phone) LIKE '%%%findphone%%%') AND ((bdd.fax) LIKE '%%%findfax%%%')


Now the problem is that if one of the database fields is empty, the search will skip that file. For instance is there is no text in the "bdd.fax" field, even if the query string is also empty, any files with a blank bdd.fax field will be left out of the results.

Is there a way to allow for blank fields, if the query string is blank?

 

[tlbroadbent]

If the "blank fields" are Null then you can overcome this problem with the IsNull function.

WHERE IsNull(bdd.dbaname,'') LIKE '%%%findname%%%'
AND IsNull(bdd.member, '') LIKE '%%%findmember%%%'
AND IsNull(bdd.tob, '') LIKE '%%%findtob%%%'
AND IsNull(bdd.phone, '') LIKE '%%%findphone%%%'
AND IsNull(bdd.fax, '') LIKE '%%%findfax%%%'

IsNull substitutes the value after the comma for the Null value. In my example, all Null values in any of the columns will be replaced by an empty string (''). The search should then be able to find the records.

FYI: In T-SQL, you only need one wildcard ("%&quot on each end of the string. LIKE '%findfax%' Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[DRowland]

I tried, but it didn't work. Here's the error I received from the query..

Database Error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression '(((dba.dbaname) LIKE '%%') AND ((bdd.member) LIKE '%%') AND ((bdd.tob) LIKE '%%') AND ((bdd.locate1) LIKE '%%') AND ((bdd.locate2) LIKE '%%') AND ((bdd.phone) LIKE '%%') AND ((bdd.contact) LIKE '%%') AND IsNull((bdd.

http://www,'')

LIKE '%%') )'. One or more form fields were empty.

I also tried it with the IsNull between the opening parentesis, (IsNull(bdd.

http://www,''))

What have I done wrong?

 

[tlbroadbent]

Slight syntax error.

AND IsNull((bdd.

http://www,'')

LIKE '%%') )'

should be

AND (IsNull(bdd.

http://www,'')

LIKE '%%') )' Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[DRowland]

Still does not work.. I gave up, and changed my export program to make sure the

http://www field

has a single period in it. I know it's just working around the problem, but at least it works.

Now I'm trying to figure out why, when I changed from FP98 to FP2000, the database crashed! As long as I use notepad to edit the old pages, and don't try to rebuild the results pages, they work. However as soon as I convert them to the new 2000 language, they don't.
I can't even build a new page from scratch that will recognize the database. (AAARRRGGGHHH!!!)