Dlookup using Date()

Question

Hi All,

I am having a problem with Dlookup. I have a table (tblFinYearLt) with fields of:

FinYearIDS (autonumber)

FinYear (text - 2018/2019 etc)

FinYearStDte - (Short date) start date is 01/07/2018

FinYearEndDte - (Short Date) end date is 30/06/2019

Each financial year is set up in the same way.

What I am trying to do is set the default date for a combo to the present financial year by looking at today's date (date() and using dlookup to check which financial year today is between and therefore giving me the FinYearIDS to set the combo default.

DLookup("[FinYearIDS]", "tblFinYearLst", "Date() Between #" & [FinYearStDte] & "# And #" & [FinYearEndDte] & "#")

Using the above code I get the error of:"Microsoft access cant find the field '[1' refered to in your expression.

I am guessing it is because Date()is not a field in the table but I can't work out how to use date() which is what I need..

Any suggestions?

Answer 1

It would simply be:

=DLookup("[FinYearIDS]", "tblFinYearLst", "Date() Between [FinYearStDte] And [FinYearEndDte]")

Answer 2

You don't really need a table of accounting years for this.  The following function will return the accounting year for any date by passing the date, and the month and day when the accounting year starts into the function:

Public Function AcctYear(DateVal As Date, MonthStart As Integer, DayStart As Integer) As String

    Dim dtmYearStart As Date

    If MonthStart = 1 And DayStart = 1 Then

        ' accounting year is calendar year, so return single year value

        AcctYear = Year(DateVal)

    Else

        ' get start of accounting year in year of date value

        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)

        ' if date value is before start of accounting year

        ' accounting year starts year previous to date's year,

        ' otherwise it starts with date's year

        If DateVal < dtmYearStart Then

            AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00")

        Else

            AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00")

        End If

    End If

End Function

In your case AcctYear(Date(),7,1) would return the current accounting year starting 1 July.  Note that as written the function returns the accounting year in the format yyyy-yy which was the convention in the government circles in which I worked.  It's easily amended to return it in your format of yyyy-yyyy:

        If DateVal < dtmYearStart Then

            AcctYear = Year(DateVal) - 1 & "-" & Year(DateVal)

        Else

            AcctYear = Year(DateVal) & "-" & Year(DateVal) + 1

        End If

Answer 3

I endorse Gustav's solution. There is really no need to complicate the DLookup() if you aren't using an external value.

Answer 4

I think you might have swapped the <> signs

DLookup("FinYearIDS", "tblFinYearLst", "FinYearStDte**<=#" & Date() & "# And FinYearEndDte>=**# & Date() & "#")

Answer 5

Try

DLookup("FinYearIDS", "tblFinYearLst", "FinYearStDte>=#" & Date() & "# And FinYearEndDte<=# & Date() & "#")

If there might be users who use European date format dd/mm/yyyy, you must replace both occurrences of Date() with Format(Date(), "mm/dd/yyyy")