DateFields Not Corresponding

[ConfusedNAccess]

Do you see a way to simplify this? The problem lies with the ActualYear field- which shows the year of the active report. It could be any year from 2001-2007. I need to show current reports and reports from last year that may have a due date of this year. Ex. December 2005 report may not be due until May of 2006. I don’t know how to write this. When I inserted the highlighted field, it slows up the query, and locks it up.

I appreciate you looking at this for me!

IIf([dbo_ActualReports].[Active01]=-1 And [dbo_ActualReports].[Month01Value]=0 And year([dbo_ActualReports].[ActualYear])=year(now()-1) AND Year([dbo_ActualReports].[Month01DueDate])=Year(Now()) And IsNull([dbo_ActualReports].[Month01SignOff]) And [dbo_ActualReports].[Month01DueDate]<=[Enter date to check],[dbo_BorrowerReports].[SheetRowText],[dbo_ActualReports].[Month01SignOff]) AS Jan,

IIf([jan]=True,[dbo_ActualReports].[Month01DueDate],"") AS JanDue,

Active01- shows if the report is active
Month01Value- shows if the report has been received.
ActualYear- shows the year of the report
Month01DueDate- shows the due date of the report in date format
Month01Signoff- shows if the LSC or AM signed off on not receiving it.
SheetrowText- is the name of the report
EffectiveFromDate & EffectiveToDate- Show Effective dates of the report


When testing this , results are returning for borrowers that have due dates in December of May 1, but because I didn’t use the “Actual Year” field correctly, it’s returning results if the due date is in this year.
Year([dbo_ActualReports].[Month01DueDate])=Year(Now())

EffectiveFromDate Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 1
Required: True
Source Field: EffectiveFromDate
Source Table: dbo_BorrowerReports
EffectiveToDate Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 2
Required: False
Source Field: EffectiveToDate
Source Table: dbo_BorrowerReports
ActualYear Long Integer 4
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 4
Required: True
Source Field: ActualYear
Source Table: dbo_ActualReports
Active01 Yes/No 1
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 1
Required: False
Source Field: Active01
Source Table: dbo_ActualReports
Month01Value Yes/No 1
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 2
Required: False
Source Field: Month01Value
Source Table: dbo_ActualReports
Month01ReceivedDate Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 3
Required: False
Source Field: Month01ReceivedDate
Source Table: dbo_ActualReports
Month01DueDate Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 4
Required: False
Source Field: Month01DueDate
Source Table: dbo_ActualReports
Month01SignOff Text 25
AllowZeroLength: True
Attributes: Variable Length
Collating Order: General
Data Updatable: False
GUID: Long binary data
Ordinal Position: 5
Required: False