424,066 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 IT Pros & Developers. It's quick & easy.

Trying to create a DLookup wiht multiple criteria

100+
P: 121
I have a query that I'm trying to update with a dlookup with multiple criteria

This is the string:
Expand|Select|Wrap|Line Numbers
  1. EVNT_DT: DLookUp("[Date]","[LU - Quarter Dates]","( [PRC_15011 - Find_Vendor_Mgmt_Fees].EVNT_QTR=[LU - Quarter Dates].[Quarter]) &  ([PRC_15011 - Find_Vendor_Mgmt_Fees].[EVNT_YEAR]=[LU - Quarter Dates].[YEAR])")

When i run it it says it can't find the name PRC_15011 - Find_Vendor_Mgmt_Fees.EVNT_QTR.

But I know the names are right, I've checked them three times.


Does anyone have an idea what's wrong? Is it the wrong syntax or something?
Apr 22 '07 #1
Share this Question
Share on Google+
4 Replies


100+
P: 121
does anyone have any ideas? I have tried it about three different ways, and I cna't get it correct?
Apr 22 '07 #2

ADezii
Expert 5K+
P: 8,591
I have a query that I'm trying to update with a dlookup with multiple criteria

This is the string:
Expand|Select|Wrap|Line Numbers
  1. EVNT_DT: DLookUp("[Date]","[LU - Quarter Dates]","( [PRC_15011 - Find_Vendor_Mgmt_Fees].EVNT_QTR=[LU - Quarter Dates].[Quarter]) &  ([PRC_15011 - Find_Vendor_Mgmt_Fees].[EVNT_YEAR]=[LU - Quarter Dates].[YEAR])")

When i run it it says it can't find the name PRC_15011 - Find_Vendor_Mgmt_Fees.EVNT_QTR.

But I know the names are right, I've checked them three times.


Does anyone have an idea what's wrong? Is it the wrong syntax or something?
Are the Tables [LU - Quarter Dates] And [PRC_15011 - Find_Vendor_Mgmt_Fees] related? If so, create a Query using both Tables and including any relevant Fields. Specify this Query as your Domain instead of [LU - Quarter Dates]. If they are not, I'm not sure that you can what you are requesting.
Apr 22 '07 #3

NeoPa
Expert Mod 15k+
P: 31,170
I have a query that I'm trying to update with a dlookup with multiple criteria

This is the string:
Expand|Select|Wrap|Line Numbers
  1. EVNT_DT: DLookUp("[Date]","[LU - Quarter Dates]","( [PRC_15011 - Find_Vendor_Mgmt_Fees].EVNT_QTR=[LU - Quarter Dates].[Quarter]) &  ([PRC_15011 - Find_Vendor_Mgmt_Fees].[EVNT_YEAR]=[LU - Quarter Dates].[YEAR])")

When i run it it says it can't find the name PRC_15011 - Find_Vendor_Mgmt_Fees.EVNT_QTR.

But I know the names are right, I've checked them three times.


Does anyone have an idea what's wrong? Is it the wrong syntax or something?
You're using the ampersand character (&) instead of the SQL AND operator. This will have the effect of concatenating the two criteria as strings instead of performing a logical AND on the results.
Expand|Select|Wrap|Line Numbers
  1. EVNT_DT: DLookUp("[Date]", _
  2.                  "[LU - Quarter Dates]", _
  3.                  "([PRC_15011 - Find_Vendor_Mgmt_Fees].EVNT_QTR=[LU - Quarter Dates].[Quarter]) " & _
  4.                  "AND " & _
  5.                  "([PRC_15011 - Find_Vendor_Mgmt_Fees].[EVNT_YEAR]=[LU - Quarter Dates].[YEAR])")
Apr 26 '07 #4

NeoPa
Expert Mod 15k+
P: 31,170
Of course, as you're trying to access fields from a table that isn't referenced in the DLookup function anyway ([PRC_15011 - Find_Vendor_Mgmt_Fees]), it won't work as I expect you intend. It looks as if you are after something a little more complicated than you've assumed.
You will need to build some sort of query that connects the two tables before trying this.
Apr 26 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.