DLookUp Multiple Criteria

[Guest]

oCan someone please Help Me out? I know I have the syntax wrong but I am not
sure f how to fix it. I have the following DLookup function populating a
textbox but it isnt working:

=DLookup("[Cont_Auth]", "[tblProgram]", "Forms!frmTSD!cboIRN=[tblWrkPkg!IRN]
AND [tblWrkPkg!Master] = [tblProgram!Master]")

Thanks in advance,
Chad

 

[Sandra Daigle]

Hi Chad -

First of all, in the criteria the name of the fieldname should be first
followed by the test value. The fieldname should not be prefixed with the
table name. So as a simple example lets say you want to find the record
with IRN=999:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN = 999")

Since your test value (999) is in a control you have to build the criteria
by appending the value to the criteria. Note that the reference to the
control on the form is *outside* of the quotes. When VBA executes this
statement it will resolve the control reference with the control's value. So
now your statement becomes:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN =" & Forms!frmTSD!cboIRN)

To add another condition you just append the second field name with the And
operator:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN =" & Forms!frmTSD!cboIRN & " AND
Master=" & Forms!frmTSD!Master)

*I don't know where your second test value is really coming from - it
appears that you're trying to reference a field in another table but this
doesn't really make sense here.

 

[Guest]

What you provided me with is very helpful, thank you. About your last
comment though... yes I am trying reference a field from another table... my
table struture is set up as such:

tblProgram:
Program <Primary Key>
Master <Primary Key>
Cont_Auth

tblWrkPkg:
Program <Primary Key>
Master <Primary Key>
IRN <Primary Key>

(some fields have been left out but the primary keys are all still there.
I would like to get the Cont_Auth from the table tblProgram and all I have
to work with on the form will be the IRN so.... I need to match IRN with the
field on the form, then masters between the tables. Any suggesstions?

Chad

Hi Chad -

First of all, in the criteria the name of the fieldname should be first
followed by the test value. The fieldname should not be prefixed with the
table name. So as a simple example lets say you want to find the record
with IRN=999:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN = 999")

Since your test value (999) is in a control you have to build the criteria
by appending the value to the criteria. Note that the reference to the
control on the form is *outside* of the quotes. When VBA executes this
statement it will resolve the control reference with the control's value. So
now your statement becomes:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN =" & Forms!frmTSD!cboIRN)

To add another condition you just append the second field name with the And
operator:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN =" & Forms!frmTSD!cboIRN & " AND
Master=" & Forms!frmTSD!Master)

*I don't know where your second test value is really coming from - it
appears that you're trying to reference a field in another table but this
doesn't really make sense here.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

oCan someone please Help Me out? I know I have the syntax wrong but
I am not sure f how to fix it. I have the following DLookup function
populating a textbox but it isnt working:

=DLookup("[Cont_Auth]", "[tblProgram]",
"Forms!frmTSD!cboIRN=[tblWrkPkg!IRN] AND [tblWrkPkg!Master] =
[tblProgram!Master]")

Thanks in advance,
Chad

 

[Sandra Daigle]

Hi Chad,

That's what I thought might be the case. What you need to do is create and
save a query which includes both tables, joined on Master. Then change your
Dlookup statement to lookup the value in the query instead of the table (ie
change the second parameter to the query name instead of the table name).
Then drop the second part of the criteria from the Dlookup statement since
the join will take care of it for you.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

What you provided me with is very helpful, thank you. About your last
comment though... yes I am trying reference a field from another
table... my table struture is set up as such:

tblProgram:
Program <Primary Key>
Master <Primary Key>
Cont_Auth

tblWrkPkg:
Program <Primary Key>
Master <Primary Key>
IRN <Primary Key>

(some fields have been left out but the primary keys are all still
there. I would like to get the Cont_Auth from the table tblProgram
and all I have to work with on the form will be the IRN so.... I need
to match IRN with the field on the form, then masters between the
tables. Any suggesstions?

Chad

Hi Chad -

First of all, in the criteria the name of the fieldname should be
first followed by the test value. The fieldname should not be
prefixed with the table name. So as a simple example lets say you
want to find the record with IRN=999:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN = 999")

Since your test value (999) is in a control you have to build the
criteria by appending the value to the criteria. Note that the
reference to the control on the form is *outside* of the quotes.
When VBA executes this statement it will resolve the control
reference with the control's value. So now your statement becomes:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN =" & Forms!frmTSD!cboIRN)

To add another condition you just append the second field name with
the And operator:

=DLookup("[Cont_Auth]", "[tblProgram]","IRN =" & Forms!frmTSD!cboIRN
& " AND Master=" & Forms!frmTSD!Master)

*I don't know where your second test value is really coming from - it
appears that you're trying to reference a field in another table but
this doesn't really make sense here.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

oCan someone please Help Me out? I know I have the syntax wrong but
I am not sure f how to fix it. I have the following DLookup
function populating a textbox but it isnt working:

=DLookup("[Cont_Auth]", "[tblProgram]",
"Forms!frmTSD!cboIRN=[tblWrkPkg!IRN] AND [tblWrkPkg!Master] =
[tblProgram!Master]")

Thanks in advance,
Chad