DLookup for Date Range

Question

I am trying to get my form to return the Fiscal Month based on the In Home Date of the Event it's showing. This date does not have to be stored, but it should update if the In Home Date moves. So I created a text box on the form and in the Control Source property, I have the following:

=DLookUp([FiscalMonth],[tblFiscal],[EventInHome]>[tblFiscal].[FiscalStart] And [EventInHome]<[tblFiscal].[FiscalEnd])

In tblFiscal, I have a Fiscal Month field and a FiscalStart & FiscalEnd for the beginning and end dates of each month. I want the text box to look at the EventInHome on the current form and return the FiscalMonth. Right now, it's giving me a "#Name?" message.

Any help is greatly appreciated!

Answer 1

A Dlookup has 3 parts. 

1. The field you want to return the value from in quotes
2. The table or query which contains the field you want the data from (in quotes)
3. The criteria as a string.

So,

DLookup("FieldNameHere", "TableNameHere", "[AnotherField]='Something'")

In your case you want dates so it would be:

=Nz(DLookup("[FiscalMonth]", "tblFiscal", "[EventInHome] Between [FiscalStart] And [FiscalEnd]")

---

Bob Larson, Former Access MVP (2008-2010) http://www.btabdevelopment.com (free Access tools, tutorials, and samples)