DLookup function problem

[michirure]

I've created the following in the ControlSource of a text box on a form.

TextBox = DLookup("[FirstName]&[LastName]","[Suppliers]","[SupplierID]=" &
Forms!Products!SupplierID)

This works well, but I want to add a space between FristName & LastName.
When I use the following expression, there's a dialog box saying "You may
have entered an operand without an operator".
TextBox = DLookup("[FirstName] &" " &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

I still can't figure out where's the syntax error after trying to add & and
" " at different positions.

 

[Arvin Meyer [MVP]]

Try using a recordset instead. Not only is it much faster, you can do what
you want. Set a reference to DAO, if you haven't done so already (air code):

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select * From Suppliers Where SupplierID=" &
Forms!Products!SupplierID

Set db As CurrentDb
Set rst As db.OpenRecordSet(strSQL, dbOpenSnapshot)

Me.MyTextBox = rxt![FirstName] & " " & rst![LastName]

 

[Douglas J. Steele]

The syntax error is because of your quotes. "[FirstName] &" " & [LastName]"
consists of two substrings ("[FirstName] &" and " & [LastName]") with no
operator between them. If you want a quote inside quotes, you need to double
the inner quotes:

TextBox = DLookup("[FirstName] &"" "" &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

Alternatively, you can use single quotes to represent the quoted space
inside the string.

TextBox = DLookup("[FirstName] & ' ' &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

Other options are to use the Space function, or the Chr function with the
ASCII representation of a space (32):

TextBox = DLookup("[FirstName] & Space(1) &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

or

TextBox = DLookup("[FirstName] & Chr(32) &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

 

[michirure]

Thanks so much!

The syntax error is because of your quotes. "[FirstName] &" " & [LastName]"
consists of two substrings ("[FirstName] &" and " & [LastName]") with no
operator between them. If you want a quote inside quotes, you need to double
the inner quotes:

TextBox = DLookup("[FirstName] &"" "" &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

Alternatively, you can use single quotes to represent the quoted space
inside the string.

TextBox = DLookup("[FirstName] & ' ' &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

Other options are to use the Space function, or the Chr function with the
ASCII representation of a space (32):

TextBox = DLookup("[FirstName] & Space(1) &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

or

TextBox = DLookup("[FirstName] & Chr(32) &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've created the following in the ControlSource of a text box on a form.

TextBox = DLookup("[FirstName]&[LastName]","[Suppliers]","[SupplierID]=" &
Forms!Products!SupplierID)

This works well, but I want to add a space between FristName & LastName.
When I use the following expression, there's a dialog box saying "You may
have entered an operand without an operator".
TextBox = DLookup("[FirstName] &" " &
[LastName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

I still can't figure out where's the syntax error after trying to add &
and
" " at different positions.