new member..stretching skills (1 Viewer)

mogodia · Mar 15, 2011

Hi everyone. I look forward to helping when I can & being helped as I stretch my skills into some SQL/VBA programming.

so riddle me this:

The idea here is to create labels that concatenate Person1 & Person2 appropriately and simultaneously accommodate records where there is only Person1 without producing a trailing '&' on records with only Person1
results in the query
CN: [fname1] & " " & [lname1] & " & " & [fname2] & " " & [lname2]

So then my feeble reach below produces a missing operator error:

SELECT tblMailing.*, If IsNull [FName2]& " " &[LName2] Then
[FName1]& " " &[LName1]
Else [FName1]& " " &[LName1& " &" &[FName2]& " " &[LName2]

-am I close or waaaay off?

pbaldy · Mar 15, 2011

A little off, as you can't use If in a query (you can use IIf), and the syntax of the IsNull is wrong. Try this, which uses the fact that + propagates Null and & does not.

CN: [fname1] & " " & [lname1] & (" & " + [fname2] + " " + [lname2])

mogodia · Mar 15, 2011

Dear PBaldy-
It works like a CHARM! So simple...so elegant & so SUBTLE a change with big results! Thank-you also for your remarks about syntax...syntax is more persnickety than grammar and it is igniting my curiosity. -many thanks

pbaldy · Mar 15, 2011

Happy to help, and welcome to the site! You could have gone in the direction you were going, but I like using the +/& combination.

mogodia · Mar 16, 2011

So Paul-
ok, nothing like end user requests! In addition to the concatenation tips you helped me with above...how would you consider this:

My end users want this: if Lname1=lname2, then concatenate fname1 & fname2 with lname1 (so bill smith & diane smith will become Bill & Diane smith)

Can something like this still be handled in the query?

pbaldy · Mar 16, 2011

Sure; check out the IIf() function. Test the names, put one format in the true argument and the other in the false argument.

Pesky users...I could design a really sweet system if I didn't have to worry about the stupid users.

Actually, user requests = job security, so I encourage them!

mogodia · Mar 18, 2011

o' dear pbaldy! I am completely lost.
CN: IIf([lname1]=[lname2],[fname1] & (" & "+[fname2] & " " & [lname1]& ("& "+[fname1]+" "+[lname1]+" "+[fname2]+" "+[lname2]))

but now it ignores the fname1 & lname1 (leaves it blank)
and then this: anna & doug smith anna smith & doug smith in the same line
Clearly I don't know how to combine everything...
If you get a chance to shoot me some guidance I would really appreciate help with this end user request.
-many thanks

mogodia · Mar 18, 2011

new day...some sleep..more clarity:

=IIF ([lname1]=[lname2],[fname1]& “ & “ &[fname2]& “ “ &[lname1],IIF([fname1]+[lname1]+[fname2]+[lname2], [fname1] & " " &[lname1] & (" & " + [fname2] + " " + [lname2]))

still working on this

mogodia · Mar 18, 2011

morning coffee helps!
the following returns correctly in three separate fields respectively:

cn: IIf([lname1]=[lname2],[fname1] & " & " & [fname2] & " " & [lname1])
(this successfully returns: anna & doug smith)

cn2: IIf([fname1]+[lname1],[fname1] & " " & [lname1])
(this successfully returns: suzanne smith

cn3: IIf([lname1]<>[lname2],[fname1] & " " & [lname1] & (" & "+[fname2])+" "+[lname2])
(this successfully returns: anna smith & thomas Jacobs)

Now, how do I combine all of this under one field called CN?
-learning a lot here pbaldy..thank-you for the great guidance!

DCrake · Mar 18, 2011

I would be tempted to use a function as this can get quite complex

Code:

Public Function Salutation(Optional Forename1 As String, Optional Surname1 As String, Optional Forename2 As String, Optional Surname2 As String) As String

Dim sFlag as Boolean

'/are surnames the same?
If Trim(Surname1 & "") = Trim(Surname2 & "") Then
   sFlag = True
End If

'/Is there both a forename1 and a forename2
If Trim(Forename1 & "") <> "" And Trim(Forename2 & "") <> "" Then
   Salutation = Trim(Forename1 & "") &  IIF(sFlag = False," " & Trim(Surname1),Trim(Surname1)) & " and " & Trim(Forename2 & "") & IIF(sFlag = False," " & Trim(Surname2),Trim(Surname1)) )


ElseIf Trim(Forename1 & "") = "" And Trim(forename2 & "") <> "" Then
   Salutation = Trim(Forename2) & " " & Trim(Surname2)
ElseIf Trim(Forename1 & "") <> "" And Trim(forename2 & "") = "" Then
   Salutation = Trim(Forename1) & " " & Trim(Surname1)

Else
   Salutation = "Messrs " & IIF(sFlag = True,Surname1, Surname1 & " And " & Surname2

End If

End function

totally untested, this code contends with duplicate surnames and missing forename/surnames

Usage in query

Code:

NameForLetter:Salutation([Forename1],[Surname1],[Forename2],[Surname2])

mogodia · Mar 19, 2011

hey dcrake-
Yes these requests & diversity in naming can get quite complex. Your answer looks like an artform!! It will take me a couple of days to work through this & test. Thank-you very much & I will be in touch soon.
-many thanks!

mogodia · Mar 19, 2011

Dear pbaldy...it worked exactly as you suggested & by struggling & studying this I managed to bring all three conditions combined into one field.
Here it is:

cn: IIf([lname1]=[lname2],[fname1] & " & " & [fname2] & " " & [lname1],IIf([fname1]+[lname1],[fname1] & " " & [lname1],IIf([lname1]<>[lname2],[fname1] & " " & [lname1] & (" & "+[fname2])+" "+[lname2])))

-really fantastic learning experience for me!

pbaldy · Mar 20, 2011

Great! Glad you got it working. Like David, at a certain point I'd use a function.

new member..stretching skills (1 Viewer)

mogodia

New member

pbaldy

Wino Moderator

mogodia

New member

pbaldy

Wino Moderator

mogodia

New member

pbaldy

Wino Moderator

mogodia

New member

mogodia

New member

mogodia

New member

DCrake

Remembered

mogodia

New member

mogodia

New member

pbaldy

Wino Moderator

Users who are viewing this thread