DLookup Function

[Alex Motilal]

I am using Access 2007. The problem is in the following Function:

Public Function GetSeason(SeasonalDate As Date) As String
GetSeason = Nz(DLookup("[PurchaseSeason]", "tblSeason", "[SeasonStartDT]<= #" & SeasonalDate & "# And [SeasonEndDT]>= #" & SeasonalDate & "#"), "")
End Function

For the Season 2013-14, the first date is 1st October 2013 and the last date is 31st September 2014. The Query returns Null for the following 3 dates:
10th January 2014, 11th January 2014 and 12th January 2014.


For all other dates, the Function returns the correct Season including 9th & 13th January 2014. This baffles me. Can anyone suggest what may be the problem. I tried Allen Brown's ELookup Function and it also gives the same result. Can anyone suggest what to do?

Alex

[Bob Fitz]

What date format do you use dd/mm/yyyy or mm/dd/yyyy

[Alex Motilal]

I am using dd/mmm/yyyy format.

[Alex Motilal]

Sorry. Actually it is dd/mm/yyyy Format.

[Missinglinq]

Sorry. Actually it is dd/mm/yyyy Format.

I suspect that that is the problem; MS Access is US-centric, when it comes to dates! Allen Browne explains the ins-and-outs of using date formats other than the US format with Access, here:

International Dates in Access

Linq ;0)>

[Alex Motilal]

Thank u Missinglinq,
My system setting is also in dd/mm/yyyy format. However, the problem persists. I tried examples for the same days in future dates (ie., 10/01/2015, 10/01/2016 and so on) and the same problem repeats.
Regards,
Alex

[lfpm062010]

What is the "[SeasonStartDT]" defined in the table? Is it "DateTime" data type or "Text"?

You can try to put "#" around "[SeasonStartDT]" and [SeasonEndDT]". Like
"#" & "[SeasonStartDT]" & "#"
"#" & "[SSeasonEndDT" & "#"

[Alex Motilal]

Both [SeasonStartDt] and [SeasonEndDt] fields are Date/Time and formatted to Medium Date. I tried with "#" as you suggested and again I am getting erratic results.
Alex

[ssanfu]

Can you provide a dB of JUST the table "tblSeason"?