Change The Year End Date

[VariableZ]

EDIT: I just discovered that the problem was in my data: Some numbnuts programmer (me) allowed null values in my [DateRecorded] field, and some numbnuts operator didn't bother to put the date in one of the records. I'm going to go give myself a swirly now.

I haven't been able to find information on changing the year-end date in Access (ours is currently April 1st, but it has changed before and probably will again some time in the future) so I wrote a little code to compare the dates based on user input that's stored in a table. I'll call the first string "CODE_A" and the second string "CODE_B".



For reference:
[TotalDefects] is data type Number of field size Integer
[DateRecorded] is data type Date/Time
[Test] is data type Text populated by a value list that contains only 'AB' and 'FC'
[YearStartMonth] and [YearStartDay] are data types Number of field size Byte

CODE_A:
DSum("[TotalDefects]", "tblDefectInformation", "Year(Dateserial(Year([DateRecorded]),Month([DateRecorded])-DFirst('[YearStartMonth]', 'tblYearStartDate')+1,Day([DateRecorded])+DFirst('[YearStartDay]', 'tblYearStartDate')-1)) = " & Year(DateSerial(Year(Date), Month(Date) - DFirst("[YearStartMonth]", "tblYearStartDate") + 1, Day(Date) - DFirst("[YearStartDay]", "tblYearStartDate") + 1)) & " AND [Test] = 'FC'")

CODE_B:
DSum("[TotalDefects]", "tblDefectInformation", "Year(Dateserial(Year([DateRecorded]),Month([DateRecorded])-DFirst('[YearStartMonth]', 'tblYearStartDate')+1,Day([DateRecorded])+DFirst('[YearStartDay]', 'tblYearStartDate')-1)) = " & Year(DateSerial(Year(Date), Month(Date) - DFirst("[YearStartMonth]", "tblYearStartDate") + 1, Day(Date) - DFirst("[YearStartDay]", "tblYearStartDate") + 1)) & " AND [Test] = 'AB'")

I know it's not elegant, and any help in that regard would be helpful, but the current issue I'm having is that while CODE_A returns the correct number, CODE_B results in the error "Run-time error '3464': Data type mismatch in criteria expression."

And even stranger, I get the same Data type mismatch error if I completely remove the last criteria in the expression, so that I have:

DSum("[TotalDefects]", "tblDefectInformation", "Year(Dateserial(Year([DateRecorded]),Month([DateRecorded])-DFirst('[YearStartMonth]', 'tblYearStartDate')+1,Day([DateRecorded])+DFirst('[YearStartDay]', 'tblYearStartDate')-1)) = " & Year(DateSerial(Year(Date), Month(Date) - DFirst("[YearStartMonth]", "tblYearStartDate") + 1, Day(Date) - DFirst("[YearStartDay]", "tblYearStartDate") + 1)))

But if I replace AB with anything other than AB or FC I get NULL, as I would expect. Any ideas on what the problem could be?

[pbaldy]

LOL! Thanks for following up with the solution. I'll leave the thread as it could help someone with a similar issue.