dlookup problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to use dlookup in the control source of a text field in the header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see if I can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form. I don't
know if it
makes any differenct but the table that I'm trying to get the data from is
not used
anywhere else in the report.

Thanks,
 
Is EMP_CODE a text field? If yes, then the DLookup syntax you have should
work. If it's a numeric field, lose the ' characters that deliminate ddonah
value.

However, you state something about using variables from a form. A report
cannot read the variables that a form is using unless those variables are
declared as Public in the Declarations section of the form's module, and
unless the form is open, and unless you refer to the variable as
Forms!FormName.VariableName
 
I'm trying to use dlookup in the control source of a text field in the header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see if I can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form. I don't
know if it
makes any differenct but the table that I'm trying to get the data from is
not used
anywhere else in the report.

Thanks,
Click to expand...

You are asking Access to look up the [EMP_CODE] in the Employee table
where [EMP_CODE] is "ddonah". Why? You already know what the EMP_CODE
is. It's "ddonah".

Probably, what you want to do is look up an Employee's Name whose
EMP_CODE is "ddonah".

If that is so, then using an UNBOUND control, set it's control source
to:
=DLookUp("[EmpName]","Employee","[EMP_CODE] = 'ddonah'")

Substitute your actual field names as needed.

Make sure the Name of this control is not the same as the name of any
field used in the expression.

The above hard codes 'ddonah'.
Would it be better to have the user enter the wanted value when the
report is run? Several ways.

Here's a very simple one.
1) =DLookUp("[EmpName]","Employee","[EMP_CODE] = '" & [Enter Emp Code]
& "'")

You will be prompted for the value when the report opens.
 
Yes, EMP_CODE is a text field. If you'll notice the query in my original
post, there are single quotes around "nah". But even using single quotes, it
doesn't work. I also tried it without single quotes.

Thanks,
--
Dan D.


Ken Snell (MVP) said:
Is EMP_CODE a text field? If yes, then the DLookup syntax you have should
work. If it's a numeric field, lose the ' characters that deliminate ddonah
value.

However, you state something about using variables from a form. A report
cannot read the variables that a form is using unless those variables are
declared as Public in the Declarations section of the form's module, and
unless the form is open, and unless you refer to the variable as
Forms!FormName.VariableName

--

Ken Snell
<MS ACCESS MVP>

Dan D. said:
I'm trying to use dlookup in the control source of a text field in the
header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see if I
can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form. I
don't
know if it
makes any differenct but the table that I'm trying to get the data from is
not used
anywhere else in the report.

Thanks,
Click to expand...
Click to expand...
 
As I said in the original post, this is just a test to see if I can get the
simplest dlookup query to work. When I develop, I often test with the
simplest form and build my way up to the final product. If I can't get the
simplest example to work, which I can't in this case, then I'm even less
likely to get the more complex to work.

So if I can get this example to work then I'll move on to using the
variables. I've made sure that the name of the control is not the same as the
name of any field in the report.

Thanks,
--
Dan D.


fredg said:
I'm trying to use dlookup in the control source of a text field in the header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see if I can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form. I don't
know if it
makes any differenct but the table that I'm trying to get the data from is
not used
anywhere else in the report.

Thanks,
Click to expand...

You are asking Access to look up the [EMP_CODE] in the Employee table
where [EMP_CODE] is "ddonah". Why? You already know what the EMP_CODE
is. It's "ddonah".

Probably, what you want to do is look up an Employee's Name whose
EMP_CODE is "ddonah".

If that is so, then using an UNBOUND control, set it's control source
to:
=DLookUp("[EmpName]","Employee","[EMP_CODE] = 'ddonah'")

Substitute your actual field names as needed.

Make sure the Name of this control is not the same as the name of any
field used in the expression.

The above hard codes 'ddonah'.
Would it be better to have the user enter the wanted value when the
report is run? Several ways.

