Solved

DLookup criteria problem

Posted on 2010-09-13
2
388 Views
Last Modified: 2013-11-28
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
Comment
Question by:ghaphis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 33669927
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33672482
;-)
0

Featured Post

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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