Criteria Lookup Fiscal Month

[redxtb]

I have 2 tables tblSales & tblFiscalDates.

In tblSales, I have datWeekended and curSales.
In tblFiscalDates I have datWeekEnd(Saturday); datWeekStart (Sunday); datFiscalMonth; datFiscalYear; datFiscalQtd

In my query I want to use a criteria that uses Now() to get fiscal month. I have tried using a dlookup but with no luck... any ideas?

Thanks in advance!!!

 

[pbaldy]

What was your attempt that failed?

 

[redxtb]

it was something like this.... i have long deleted it now.. I tried several different ways. like adding table names to the beginnig of everything.
dlookup("datFiscalMonth", "tblFiscalDates", "now() between " & datWeekStart & " AND " & datWeekEnd

 

[pbaldy]

Try this shot in the dark:

dlookup("datFiscalMonth", "tblFiscalDates", "datWeekStart <= Date() AND datWeekEnd >= Date()")

If that doesn't work:

dlookup("datFiscalMonth", "tblFiscalDates", "datWeekStart <= #" & Date() & "# AND datWeekEnd >= #" & Date() & "#")

 

[redxtb]

Awesome worked like a charm pbaldy!!!

 

[pbaldy]

Happy to help!