Re: [MS_AccessPros] DLookup question

 

Jim-

Well first, DateDiff is a horrible way to calculate someone's actual age or years of service.  That merely extracts the Year portion of both dates and subtracts.  The correct way to do it is:

DateDiff("yyyy", [DOB], Date())+ Int( Format(Date(), "mmdd") < Format( [DOB], "mmdd") )

The second one is close.  Why did you remove the quotes around Jim Wagner?  Try this:

=DLookUp("[Employees Years of Service]","qryYearsOfService","FullName = 'Jim Wagner'")  

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Apr 7, 2016, at 8:21 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am trying to do a dlookup on a form and have come to a head and a wall. I have a query Named qryYearsOfService. The query calculates the years of service. But the form gave me a #Name? error so I tried it another way and the next expression gave me an gets a #Error error. I must be missing something so simple here. Any help will be of great assistance to me. 

Thank You 

Jim Wagner

qryYearsOfService

 SELECT tblInformation.FullName, tblInformation.DOB, DateDiff("yyyy",[tblInformation]![DOB],Now()) AS [Age of Employee], tblInformation.YearsOfServiceDate, DateDiff("yyyy",[tblInformation]![YearsOfServiceDate],Now()) AS [Years Worked], [Age of Employee]+[Years Worked] AS [Employees Years of Service]

FROM tblInformation;

 

On the form I have several text boxes that neither work.  

Text Boxes on form

this onea #Name? error

txtYearsOfServiceTotal

 =DLookUp(["Employees Years of Service"]!["qryYearsOfService"],"FullName = 'Jim Wagner'") 

 

This one gets a #Error error

Text369

 

=DLookUp("[Employees Years of Service]","qryYearsOfService","FullName = Jim Wagner")  

 

Jim Wagner

---

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? The Yahoo Mail app is fast, beautiful and intuitive. Try it today!

---

Visit Your Group

• New Members 4

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___