Returning specific value for null

[MKH2001]

I have a table which is used as a selection criteria

Where the value is null this is represented as "UNKNOWN"

What I want to do is then pass this back to another table as a query.

This is as far as I have got.

SELECT IIf(tbl_AllSLMastr!Town Is Null Or tbl_AllSLMastr!Town="","Unknown",tbl_AllSLMastr!Town) AS TOWN
FROM tblAVNDTChsn1, tbl_AllSLMastr INNER JOIN tbl_ScoutRouteAVNDT ON tbl_AllSLMastr.IMSV7_COMPLGHT_UNITID = tbl_ScoutRouteAVNDT.UNITID
WHERE (((tbl_ScoutRouteAVNDT.GROUPID)=[tblAVNDTChsn1]![GROUPID] Or (tbl_ScoutRouteAVNDT.GROUPID)=(IIf([tblAVNDTChsn1]![GROUPID]="Unknown",(tbl_ScoutRouteAVNDT.GROUPID) Is Null))))
GROUP BY IIf(tbl_AllSLMastr!Town Is Null Or tbl_AllSLMastr!Town="","Unknown",tbl_AllSLMastr!Town);

But I am getting a mismatch error.

Basically if the chosen text is exactly the same in tblAVNDTchsn.GROUPID and tbl_ScoutRouteAVNDT.GROUPID then I want it to select... however if the text in tblAVNDTchsn.GROUPID is "Unknown" I want it to match it up with Null values in tbl_ScoutRouteAVNDT.GROUPID.

Any help would be appreciated.

Ta!

 

[VRoscioli]

You could try making a change to:

IIf(Nz(tbl_AllSLMastr!Town,"")="" ,"Unknown",tbl_AllSLMastr!Town) AS TOWN

-V

 

[MKH2001]

Thank you for the prompt response VRoscioli.

Unfortunately the original part of the part of the section for returning "Unknown" if the Town is null or blank is fine.

It is filtering down that is an issue so that my list of towns only include those where the GROUPID in tblAVNDTChsn1 and tbl_ScoutRouteAVNDT are the same or where "Unknown" in the tblAVNDTChsn1 is matched to any NULL value in the tbl_ScoutRouteAVNDT.

Was amazed by the speed of your response =)

 

[PHV]

One way:
WHERE tbl_ScoutRouteAVNDT.GROUPID=tblAVNDTChsn1.GROUPID
OR (tblAVNDTChsn1.GROUPID="Unknown" AND tbl_ScoutRouteAVNDT.GROUPID Is Null)

Another way:
WHERE Nz(tbl_ScoutRouteAVNDT.GROUPID,"Unknown")=tblAVNDTChsn1.GROUPID

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

 

[MKH2001]

Oops typed to quick. Read back and made no sense =(

And no EDIT =/

Was saying original part of query either giving me the Town name or the returning "Unknown" instead of Null was fine.
This is the part your code seems to effect.

However it is the filtering down of my list of town names that so that only those where the GROUPID in tblAVNDTChsn1 and tbl_ScoutRouteAVNDT are the same or (and this is the problem part) where the value "Unknown" in tblAVNDTChsn1 is the text "Unknown" and I want to match these to the null values in the tbl_ScoutRouteAVNDT... Thats where I am having the problems.

Was amazed by the speed of your response =)

 

[MKH2001]

Wow this one has generated some fast response. Was just trying to edit my response to VRoscioli when I got another reply.

That looks like what I am needing PHV.

Will let you know =)

 

[MKH2001]

Yes worked like a charm.

Seems so simple looking at it now =(