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.

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]

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"michirure" <(E-Mail Removed)> wrote in message
news:4D0C088D-6F28-4145-8FB2-(E-Mail Removed)...
> 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.

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
http://I.Am/DougSteele
(no e-mails, please!)


"michirure" <(E-Mail Removed)> wrote in message
news:4D0C088D-6F28-4145-8FB2-(E-Mail Removed)...
> 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.

Thanks so much!

"Douglas J. Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "michirure" <(E-Mail Removed)> wrote in message
> news:4D0C088D-6F28-4145-8FB2-(E-Mail Removed)...
> > 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.
>
>
>