DLookUp

[Mike C]

I'm trying to put a DLookUp in my report but it's not
working. I think it's because I'm trying to pass a
parameter in the statement. Any ideas or assistance would
be greatly appreciated.

=DLookUp([Forms]![frm_Cross]!
[txt_el],"q_COM_Total_Div","[Reports]![r_COM]![txt_Div] =
[q_COM_Total_Div]![DIV_NAM]")

 

[chas]

Hi Mike,

It looks like the parameter argument of the function is
wrong (as you suspect). The left hand side of the = sign
MUST be the name of a field in the table that you are
looking into and should not be a reference to a value in a
Report control. The third parameter equates to an SQL
WHERE clause (without the WHERE).

hth

chas

 

[John Spencer (MVP)]

Does Forms!frm_Cross!txt_el contain a field name/column name in your query
"q_COM_Total_Div"? If not, the DLookup is going to fail, since the first
parameter is supposed to be a string that is the NAME of a FIELD in the
query/table referenced in the second parameter.

As a guess -

=DLookUp([Forms]![frm_Cross]![txt_el],
"q_COM_Total_Div",
"[DIV_NAM]=" & Chr(34) & [Reports]![r_COM]![txt_Div] & Chr(34))

 

[John Spencer (MVP)]

If the first one works then try putting brackets around the field name in the
second one.

DLookUp("[" & [Forms]![frm_Cross]![txt_el] & "]",
"q_COM_Total_Div",
"[txt_Div] = [q_COM_Total_Div]![DIV_NAM]")

John, thanks for responding. I put the name of the column
in the query in the Forms![frm_Cross]![txt_el] text box.
I got it to work by using the name of the field. But I
can't seem to pass it using Forms![frm_Cross]![txt_el].
Any suggestions?

This works: =DLookUp
("[1Per_NDA]","q_COM_Total_Div","[txt_Div] =
[q_COM_Total_Div]![DIV_NAM]")

Don't work: =DLookUp([Forms]![frm_Cross]!
[txt_el],"q_COM_Total_Div","[txt_Div] = [q_COM_Total_Div]!
[DIV_NAM]")

-----Original Message-----
Does Forms!frm_Cross!txt_el contain a field name/column name in your query
"q_COM_Total_Div"? If not, the DLookup is going to fail, since the first
parameter is supposed to be a string that is the NAME of a FIELD in the
query/table referenced in the second parameter.

As a guess -

=DLookUp([Forms]![frm_Cross]![txt_el],
"q_COM_Total_Div",
"[DIV_NAM]=" & Chr(34) & [Reports]![r_COM]![txt_Div] &
Chr(34))