Improve company productivity with a Business Account.Sign Up

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

DLookup criteria problem

Hi

I am using DLookup in an unbound field in a report. I want it to look up the field in a query with criteria for age = "<5" and with a year in the "Year" field in the query that matches the "rYear" field in the report. All fields are text data type. The query is called "All Other disease" and the field in the query is called "Abscess". The code I'm using works fine without the last part with year, but once I add in the year criteria I get #NAME.

=DLookup("Abscess","All Other disease","[Age]='<5' and [Year] like [rYear]")

Thanks.
0
ghaphis
Asked:
ghaphis
  • 2
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
<All fields are text data type.>
1. Can I ask why age an year are text instead of numbers?
Aggregate functions are so much easier when using numbers.
2. Year is a Reserved word in Access so don't use it as a field name.
Besides you should make it more descriptive anyway.
(SchoolYear, StartYear, ...etc)

Try this in any event:
 DLookUp("Abscess","[All Other disease]","[Age]='<5'" & " AND [Year] =" & "'" & [rYear] & "'")

sample attached, see query1


Access-EEQ26470894DlookupTextStr.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
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

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.

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