Solved

criteria in dlookup

Posted on 2004-09-27
8
1,023 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

831 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