Here's a very simple one.
1) =DLookUp("[EmpName]","Employee","[EMP_CODE] = '" & [Enter Emp Code]
& "'")

You will be prompted for the value when the report opens.
Click to expand...
 
Try removing the [ ] characters from around the table name:

=DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

I have found that ACCESS sometimes thinks the [ ] characters are part of the
table's name when there are no "non-letter" and "non-number" characters in
the name.
--

Ken Snell
<MS ACCESS MVP>


Dan D. said:
Yes, EMP_CODE is a text field. If you'll notice the query in my original
post, there are single quotes around "nah". But even using single quotes,
it
doesn't work. I also tried it without single quotes.

Thanks,
--
Dan D.


Ken Snell (MVP) said:
Is EMP_CODE a text field? If yes, then the DLookup syntax you have should
work. If it's a numeric field, lose the ' characters that deliminate
ddonah
value.

However, you state something about using variables from a form. A report
cannot read the variables that a form is using unless those variables are
declared as Public in the Declarations section of the form's module, and
unless the form is open, and unless you refer to the variable as
Forms!FormName.VariableName

--

Ken Snell
<MS ACCESS MVP>

Dan D. said:
I'm trying to use dlookup in the control source of a text field in the
header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see if I
can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form. I
don't
know if it
makes any differenct but the table that I'm trying to get the data from
is
not used
anywhere else in the report.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
I tried that but I still get the "#ERROR".
--
Dan D.


Ken Snell (MVP) said:
Try removing the [ ] characters from around the table name:

=DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

I have found that ACCESS sometimes thinks the [ ] characters are part of the
table's name when there are no "non-letter" and "non-number" characters in
the name.
--

Ken Snell
<MS ACCESS MVP>


Dan D. said:
Yes, EMP_CODE is a text field. If you'll notice the query in my original
post, there are single quotes around "nah". But even using single quotes,
it
doesn't work. I also tried it without single quotes.

Thanks,
--
Dan D.


Ken Snell (MVP) said:
Is EMP_CODE a text field? If yes, then the DLookup syntax you have should
work. If it's a numeric field, lose the ' characters that deliminate
ddonah
value.

However, you state something about using variables from a form. A report
cannot read the variables that a form is using unless those variables are
declared as Public in the Declarations section of the form's module, and
unless the form is open, and unless you refer to the variable as
Forms!FormName.VariableName

--

Ken Snell
<MS ACCESS MVP>

I'm trying to use dlookup in the control source of a text field in the
header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see if I
can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form. I
don't
know if it
makes any differenct but the table that I'm trying to get the data from
is
not used
anywhere else in the report.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Try running the DLookup expression that you're using from the VBE Immediate
Window... if an error exists, you'll get an error message from ACCESS:

?DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

--

Ken Snell
<MS ACCESS MVP>


Dan D. said:
I tried that but I still get the "#ERROR".
--
Dan D.


Ken Snell (MVP) said:
Try removing the [ ] characters from around the table name:

=DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

I have found that ACCESS sometimes thinks the [ ] characters are part of
the
table's name when there are no "non-letter" and "non-number" characters
in
the name.
--

Ken Snell
<MS ACCESS MVP>


Dan D. said:
Yes, EMP_CODE is a text field. If you'll notice the query in my
original
post, there are single quotes around "nah". But even using single
quotes,
it
doesn't work. I also tried it without single quotes.

Thanks,
--
Dan D.


:

Is EMP_CODE a text field? If yes, then the DLookup syntax you have
should
work. If it's a numeric field, lose the ' characters that deliminate
ddonah
value.

However, you state something about using variables from a form. A
report
cannot read the variables that a form is using unless those variables
are
declared as Public in the Declarations section of the form's module,
and
unless the form is open, and unless you refer to the variable as
Forms!FormName.VariableName

--

Ken Snell
<MS ACCESS MVP>

