Solved

criteria in dlookup

Posted on 2004-09-27
8
1,025 Views
Last Modified: 2008-02-01
I am trying to do a dlookup in a query, using Access 2002.  In the criteria field I want to use a field from another query, but it won't accept it.  I know you can do this with a forms field...ie forms![formname]![field] but how would I do this with a query field XXXX![queryname]![field] but I can't figure out what goes at the first bang.

Thanks
0
Comment
Question by:ChrisTHall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 16

Expert Comment

by:Nestorio
ID: 12165341
You can do for example:

dlookup("id","mytable","id = (select id from mytable where id=2)")
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12165558
Another way:

MyVar = CurrentDb.OpenRecordset("MyQuery").Fields("MyField")
MyResult = dlookup("MyLookupField","MyTable","MyKey = " & MyVar)
0
 
LVL 1

Assisted Solution

by:prbnc
prbnc earned 100 total points
ID: 12165607
There are at least two options - the one I use most is to put the compare in the field row with a boolean output(true/false) and then the criteria is just a true or false.
Example(with the query showing "above" and related to the source table/query):

IIf(dlookup("[myfield]","MyTable]",mycriteria)=[YourQuery]![field]),True,False)
and True in the criteria row

I have also created custom functions to do similar comparissions.

FYI - what you are trying to do should work if the query is "visible" to the active query (in the upper pane) - if you just put in this way [myQuery]![myfield]
-- this way it does not have to be related - but if it is not related to your data source then it just pulls the compares the first record of that field (which in most cases is not very valuable).

You can also create between and in statements to compare ranges or compare against a list
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:ChrisTHall
ID: 12173681
the dlookup is in a query, so I do not think I can establish a variable unless I do it as a global correct?
here is the expression as it stands now
custtype: IIf(Not IsNull(DLookUp("[sales]","SalesType","[sales]=querys![Query2]![sales]")),"yes","no")

am I close?
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 150 total points
ID: 12173791
I think you're looking for something like this:

custtype: IIf([SalesType]=DLookup("[sales]","Query2"),"yes","no")

This is assuming that SalesType is a field in the query where you need the DLookup (i.e., the query in which the above expression appears, not Query2).
0
 

Author Comment

by:ChrisTHall
ID: 12173893
let me put alil more info on the table...my apologies things have been crazy here.

Query2 is a query that I call in this new query...it has a calculated field in it called sales.  Which I want to use to do a dlookup on the Salestype table and return the field sales from that.

could it be the problem is the 2 same field name in 2 different locations?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12174062
Please give some examples of data and the results you want to return. I don't really follow what fields are in which tables and queries, and what results you wish to return. A DLookUp returns ONE value from a table (the first one it finds that matches your criteria), even if there are multiple records in the target table/query.
0
 

Author Comment

by:ChrisTHall
ID: 12174470
I got it to work....thanks for the help
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question