CODEWITHSUNDEEP
stringvbasyntaxfunction
dmr
dmr

Reputation: 22393

Help with function in string

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?

Upvotes: 1

Views: 157

Answers (3)

JohnK813
JohnK813

Reputation: 1124

So you have this exact string stored in a table field, and you want Access/VBA to evaluate it, correct?

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

If so, try the Eval() command:

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

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 1

iDevlop
iDevlop

Reputation: 25272

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).

Upvotes: 1

Related Questions