Dlookup with Date Range

[ronda]

I have been trying this off and on for the past week and can't seem to get it .
I have a table called tblFiscal which has a start date, end date and month name.
I have another table that shows invoices with PostingWEDate field.
I am trying to get the month name according to where the posting we date falls. I know I can simply get the month name by formatting with "mmm" but when I export it to excel it still uses the dates - I need the month name (in text format).
example of tblFiscal:

Month Name - January
StartDate - 01/03/2005
EndDate - 01/30/2005

Month Name - February
StartDate - 01/31/2005
EndDate - 02/27/2005

example of tblSRATIO
PostingweDate of 1/27/2005 returns January
2/3/2005 returns February etc.

I have tried in a query:
DLookUp("[MonthName]","tblFiscalMonth","[Postingwedate]<=# " &

![tblFiscalMonth]![EndFiscal] & "#")

but it says expression is typed incorrectly or too complex to be evaluated
If anyone can point me in the right direction I would appreciate it!!!!!

 

[ronda]

thanks for the info - I realized after I sent the post that just formatting the dates will not work because of the way the fiscal months are setup - March's fiscal month is 2/28 - 4/3 - if I used the formatting that would return February, March, or April

 

[Jon K]

In DLookup, you can use Between ... And ....
For example, in query SQL View:

SELECT tblSRATIO.*,
DLookUp("[MonthName]","tblFiscalMonth","#" & [Postingwedate] & "# Between [StartFiscal] And [EndFiscal]") AS MonthName
FROM tblSRATIO;
.

 

[ronda]

thank you, thank you, thank you Jon K!!!!!!!!
now it's margarita time