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

Thanks to All. This works great for my purpose.