Solved

DLookup criteria problem

Posted on 2010-09-13
2
383 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
  • 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

777 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