Help with function in string

Question

I have a variable, emailBody, which is set to a string stored in a database. The email body is set to a string via a dlookup function.

emailBody = DLookup("emailBody", "listAdditions", "Id = " & itemType)

The string that email body is set to includes an IIf function (which includes a dlookup function). When
?emailBody is entered in the immediate window during runtime, it shows that emailBody is set to the following string:

The new commodity is" & Iif(dlookup("IsVague", "CommodityType", "Description= " & newItem)="1", "vague.", "not vague.")

However, I want the dlookup and IIf functions to be evaluated and their results stored in the string. How do I properly format the emailBody string ("the new commodity...") in my database so that the functions will be evaluated and the results stored in the emailBody variable?

Answer 1

Your question is a bit unclear, but if [Id] (Or [Description]) is a string, then you Dlookup must be like this:

 emailBody = DLookup("emailBody", "listAdditions", "Id = '" & itemType & "'")

or

 emailBody = DLookup("emailBody", "listAdditions", "Id = """ & itemType & """")

That is, your constant should be surrounded by quotes. You can either use ' (single quote) or "" (doubled double quote).

Answer 2

I am somewhat concerned about the value of newitem, but in general you can use Eval:

s = """The new commodity is"" & " _
& "Iif(dlookup(""IsVague"", ""CommodityType"", ""Description= "" & newItem)=""1"", ""vague."", ""not vague."")"
s2 = Eval(s)

I am not sure that this is the way to go, think about it.