If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLookUp Multiple Criteria
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 |
#2
|
|||
|
|||
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. Chad wrote: 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 |
#3
|
|||
|
|||
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 "Sandra Daigle" wrote: 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. Chad wrote: 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 |
#4
|
|||
|
|||
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. Chad wrote: 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 "Sandra Daigle" wrote: 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. Chad wrote: 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 |
#5
|
|||
|
|||
Thanks for your help
Chad "Sandra Daigle" wrote: 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. Chad wrote: 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 "Sandra Daigle" wrote: 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. Chad wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using multiple criteria in SUMIF & COUNTIF? | RagDyer | Worksheet Functions | 2 | May 12th, 2004 03:38 AM |
Using multiple criteria in SUMIF & COUNTIF? | ScubaJoe13bitem | Worksheet Functions | 1 | May 12th, 2004 03:22 AM |
Countif functions with multiple criteria | Jason Morin | Worksheet Functions | 3 | April 5th, 2004 08:46 PM |
Countif functions with multiple criteria | t | Worksheet Functions | 0 | April 5th, 2004 07:11 PM |
Countif function based on multiple criteria | Craig Mowbray | Worksheet Functions | 4 | September 28th, 2003 10:42 PM |