Recordset with Null value error

[Dom606]

I have a query (qryMasterQuery_WithEmail) that is used to send bulk email. The problem I am having is one
of the fields in the query (SWITCH]) can have nulls which makes the code error on the SWITCH field
saying improper use of Null values in the line:

(MyNewBodyText = Replace(MyNewBodyText, "[[SWITCH]]", MailList("SWITCH"))

The solution, I think, is to add another condition to the sql in the query for the SWITCH
field but I am having problems figuring out how to us a third condition, e.g., if blank
or null use N/A.

Is there a way to use IIf(IsNull) in this line of code as another condition?


IIf([tblmaster].[PLATOONID]=20 Or [tblmaster].[ID]=485,"FREE",[tblDuesYearsLKU].[DuesYears]) AS SWITCH

'The query used as a recordset
Set MailList = db.OpenRecordset("qryMasterQuery_WithEmail")


' Now we can replace tokens to our heart's content
' without worrying about corrupting the "master" template

MyNewBodyText = Replace(MyBodyText, "[[Name]]", MailList("Name"))
MyNewBodyText = Replace(MyNewBodyText, "[[Address]]", MailList("Address"))
MyNewBodyText = Replace(MyNewBodyText, "[[Address2]]", MailList("Address2"))
MyNewBodyText = Replace(MyNewBodyText, "[[Phone1]]", MailList("Phone1"))
MyNewBodyText = Replace(MyNewBodyText, "[[Tour1]]", MailList("Tour1"))
MyNewBodyText = Replace(MyNewBodyText, "[[Platoon1]]", MailList("Platoon1"))
[COLOR=red]MyNewBodyText = Replace(MyNewBodyText, "[[SWITCH]]", MailList("SWITCH"))[/color]
MyMail.Body = MyNewBodyText

 

[dhookom]

I would first try the following to change a possible null to a zero length string:

IIf([tblmaster].[PLATOONID]=20 Or [tblmaster].[ID]=485,"FREE",[tblDuesYearsLKU].[DuesYears] & "") AS SWITCH

Duane
Hook'D on Access
MS Access MVP

 

[Dom606]

dhookom, thank you so much. That worked great. I guess it is easy if you know how!
Thanks
Dom