Retrieving all rcords where values are not null 1

[Kalisto]

Im trying to pull data from excel spreadsheets using Ole and some sql.

However, there are times when the end user has edited additional rows by accident, or just had the cursor in teh wrong place, and so the query imports all rows up to the position of the last edit. (Sometimes leaving me with blank records)

I have tried

"SELECT * FROM [Sheet1$]", WHERE (fn IS NOT null and sn is not null and dob is not null)"

But that seems to exclude rows where any 1 of the above is null. What I need to do is import all rows, unless all 3 of the above fields are null.

(i.e. if fn and dob are present but sn is null, then this is a valid row for importing.)

Any thoughts on how to structure the SQL? (I'd rather do this in the query than loop through all the datarows in my code and delete them there if atall possible.)

Cheers

K

 

[PHV]

Use OR instead of AND:
WHERE (fn IS NOT null OR sn is not null OR dob is not null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way:
WHERE NOT (fn IS null AND sn is null AND dob is null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kalisto]

PHV, I can see why "where not" would work (and it does, so thanks), but I cant see how or'ing would work, as that to me logically would exclude where any 1 of the 3 was null ?

 

[PHV]

Have you ttried the OR version ?
In formal logic, NOT (A and B) is the same as NOT A or NOT B

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kalisto]

I didnt try it, but I will do.

Ive just got to get this version up to the test server first, then I can look at enhancing / playing whilst they find a new set of 'features'

Cheers