DLookUp

[Mike C]

I have a dlookup expression in a query that almost works.
It will go to the table and return the value with the
field name and the table. But I can't seem to get the
criteria right. Any ideas or suggestions would be greatly
appreciated. Thanks in advance.

SA_Plan: DLookUp(Forms!frm_Main!
txt_Mon,"tbl_SAPlan","Entity = [qry_SA]![CEN_NUM]")

Entity is the field in the table and CEN_NUM is the field
in the query (same query the dlookup is in).

 

[Allen Browne]

Concatenate the value of the query field into the 3rd argument.

If Entity is a Text field, try:
..., "Entity = """ & [CEN_NUM] & """"

If it is a Number field, try:
..., "Entity = " & [CEN_NUM]
Actaully, that will be mal-formed if Cen_Num is null, so use Nz() to take
care of that case:
..., "Entity = " & Nz([CEN_NUM], 0)

More help on DLookup():
http://allenbrowne.com/casu-07.html

 

[Mike C]

That worked... thank you very much.

-----Original Message-----
Concatenate the value of the query field into the 3rd argument.

If Entity is a Text field, try:
..., "Entity = """ & [CEN_NUM] & """"

If it is a Number field, try:
..., "Entity = " & [CEN_NUM]
Actaully, that will be mal-formed if Cen_Num is null, so use Nz() to take
care of that case:
..., "Entity = " & Nz([CEN_NUM], 0)

More help on DLookup():
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a dlookup expression in a query that almost works.
It will go to the table and return the value with the
field name and the table. But I can't seem to get the
criteria right. Any ideas or suggestions would be greatly
appreciated. Thanks in advance.

SA_Plan: DLookUp(Forms!frm_Main!
txt_Mon,"tbl_SAPlan","Entity = [qry_SA]![CEN_NUM]")

Entity is the field in the table and CEN_NUM is the field
in the query (same query the dlookup is in).

.