My 2007 database stores projects and the contracts on which they go. Each contract has several options, one for each fiscal year that the contract spans. Here is my table structure:
Tbl_FiscalYear (lookup for fiscal years)
FiscalYearID
FiscalYear
Tbl_Projects
ProjectID
ProjectName
ProjectFiscalYearFK
Tbl_Contracts
ContractID
ContractNumber
Tbl_ContractOptions
OptionID
ContractNumberFK
OptionStart (date type field)
OptionStartFiscalYearFK
My query has fields FiscalYear (from lookup table tbl_FiscalYear), ProjectName, ContractNumber, and OptionStart. I have set a parameter [Enter the fiscal year] in the criteria row of FiscalYear because the user will run the query for one FY at a time. Through the relationships between FiscalYear, ProjectFYFK, and OptionStartFYFK, setting a parameter for the FiscalYear field should return the values for ProjectName and OptionStart that correspond to the chosen fiscal year.
The problem is that both the OptionStart and OptionStartFiscalYearFK fields must contain null values. By setting a parameter for FiscalYear, the query excludes the nulls, and the projects and contracts attached to those records with null values do not show.
To handle the nulls, I tried entering
[Enter the fiscal year] Or Is Null
into the Criteria row of FiscalYear field, but the query is still excluding those records with null values.
I then tried splitting the single query into two queries to create an outer join. In the two queries setup, I set two parameters, one each for ProjectFiscalYearFK and for OptionStartFYFK. Null values were still excluded.
Clearly I do not understand how to properly handle nulls here. Any help would be appreciated!
Thanks!
Tbl_FiscalYear (lookup for fiscal years)
FiscalYearID
FiscalYear
Tbl_Projects
ProjectID
ProjectName
ProjectFiscalYearFK
Tbl_Contracts
ContractID
ContractNumber
Tbl_ContractOptions
OptionID
ContractNumberFK
OptionStart (date type field)
OptionStartFiscalYearFK
My query has fields FiscalYear (from lookup table tbl_FiscalYear), ProjectName, ContractNumber, and OptionStart. I have set a parameter [Enter the fiscal year] in the criteria row of FiscalYear because the user will run the query for one FY at a time. Through the relationships between FiscalYear, ProjectFYFK, and OptionStartFYFK, setting a parameter for the FiscalYear field should return the values for ProjectName and OptionStart that correspond to the chosen fiscal year.
The problem is that both the OptionStart and OptionStartFiscalYearFK fields must contain null values. By setting a parameter for FiscalYear, the query excludes the nulls, and the projects and contracts attached to those records with null values do not show.
To handle the nulls, I tried entering
[Enter the fiscal year] Or Is Null
into the Criteria row of FiscalYear field, but the query is still excluding those records with null values.
I then tried splitting the single query into two queries to create an outer join. In the two queries setup, I set two parameters, one each for ProjectFiscalYearFK and for OptionStartFYFK. Null values were still excluded.
Clearly I do not understand how to properly handle nulls here. Any help would be appreciated!
Thanks!
