Dear reader,

I have the following situation at hand for which I cannot find a solution. Perhaps one of you can assist me.

I get data from a MS Access 7.0 database using Visual FoxPro 6.0 and ODBC 3.5 from Windows 2000. I use plain textboxes which put values into global variables. I have 7 of these textboxes, all connected. An empty textbox gets filled with a wildcard.

Access handles this fine and returns the records which match the search criteria, but it discards any NULL records for NULL does not match a wildcard in Access.

I do not want to filter the data from FoxPro since that causes too much of a slowdown. Instead, I want to filter it in the remote view.

I currently have the following SQL-select:

SELECT *;
FROM R10_Relatie R10_relatie;
WHERE R10_relatie.Vin = ?gnACR10VN;
OR (R10_relatie.Naam1 LIKE ?gcACR10N1;
AND R10_relatie.Naam2 LIKE ?gcACR10N2;
AND R10_relatie.Contactpers LIKE ?gcACR10CP;
AND R10_relatie.V_Postcode LIKE ?gcACR10PC;
AND R10_relatie.V_Plaats LIKE ?gcACR10PL;
AND R10_relatie.Aansl_Id LIKE ?gcACR10AI;
AND R10_relatie.RoepNm LIKE ?gcACR10RP)

I have come up with the following solution to solve my problem:

SELECT *;
FROM R10_Relatie R10_relatie;
WHERE R10_relatie.Vin = ?gnACR10VN;
OR ((R10_relatie.Naam1 = ?gcACR10N1;
OR R10_relatie.Naam1 IS NULL);
AND (R10_relatie.Naam2 LIKE ?gcACR10N2;
OR R10_relatie.Naam2 IS NULL);

etc..

I don't know whether my solution is realistic (I'm having doubts about that) and how to implement it. If anyone could advise me, it would be very much appreciated.

Johan IJzerman
Digital Dynamics ITC
Tel. +31-(0)578-693859
Cellphone: +31-(0)6-51299575