[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

criteria in dlookup

Posted on 2004-09-27
8
Medium Priority
?
1,039 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 400 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 600 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

873 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