Review of DLookup expression

[eionjess]

I've reviewed all of the posts regarding dlookup and still can't figure out
why I continue to get #Name? error.

When a Job Code is entered into a form, I would like the Job Title field
filled in. Here's what I have in the control source of the job title field:

=DLookUp("[Job Title]","Job Codes and Titles"," [Job Codes and Titles]![Job
Code] =" & [Job Code])

Where did I go wrong? Also, if there's a better way to do this, I'm
definitely open to suggestions. Thanks in advance.

 

[Pieter Wijnen]

Try
=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] =" & [Job
Code])

If Job Code is a Text Field you need
=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] ='" & [Job
Code] & "'")

HTH

Pieter

 

[eionjess]

Pieter, thanks. Job Code is a text field. I don't get the #Name? error, but
now I don't get anything.

Try
=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] =" & [Job
Code])

If Job Code is a Text Field you need
=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] ='" & [Job
Code] & "'")

HTH

Pieter

I've reviewed all of the posts regarding dlookup and still can't figure
out
why I continue to get #Name? error.

When a Job Code is entered into a form, I would like the Job Title field
filled in. Here's what I have in the control source of the job title
field:

=DLookUp("[Job Title]","Job Codes and Titles"," [Job Codes and
Titles]![Job
Code] =" & [Job Code])

Where did I go wrong? Also, if there's a better way to do this, I'm
definitely open to suggestions. Thanks in advance.

 

[HK]

=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] ='" & [Job
Code] & "'")

Try

DLookup("[Job Title]", "Job Codes and Titles", "nz([Job Code], "")= ""
And nz([Job Code], "") = """)

 

[eionjess]

I tried. It resulted in #Error.

=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] ='" & [Job

Code] & "'")

Try

DLookup("[Job Title]", "Job Codes and Titles", "nz([Job Code], "")= ""
And nz([Job Code], "") = """)

 

[UpRider]

ANY fieldname, table name, query name, etc with spaces in the name needs to
be in square brackets [ ].
if Job Code is on a form, then

= DLookup("[Job Title]", "[Job Codes and Titles]", "[Job Code] = Me.[Job
Code]")

This code must be in the form's module.
It also assumes that the field on the form that the job code is typed into
is named 'Job Code' which is bound to table field 'Job Code'.
I try to eliminate this confusion by ALWAYS prefixing 'txt' on form fields
and not using spaces in names.
On the form, I would have named the field 'txtJobCode', bound to table field
[Job Code].

UpRider

 

[Pieter Wijnen]

You might try to spell the control out completely
ie
=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code]
=Forms!YourForm![Job Code])

I never use spaces in Tables/Fields/Forms/controls etc. I prefer hungarian
notaion
ie JobCodesAndTitles & use the field's Caption Property for user
friendliness
this partly becasuse [] are hard to get at on a norwegian keyboard, partly
because most RDMS systems don't support/reccommend it
(and I can't see any reason why to do it)

HTH

Pieter

Try
=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] =" & [Job
Code])

If Job Code is a Text Field you need
=DLookUp("[Job Title]","[Job Codes and Titles]"," [Job Code] ='" & [Job
Code] & "'")

HTH

Pieter

I've reviewed all of the posts regarding dlookup and still can't figure
out
why I continue to get #Name? error.

When a Job Code is entered into a form, I would like the Job Title field
filled in. Here's what I have in the control source of the job title
field:

=DLookUp("[Job Title]","Job Codes and Titles"," [Job Codes and
Titles]![Job
Code] =" & [Job Code])

Where did I go wrong? Also, if there's a better way to do this, I'm
definitely open to suggestions. Thanks in advance.