Based on a date in a form, return First of Fiscal Month based on FiscalMonthTable

[mmart33]

I have a table that has our fiscal months with a BeginningDate and End Date that equals a fiscal month (for example, JAN 2013 - Beginning = 12/29/12, Ending Date is 2/1/13)

When a date is selected in a form, I want to reference this date in a query. I want my FiscalMonthTable to determine which fiscal month the date lies in. I then want to create a field that generates the BeginningDate for that FiscalMonth.

I will use this to determine the FiscalMonthToDate totals for a report. I know how to use DateSerial to get First of Month and First of Year, but I need our custom First of Fiscal Month and Fiscal Year.



Thanks!!

[June7]

One way to do this is with DLookup() domain aggregate function.

DLookup("month field", "FiscalMonthTable", "#" & [date value] & "# BETWEEN [Beginning] AND [Ending]")

or

DLookup("Beginning", "FiscalMonthTable", "#" & [date value] & "# BETWEEN [Beginning] AND [Ending]")

[mmart33]

This worked perfectly. Thanks!

One way to do this is with DLookup() domain aggregate function.

DLookup("month field", "FiscalMonthTable", "#" & [date value] & "# BETWEEN [Beginning] AND [Ending]")

or

DLookup("Beginning", "FiscalMonthTable", "#" & [date value] & "# BETWEEN [Beginning] AND [Ending]")