Hello,
I am trying to seto up a Dlookup in a query.
My Dlookup field will retrieve data from a second query. The match criteria is the W field. This dlookup up process has to retrieve 24 different values from the second query (Query5). Unfortunatelly the function always brings the first value of the 2nd query for all the 24 fields.
My formula is the
Expr10: IIf([ph4]="Stock4";dLook Up("SumOfExpr2" ;"Query5";"[W]=" & "[Query5].[W]");[SumOfSell out]).
Is it something wrong with the formula or the Dlookup just works once!!!!
Many thanks
Demosthenis
3  1394 
Hi, and Welcome to Bytes!
Firstly, your filter value in the DLookup tries to filter on a string literal value that is the name of the field you are comparing against - which can't possibly be right (..."[W]=" & "[Query5].[W]"). It is clearly incorrect that you are trying to lookup something in Query5 where its own field [W] equals itself - the filter will not filter anything in these circumstances.
Removing the redundant ampersand from your filter you really just have
"[W]=[Query5].[W]"
and since it is Query5 that you are looking up you really have a filter that is just
"[W]=[W]"
As this will always evaluate as True (just as 1=1 is True, as indeed anything = anything is also True) there is no filter at all being applied.
In any event, DLookup can only return one value - that is its job.
I reckon you have not thought through what you are trying to achieve here, and you will need to rethink your approach.
-Stewart
Hi, and Welcome to Bytes!
Firstly, your filter value in the DLookup tries to filter on a string literal value that is the name of the field you are comparing against - which can't possibly be right (..."[W]=" & "[Query5].[W]"). It is clearly incorrect that you are trying to lookup something in Query5 where its own field [W] equals itself - the filter will not filter anything in these circumstances.
Removing the redundant ampersand from your filter you really just have
"[W]=[Query5].[W]"
and since it is Query5 that you are looking up you really have a filter that is just
"[W]=[W]"
As this will always evaluate as True (just as 1=1 is True, as indeed anything = anything is also True) there is no filter at all being applied.
In any event, DLookup can only return one value - that is its job.
I reckon you have not thought through what you are trying to achieve here, and you will need to rethink your approach.
-Stewart
Many thanks for your quick reply.
It is clear now where the problem is. Is it possible in some way to define the logic
"[Query4].[W]=[Query5].[W]"
since this is what I am trying to achieve. - the formula lies in Query 4.
Forget DLookup - it is not what you need. As I mentioned, it can only return a single value. You need to join Query 4 and Query 5 at least on the W field. You may also need to join on other key fields common to both queries that you don't mention, as otherwise the resultant query will cause multiplication of rows in the joined dataset.
Easiest way to do this is using the Access query editor, which will simplify the process of creating the joins, selecting the fields etc.
I strongly advise you to use meaningful names for fields, queries and so on. Names such as W, Query4, Query5 don't help you to understand what any of these are for. Using such meaningless names will hamper you in trying to see how to develop properly-joined queries.
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics | |
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 site
could have many units. How do I have the DLookUp field value change
to a newly calcuated field when I navigate through the units? please
see example below:
On the Form:
A site can have many units.
|
by: Tripp Knightly |
last post by:
I have a lookup table from which I want to categorize various bands of
customer net income. Some of the income is positive, some is
negative. The bands vary in size (ie, <500, -200 to 0, 100 to 1000).
When I have a lookup table w/ the "threshold" amounts of income, I'm
not able to get dlookup to work, and I'm pretty sure it's getting
tripped up by negative / positive lookup values. Is it not possible
to do lookups on a table w/ both...
|
by: access345 |
last post by:
I have created a form to lookup components in a table. The problem I am having is the table I have has multiple designations in the same field. Ex CR2,CR4,CR18
If I am looking up a field that has one designation in it I am fine. But if the field has more than one designation in it, my Dlookup returns no information. Some of these fields have more than 50 designations in them. So I am trying to avoid creating new individual records. I will if I...
|
by: |
last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the application's reports I have a page footer with an unbound field that retrieves and shows the name of the company. This is done by Dlookup. My question is: is Dlookup the best way to do this, performance wise, or is there a more efficient way?
Thanks in...
|
by: Nathan H |
last post by:
I don't know why I am having an issue with this, especially when I helped someone the other day.
Four fields in the table tblFTPLOGIN:
txtServerList
txtFolder
txtUID
txtPWD
The unbound form has a combo box called txtServerChoice, listing txtServerList from the table. I want to post txtFolder from the table to an unbound box txt on form frmForm.
| | |
by: DH22 |
last post by:
Access 03
I'm currently having an issue using Dlookup when trying to reference a query (criteria syntax)
Currently I have 1 table, which is L_Emps (which contains Employee_ID as my key (numeric), First_Name, and Last_Name (as text).
My query is Query1 which is the following:
SELECT L_Emps.Employee_ID, L_Emps.First_Name, L_Emps.Last_Name
FROM L_Emps
|
by: adigga1 |
last post by:
Hello Experts,
I'm about to go grey with this dlookup statement:
I'm trying to run a dlookup statement, I have the correct expression string, etc. but when I run it under the Immediate window for testing I'm getting the runtime error.
Scanerio:
I'm calling an associated Price value (Currency) for a Code (text) from a Query in order to have an auto population in the fields; here is the code:
|
by: tasawer |
last post by:
I am using DLOOKUP to bring up the value of VAT rate applicable in a particular date period. but it is not bringing expected values.
Can someone help me resolve the issue. Thx
I have a table: "tVATRate"
VATId RateDateStart RateDateEnd VATrate
1 01/12/2000 30/11/2008 17.5
2 01/12/2008 30/11/2009 15
3 01/12/2009 31/12/2099 16
In a form, I am using dlookup to find the VATRATE between two dates using the function below:
|
by: rleepac |
last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue
This is a table used to assign an Age Correction value to hearing test results - since some degree of hearing loss naturally occurs with aging - OSHA lets us calculate that in before determining if the employee has an actual "significant" hearing loss.
Anyway...I have my...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
