DLookup function problem

M

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.
 
A

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]
 
D

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)
 
M

michirure

Thanks so much!

Douglas J. Steele said:
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!)


michirure said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

setvalue macro 4
dlookup error 7
Add records to table from Datsheet 2
Double-Click in Datasheet 0
Error 3075 Syntax Error 5
Date lookup 1
Problem with DLookup function 0
Dlookup Expression 3

Top