Trying to create a DLookup wiht multiple criteria

121 100+

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

 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

Subscribe Post Reply

3311

JHNielson

121

100+

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

8,834

Expert 8TB

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

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

32,556

Expert Mod 16PB

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

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

 
EVNT_DT: DLookUp("[Date]", _

                 "[LU - Quarter Dates]", _

                 "([PRC_15011 - Find_Vendor_Mgmt_Fees].EVNT_QTR=[LU - Quarter Dates].[Quarter]) " & _

                 "AND " & _

                 "([PRC_15011 - Find_Vendor_Mgmt_Fees].[EVNT_YEAR]=[LU - Quarter Dates].[YEAR])")

Apr 26 '07 #4

NeoPa

32,556

Expert Mod 16PB

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

Similar topics

DLookUp of multiple records in a query

by: KLAU | last post by:

I have a field that retrieves information from an expression in a query. I have used a DLookup function to get the calculated field from the query. However, the relationship is 1-to-many so one...

Microsoft Access / VBA

Can I use multiple criteria with a DLookup?

by: ShyGuy | last post by:

I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("",...

Microsoft Access / VBA

DLookUp

by: Nick J | last post by:

Hi, I want t display several fields in one label box using the DLookUp function. I have tried using below but all I get is '-1' when I preview the report. Any idea's? =DLookUp("" And...

Microsoft Access / VBA

DLookup criteria problem

by: chris.thompson13 | last post by:

I am having a problem setting the criteria part of the DLookup method correctly and am consequently getting an error message. I have a database of staff duties, part of which is a query (qryDaily)...

Microsoft Access / VBA

Boolean criteria with DLookup

by: bjaj | last post by:

Hi How do I use a boolean criterian with the funktion DLookup ? I know the syntax for strings, numeric and date as follows For numerical values: DLookup("FieldName" , "TableName" ,...

Microsoft Access / VBA

Dlookup with 2 criteria

by: MSeda | last post by:

I have a loop that is controlled by a Dlookup statement with two criteria. Both criteria fields are checkboxes. I have tried an assortment of quotation marks in the criteria section and cannot get...

Microsoft Access / VBA

another Dlookup problem...

by: vkong85 | last post by:

I'm creating a database for work and i've run into a snag. Currently i'm using to nested dlookup statements and they are searching for certain criteria in order to find the correct value the...

Microsoft Access / VBA

DLookup with multiple fields

by: BradHodge | last post by:

Wouldn't ya know it. Posting a question on my first day of "ExpertDum" :) How do you use DLookup with multiple criteria from the same table? I have a report and I want a text box to display...

Microsoft Access / VBA

Criteria works for FindFirst but not for Dlookup when text containsdouble quotes

by: Denise | last post by:

Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a...

Microsoft Access / VBA

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing