null in queries

[disgruntled]

I have a form where a user enters criteria that determines a query that is executed and sent to a report. My situation arises in one field of the table where in some cases a null value. My form allows the user to set a range for this field but i would also like the query to return the null records as well. I have tried entering the 'OR is null' onto the criteria expression in the query but no luck as of yet. Any thoughts would be great!!!!

here is the sql generated by the query as i have it now.....

SELECT DISTINCTROW tblMain_BV.PARENT_ID, tblMain_BV.VEGETATION_CODE, tblMain_BV.SEED_PLAN_UNIT_ID, [tbl_SEED_PLAN_UNIT].[VEGETATION_CODE] & " " & [tbl_SEED_PLAN_UNIT]![SEED_PLAN_ZONE_CODE] & " " & [tbl_SEED_PLAN_UNIT]![ELEVATION_BAND_CODE] AS [COMMON NAME], tblMain_BV.SEED_PLAN_UNIT_2, [tbl_SEED_PLAN_UNIT_1].[VEGETATION_CODE] & " " & [tbl_SEED_PLAN_UNIT_1]![SEED_PLAN_ZONE_CODE] & " " & [tbl_SEED_PLAN_UNIT_1]![ELEVATION_BAND_CODE] AS [COMMON NAMEtwo], tblMain_BV.SEED_PLAN_ZONE_CODE, tblMain_BV.BV_STATUS_CODE, tblMain_BV.STATUS_CHANGE, tblMain_BV.BV_TRAIT_CODE, tblMain_BV.BV, tblMain_BV.RANK, tblMain_BV.DATE_CREATED
FROM ((tblMain_PTREE INNER JOIN tblMain_BV ON (tblMain_PTREE.PARENT_ID = tblMain_BV.PARENT_ID) AND (tblMain_PTREE.VEGETATION_CODE = tblMain_BV.VEGETATION_CODE)) INNER JOIN tbl_SEED_PLAN_UNIT ON tblMain_BV.SEED_PLAN_UNIT_ID = tbl_SEED_PLAN_UNIT.SEED_PLAN_UNIT_ID) INNER JOIN tbl_SEED_PLAN_UNIT AS tbl_SEED_PLAN_UNIT_1 ON tblMain_BV.SEED_PLAN_UNIT_2 = tbl_SEED_PLAN_UNIT_1.SEED_PLAN_UNIT_ID
WHERE (((tblMain_BV.BV) Between [forms]![frmReport_chars]![tbox_BV_min] And [forms]![frmReport_chars]![tbox_BV_max] Or (tblMain_BV.BV) Is Null) AND ((tblMain_PTREE.MEAN_ELEVATION) Between [forms]![frmReport_Chars]![cbox_ELEV_MIN] And [forms]![frmReport_Chars]![cbox_ELEV_MAX]) AND ((tblMain_PTREE.LATITUDE_DEGREES) Between [forms]![frmReport_Chars]![tbox_LAT_MIN] And [forms]![frmReport_Chars]![tbox_LAT_MAX]) AND ((tblMain_PTREE.LONGITUDE_DEGREES) Between [forms]![frmReport_Chars]![tbox_LONG_MIN] And [forms]![frmReport_Chars]![tbox_LONG_MAX]));

The relevant section is:
(((tblMain_BV.BV) Between [forms]![frmReport_chars]![tbox_BV_min] And [forms]![frmReport_chars]![tbox_BV_max] Or (tblMain_BV.BV) Is Null)

 

[jwindon]

What does it look like in Query Design view? The "OR" part has to be on the line underneath the Criteria: line where your "AND" statement is. As far as the syntax you wrote, sounds like you need to restate the conditions within a kind of if then statement.

Hope that helps out some.

 

[disgruntled]

I've tried on the same line and below and even in it's own column and none seem to work. I don't think and if - else is what I need as I want all the values between the range as well as the null values returned so maybe it short circuits once it isn't fitting into the range?????

 

[disgruntled]

Thanks for the help but it turned out to be a side effect of an incomplete table in the dB.