IsNull Query help please

[Epsilon101]

Hi,

Sorry for the example names but i cant use the existing table or field names, here is my sql statement.

SELECT TblLeadInfo.FieldOne, TblLeadInfo.FieldTwo, TblLeadInfo.FieldThree, TblLeadInfo.FieldFour, TblLeadInfo.FieldFive, TblLeadInfo.CustomerID, TblUWDetails.UWFieldOne, TblUWDetails.UWFieldTwo, TblUWDetails.UWFieldThree
FROM TblLeadInfo INNER JOIN TblUWDetails ON TblLeadInfo.CustomerID = TblUWDetails.CustomerID
WHERE (((TblUWDetails.UWFieldOne)="IsNull") AND ((TblUWDetails.UWFieldTwo)="IsNull") AND ((TblUWDetails.UWFieldThree)="IsNull"));

I am just trying to search for a customer who has all three of those fields with nothing in, the query returns the correct info when there is no criteria entered, but when i enter the criteria as it is now, it returns no records, well it displays the new record with the <autonumber> in customerID, but does it always do that? i cant remember

The information that goes into the three fields in TblUWDetails comes from combo box selections on a form, that are limited to list in case that effects it in anyway.

Any help would be great.


---------------------------------------

Neil

 

[Epsilon101]

Oh sorry the tables are one to one relationship

---------------------------------------

Neil

 

[PHV]

Something like this ?
WHERE TblUWDetails.UWFieldOne Is Null AND TblUWDetails.UWFieldTwo Is Null AND TblUWDetails.UWFieldThree Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

The query you have above is going to return records where UWFieldOne has a value in the database of a string that is "IsNull". Do you store "IsNull" in the field UWFieldOne in the table?

I think the syntax you need is:

WHERE IsNull(UWFieldOne) AND IsNull(UWFieldTwo) AND IsNull(UWFieldThree)

Leslie

 

[lespaul]

Ok, PHV's syntax is probably right, I was just guessing.

les


Leslie

 

[Epsilon101]

Thanks for the help

---------------------------------------

Neil