dlookup function problems

[rocco]

hello,
i have two tables. The first table ("tblclienti")lists
all the clients of my company. Each client is identified
by a code ("codcliente") that is also the primary key for
this table (i.e. "1" for the first, "2" for the second
and so on). The field for the code is text type, also if
it is a number.
Then i have another table "preventivi" in wich are listed
all the works done for those clients. In the forms where
i enter data for this second table, the client name is
choosen trough a combobox. In the table "preventivi" is
recorded the code for the client and not the name.
Now i'm building another form and i need to set the value
of an unbound text box ("CLIENTE") to the client name.
For this I'm using the dlookup function twice. The first
time i find the client code in the "preventivi" table
using in the criteria the number for the work done, and
the second time i use in the criteria a variable setted
to the result of the first dlookup function to retrieve
the name for the client in the "tblclienti" table.
This second dlookup function doesn't work. It gives me
the "data type mysmatch in criteria expression". Why?!
Here is the code:

Private Sub Form_Open(Cancel As Integer)
Dim cli As String
Dim name As String
cli = DLookup
("client", "preventivi", "nrpreventivo=nrpreventivo.value"
)
name = DLookup("name", "tblclienti", "codcliente=" &
cli)
CLIENTE.Value = name
End Sub

 

[Douglas J. Steele]

When your field is a text type, you need to use quotes around the value:

name = DLookup("name", "tblclienti", "codcliente='" & cli &"'")

(In case it isn't obvious, that last argument is "codcliente=' " & cli &" '
")

And I'd advise strongly against use name as a variable: it's a reserved word
in Access, and using it inappropriately can lead to all sorts of problems.

 

[rocco]

thanks!
it works!!
where i could learn all those tricks?
In the help for dlookup function there's nothing about
this.
Thanks a lot!!!

 

[Marshall Barton]

i have two tables. The first table ("tblclienti")lists
all the clients of my company. Each client is . . .

Please don't post the same message to multiple newsgroups.

I wasted my time answering this same question in another
group.