Problem with DLookup

[Fubear]

I am putting together a report which I want to lookup several related fields in another table.

My Problem is that I only seem t get #Error returned.

I am trying to combine the first name and surname into a single report field.

=DLookUp("Forename","tblUsers","UserID=" & [Sponsor])+" "+DLookUp("Surname","tblUsers","UserID=1&quot

[Sponsor] is a field of the reports recordsource.
I have left the surname crit as 1, which was my test value to make sure DLookup works without a dnamic crit (which it did).

Anyone out there ableto shed some light on my problem?

 

[CosmoKramer]

Try changing the plus signs to ampersands:

=DLookUp("Forename","tblUsers","UserID=" & [Sponsor])& " " & DLookUp("Surname","tblUsers","UserID=1")

 

[JohnLowell]

You could do it in 1 Dlookup call:

=DLookUp("[Forename] & ' ' & [Surname]","tblUsers","UserID=" & [Sponsor])

 

[Fubear]

Thanks guys for your help, I tried both of your solutions but I still get #Error.

I am using an Access Project with SQL Server backend, could this be causing my trouble?

The code below works perfectly, but I just cant get it working on a per record basis.

=DLookUp('Forename','tblUsers','UserID=1')+' '+DLookUp('Surname','tblUsers','UserID=1')

 

[CosmoKramer]

If the code works when you hard-code the value:

=DLookUp("Forename","tblUsers","UserID=1")& " " & DLookUp("Surname","tblUsers","UserID=1")

then the problem most likely lies with the [Sponsor] control. Check to make sure that Sponsor is the Name of the control. That's usually the biggest cause of the #Error problem.....