Greater than/Less than and null values

[loasbyp]

Can anybody suggest how to return records with null values when using the greater than or equal to and less than or equal to criteria in a query please?

The SQL text in the query below works fine where there is a figure in the ROSStart/ROSEnd/LFVarStart/LFVarEnd columns, however if they are blank the rows are ignored and I am unable to figure out a way around this after what feels like thousands of different cominations of queries/formulae!!!

Thanks in advance.

SELECT [Tbl-Flight Model Output (By Prod) S07].Origin, [Tbl-Flight Model Output (By Prod) S07].Dest, [Tbl-Flight Model Output (By Prod) S07].[Product Type], [Tbl-Flight Model Output (By Prod) S07].[Flight Code], [Tbl-Flight Model Output (By Prod) S07].[Dep Date], [Tbl-Flight Model Output (By Prod) S07].[Days to Dep], [Tbl-Flight Model Output (By Prod) S07].[ROS %], [Tbl-Flight Model Output (By Prod) S07].[TY Cax], [Tbl-Flight Model Output (By Prod) S07].[TY Pax], [Tbl-Flight Model Output (By Prod) S07].[TY Sts], [Tbl-Flight Model Output (By Prod) S07].[TY LF], [Tbl-Flight Model Output (By Prod) S07].[LF Var], [Tbl-Flight Model Output (By Prod) S07].[TY Mgn/Pax], [Tbl-Flight Model Output (By Prod) S07].[7 Day Pax], [Tbl-Flight Model Output (By Prod) S07].[7 Day Mgn/Pax], [Tbl-Flight Model Output (By Prod) S07].[LY Cax], [Tbl-Flight Model Output (By Prod) S07].[LY LF], [Tbl-Flight Model Output (By Prod) S07].[LY LF Final], [Tbl-Flight Model Output (By Prod) S07].[LY LF 1wk out], [Tbl-Flight Model Output (By Prod) S07].[LY LF 2wk out], [Tbl-Flight Model Output (By Prod) S07].[LY LF 3wk out], [Tbl-Flight Model Output (By Prod) S07].[LY LF 4wk out], [Tbl-Flight Model Output (By Prod) S07].[LY LF 5wk out], [Tbl-Flight Model Output (By Prod) S07].[LY LF 6wk out], [Tbl-Flight Model Output (By Prod) S07].[LY LF 7wk out], [Tbl-Flight Model Output (By Prod) S07].[LY LF 8wk out], [Tbl-Flight Model Output (By Prod) S07].[LY Pax], [Tbl-Flight Model Output (By Prod) S07].[LY Final Pax], [Tbl-Flight Model Output (By Prod) S07].[Pax 1Wk], [Tbl-Flight Model Output (By Prod) S07].[Pax 2Wk], [Tbl-Flight Model Output (By Prod) S07].[Pax 3Wk], [Tbl-Flight Model Output (By Prod) S07].[Pax 4Wk], [Tbl-Flight Model Output (By Prod) S07].[Pax 5Wk], [Tbl-Flight Model Output (By Prod) S07].[Pax 6Wk], [Tbl-Flight Model Output (By Prod) S07].[Pax 7Wk], [Tbl-Flight Model Output (By Prod) S07].[Pax 8Wk], [Tbl-Flight Model Output (By Prod) S07].[LY Mgn/Pax], [Tbl-Flight Model Output (By Prod) S07].[LY Final Mgn/Pax], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 1Wk], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 2Wk], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 3Wk], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 4Wk], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 5Wk], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 6Wk], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 7Wk], [Tbl-Flight Model Output (By Prod) S07].[Mgn/Pax 8Wk]
FROM [Tbl-Flight Model Output (By Prod) S07]
WHERE ((([Tbl-Flight Model Output (By Prod) S07].Origin) Like [forms]![Flt Rpt By Prod S07]![QryOrig]) AND (([Tbl-Flight Model Output (By Prod) S07].Dest) Like [forms]![Flt Rpt By Prod S07]![QryDest]) AND (([Tbl-Flight Model Output (By Prod) S07].[Product Type]) Like [forms]![Flt Rpt By Prod S07]![QryProd]) AND (([Tbl-Flight Model Output (By Prod) S07].[Flight Code]) Like [forms]![Flt Rpt By Prod S07]![QryFltCode]) AND (([Tbl-Flight Model Output (By Prod) S07].[Dep Date])>=[forms]![Flt Rpt By Prod S07]![QryDepDateStart] And ([Tbl-Flight Model Output (By Prod) S07].[Dep Date])<=[forms]![Flt Rpt By Prod S07]![QryDepDateEnd]) AND (([Tbl-Flight Model Output (By Prod) S07].[ROS %])>=[forms]![Flt Rpt By Prod S07]![QryROSStart] And ([Tbl-Flight Model Output (By Prod) S07].[ROS %])<=[forms]![Flt Rpt By Prod S07]![QryROSEnd]) AND (([Tbl-Flight Model Output (By Prod) S07].[LF Var])>=[forms]![Flt Rpt By Prod S07]![QryLFVarStart] And ([Tbl-Flight Model Output (By Prod) S07].[LF Var])<=[forms]![Flt Rpt By Prod S07]![QryLFVarEnd]) AND (([Tbl-Flight Model Output (By Prod) S07].Team_Models) Like [forms]![selection]![dest]));

 

[SydneyGeek]

Hi, and welcome to the Board!

To handle nulls, you need to wrap EACH field reference in a call to the NZ function. So, [ROSstart] becomes NZ([ROSstart],0) ...etc.
Otherwise, as you have found, Access will just ignore that record.

Denis

 

[giacomo]

look's like you have some normalization issues. I see several repeating sets (example [Mgn/Pax 2Wk], [Mgn/Pax 4Wk], [Mgn/Pax 6Wk], etc). I would bet you that this query could be simplified greatly if you followed a normalized database structure. Please read the link below on database normalization.

hth,
Giacomo

 

[loasbyp]

Thank you both for your replies, but the NZ option appears to have worked just great.

Thank you once again.

 

[giacomo]

Thank you both for your replies, but the NZ option appears to have worked just great.

Thank you once again.

I didn't mean to imply that Denis's solution wasn't going to work. I only wanted to bring to your attention that your table is poorly structured and if you don't fix it soon it's going to continue to cause you problems.

hth,
Giacomo

 

[loasbyp]

Totally understand and I'm actually reviewing the links you posted at present and will (hopefully!) be applying some of the logic shortly.

Cheers, P.

 

[loasbyp]

OK I've applied the NZ logic and all appears to be well. I know have a field being returned in my query that is

NZ([Tbl-Flight Model Output].[ROS %],0)

that brings back all records and when I apply the criteria

>=[forms]![Flt Rpt By Prod]![QryROSStart] AND <=[forms]![Flt Rpt By Prod]![QryROSEnd]

where QryROSStart is a zero value and QryROSEnd is one it continues to work fine (all records have between 0 and 100 held against them), however if I extend the range to -1 in QryROSStart the query doesn't return any rows!

Surely by extending the range (ie greater than or equal to minus one and less than or equal to plus one) shouldn't make a difference as all of the records are still within this range???

 

[giacomo]

I responded to your other post about this issue. I missed your post about it in this thread...

http://www.mrexcel.com/board2/viewtopic.php?t=247738&highlight=

hth,
Giacomo