Solved DLookup & Concatenate

[E9-Tech]

I need to concatenate "FirstName" and "Surname" from a table with a lookup on a form, I am stuck on how to use concatenate in the code.

The code I have under the afterUpdate event for the textbox RefStaffN is:

Me.FullName = DLookup("FirstName", "tblEmployee", "StaffN=" & Me!RefStaffN)

This is only showing the first name in the text box FullName, how do I add the surname so that it shows Name Surname together?

Thanks

 

[Gasman]

dlookup("Carbscalc & '|'& SugarCalc","tbldaily","dailyID = 4456")
33.9|6.78

 

[arnelgp]

Me.FullName = DLookup("[FirstName] & ' ' & [LastName]", "tblEmployee", "StaffN=" & Me!RefStaffN)

 

[E9-Tech]

Me.FullName = DLookup("[FirstName] & ' ' & [LastName]", "tblEmployee", "StaffN=" & Me!RefStaffN)

Thanks for this!!

 

[Cotswold]

@Gasman & @arnelgp
Impressed with those. I've never thought of doing that in Dlookup() & not indicated in my Language Ref. Maybe the time I've spent on queries I'd rather not think about!
Rather than showing addresses vertically, I'll often concatenate addresses onto one line on Forms & Reports as it saves so much space.
I'll use Dlookup() where possible instead. However, addresses will often not have all fields filled and it looks a mess with ,, or ,,, between text. So still using my standard Function there.
But otherwise very useful.

 

[Gasman]

TBH, I got that method from @arnelgp a year or so back.

 

[cheekybuddha]

However, addresses will often not have all fields filled and it looks a mess with ,, or ,,, between text.

You can use the plus operator instead of the ampersand in your concatenation expression.

Do a search for 'The law of propagating nulls'

 

[cheekybuddha]

Back at a computer.

Here's a quick example:

AddressOneLine = DLookup("Address1 & (', ' + Address2) & (', ' + TownCity) & (', ' + County) & (' ' + Postcode)", "tblAddress", "ID = " & Me.txtAddressID)

Of course this won't work using DLookup() if you have normalised out your TownCity and County tables!

In a textbox ControlSource you can just use:

=[Address1] & (', ' + [Address2]) & (', ' + [TownCity]) & (', ' + [County]) & (' ' + [Postcode])

 

[Cotswold]

@cheekybuddha
I usually do use + and prefer them. Basically as always used them in other languages from way back.
I'd actually tested it out on one of my address tables before I posted in #5
Thanks anyway.

 

[cheekybuddha]

I usually do use + and prefer them

Ah, but it's the combination of & and + that does the magic.

 

[Cotswold]

Ah, but it's the combination of & and + that does the magic.

Correct!
It works perfectly and will probably be quicker than my Function that does the same.
Cheers for that.

 

[cheekybuddha]

than my Function that does the same

You can also use the same logic in a function:

Function AddressOneLine( _
  address1 As Variant, _
  address2 As Variant, _
  towncity As Variant, _
  county As Variant, _
  postcode As Variant
) As String

  AddressOneLine = address1 & _
                   (', ' + address2) & _
                   (', ' + towncity) & _
                   (', ' + county) & _
                   (' ' + postcode)

End Function

Then use that in a query or ControlSource expression:

=AddressOneLine([Address1], [Address2], [TownCity], [County], [Postcode])

or:

SELECT
  AddressOneLine(Address1, Address2, TownCity, County, Postcode) As [AddressOneLine]
FROM tblAddress;
-- or if you are normalised:
SELECT
  AddressOneLine(a.Address1, a.Address2, t.TownCity, c.County, a.Postcode) As [AddressOneLine]
FROM (
  tblAddress a
  INNER JOIN tblTownCity t
          ON a.TownCityFK = t.ID
)
INNER JOIN tblCounty c
        ON a.CountyFK = c.ID;

It will probably work in a DLookup() too, but I'm not sure and haven't tested:

varAddressOneLine = DLookup("AddressOneLine(Address1, Address2, TownCity, County, Postcode)", "tblAddress", "ID = " & Me.txtAddressID)