AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question -------------------------
Followup To
Question -
Have two tables:
Schedule-Date, Inspector, Address, etc.
tMessages-Date, Inspector, Message
I have a form that will bring up an Inspectors day with his schedule. On the form I have an unbound field for the Message of the day if there is one. Ex: Tony full for the day.
When the form is loaded I am trying to have it do a Dlookup for the Message of the day if there is one, else return "No Message".
have also tried:
=IIF("[Inspector]=[qDailyMessages].[Inspector]","Dlookup("[Message]","qDailyMessages]","[Date]=[Forms]![Tony]![schDate]"),"No Message")
I am stumped! Can you help?
Thanks in advance
Jamie
Answer -
Try
=IIF(IsNull(DLookUp("[txtMessage]","[qDailyMessages]","[Date]=[Forms]![Tony].[schDate]")),"",DLookUp("[txtMessage]","[qDailyMessages]","[Date]=[Forms]![Tony].[schDate]"))
HTH
Scott<>
Almost there, but it will not look up the correct inspector's message. It brings up the first message that it finds for that day. I have tried the following:
Answer You might want to use a custom function for this, it will be easier to debug. But I need more info to help further. First where are you putting this expression? Second, why is your form given a name of a person? Third, is qDailymessages a query? Is so what is the SQL for it?