I'm trying to use dlookup in the control source of a text field in
the
header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see
if I
can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form.
I
don't
know if it
makes any differenct but the table that I'm trying to get the data
from
is
not used
anywhere else in the report.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
You did it Ken. I forgot that in Access the table is name differently than it
is in SQL Server. In SQL Server the table is called "EMPLOYEE" but in Access
it is called "SYSADM_EMPLOYEE".

Thanks a lot,
--
Dan D.


Ken Snell (MVP) said:
Try running the DLookup expression that you're using from the VBE Immediate
Window... if an error exists, you'll get an error message from ACCESS:

?DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

--

Ken Snell
<MS ACCESS MVP>


Dan D. said:
I tried that but I still get the "#ERROR".
--
Dan D.


Ken Snell (MVP) said:
Try removing the [ ] characters from around the table name:

=DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

I have found that ACCESS sometimes thinks the [ ] characters are part of
the
table's name when there are no "non-letter" and "non-number" characters
in
the name.
--

Ken Snell
<MS ACCESS MVP>


Yes, EMP_CODE is a text field. If you'll notice the query in my
original
post, there are single quotes around "nah". But even using single
quotes,
it
doesn't work. I also tried it without single quotes.

Thanks,
--
Dan D.


:

Is EMP_CODE a text field? If yes, then the DLookup syntax you have
should
work. If it's a numeric field, lose the ' characters that deliminate
ddonah
value.

However, you state something about using variables from a form. A
report
cannot read the variables that a form is using unless those variables
are
declared as Public in the Declarations section of the form's module,
and
unless the form is open, and unless you refer to the variable as
Forms!FormName.VariableName

--

Ken Snell
<MS ACCESS MVP>

I'm trying to use dlookup in the control source of a text field in
the
header
section
of a report. If I used a query to get the result it would be this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to see
if I
can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a form.
I
don't
know if it
makes any differenct but the table that I'm trying to get the data
from
is
not used
anywhere else in the report.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Glad you found the problem.

--

Ken Snell
<MS ACCESS MVP>

Dan D. said:
You did it Ken. I forgot that in Access the table is name differently than
it
is in SQL Server. In SQL Server the table is called "EMPLOYEE" but in
Access
it is called "SYSADM_EMPLOYEE".

Thanks a lot,
--
Dan D.


Ken Snell (MVP) said:
Try running the DLookup expression that you're using from the VBE
Immediate
Window... if an error exists, you'll get an error message from ACCESS:

?DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

--

Ken Snell
<MS ACCESS MVP>


Dan D. said:
I tried that but I still get the "#ERROR".
--
Dan D.


:

Try removing the [ ] characters from around the table name:

=DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")

I have found that ACCESS sometimes thinks the [ ] characters are part
of
the
table's name when there are no "non-letter" and "non-number"
characters
in
the name.
--

Ken Snell
<MS ACCESS MVP>


Yes, EMP_CODE is a text field. If you'll notice the query in my
original
post, there are single quotes around "nah". But even using single
quotes,
it
doesn't work. I also tried it without single quotes.

Thanks,
--
Dan D.


:

Is EMP_CODE a text field? If yes, then the DLookup syntax you have
should
work. If it's a numeric field, lose the ' characters that
deliminate
ddonah
value.

However, you state something about using variables from a form. A
report
cannot read the variables that a form is using unless those
variables
are
declared as Public in the Declarations section of the form's
module,
and
unless the form is open, and unless you refer to the variable as
Forms!FormName.VariableName

--

Ken Snell
<MS ACCESS MVP>

I'm trying to use dlookup in the control source of a text field
in
the
header
section
of a report. If I used a query to get the result it would be
this:
select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'

The dlookup syntax that I'm using is this:
=DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")

I get a "#error" when I run the report. This is just a test to
see
if I
can
get anything to show up on the report. Eventually
I want to use a dlookup using variables that will come from a
form.
I
don't
know if it
makes any differenct but the table that I'm trying to get the
data
from
is
not used
anywhere else in the report.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top