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 -
-------------------------
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:
Thanks
Jamie
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?
Scott<>
I am putting this in the unbound control on the form [Bob Willoughby]. On my main menu I have a field where the girls key in a "date". The date they key in will then show up at the top of the page so they don't get confused as to what day they are on. Then they will click on an Inpsectors name, which brings up only that inspectors schedule for the day,[Form]![Bob Willoughby]. My actuall date field on this form calculates [date]+1 so if they are just putting in tomorrows inspections it will already be there and cuts down on data entry. They can overide the date and change a certain inspection to another one if needed. At the top of that form is my unbound control to bring up the message for that particular inspector.
qDailyMessages is a query which is straight forward.
SELECT tMessages.Date, tMessages.Message, tMessages.Inspector
FROM tMessages
ORDER BY tMessages.Date DESC;
Does this help anymore?
Jamie
Answer Well first I would definitely not have separate forms for each Inspector. Instead I would have one form and filter it for the selected Inspector. But that's another issue.
Though using one form can help you filter the query. You could then filter the query by the scheduled date on the form which would then return ONE record making the DLookup easier and simpler.
There is also another problem I should have noticed before. Date is a reserved word in Access. Using that as a field or object name can cause unpredictable results. So you need to change that name to MsgDate or somethign like that.
But a custom function might look like this:
Public Function DailyMessage(strInspector As String, dteSched As Date) As String
