Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

criteria in dlookup

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
ChrisTHall
Asked:
ChrisTHall
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
NestorioCommented:
You can do for example:

dlookup("id","mytable","id = (select id from mytable where id=2)")
0
 
NestorioCommented:
Another way:

MyVar = CurrentDb.OpenRecordset("MyQuery").Fields("MyField")
MyResult = dlookup("MyLookupField","MyTable","MyKey = " & MyVar)
0
 
prbncCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
ChrisTHallAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
ChrisTHallAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
ChrisTHallAuthor Commented:
I got it to work....thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now