calculated expression

[everhopefull]

I have two email addresses in a table one personal and one work and a question asking which to use, I have a query running from the table and have an IIF expression creating a field which looks to see which one to use (that all works fine !) anyway my client has now asked for an extra field in the query which combines the two email addresses, as in aaa@homedomain.com;bbb@workdomain.com so that I can run an email to both addresses at the same time. fraid I just can't figure it out ! I'm sure it's obvious, I can't find a similar question posted either, I've tried the expression builder using AND for the two email addressses but just end up getting -1 in field when I run the query, not the two email addresses separated by a ;

Anyone any ideas

many tks

 

[ejstefl]

Try the concatenate command -

[Email address field 1] & ";" & [Email address field 2]

 

[everhopefull]

That works perfectly many tks ! BUT!!

I noticed it throws up another issue (that mightn't actually be an issue) but some of them don't have any emails at all !! (grr -imagine !) anyway it means just a ; is returned which may cause a problem with the email sending anyway is there a way to say if null don't put a ; - if u know what I mean !!

tks

 

[ejstefl]

I would use a custom function, like this:

Public Function CombineEmail(Email1 As String, Email2 As String) As String

If Email1 = "" Then
    CombineEmail = Email2
    
ElseIf Email2 = "" Then
    CombineEmail = Email1
    
ElseIf Email1 = "" And Email2 = "" Then
    CombineEmail = Null
Else
    CombineEmail = Email1 & ";" & Email2
End If

Then use this in your query:

MultipleEmail: CombineEmail(nz([emailfield1]),nz([emailfield2]))