Solved

criteria in dlookup

Posted on 2004-09-27
8
1,022 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2003 query lost it's only join 7 27
SQL multicriteria from ONE textbox 32 44
Binding recordsets to a form 6 27
Experience with VBA code that changes unexplicably? 8 24
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

832 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