Solved Handling Null Values

[Emma35]

Hi All,
I have a database which deals with various chemicals. I want to print a report listing all the chemicals in the database but need to put the chemicals with the lowest melting point at the top of the report. My problem is that some chemicals have no melting point and the field is just blank. I can't add N/A to a number field. Anyway, the blanks are showing up randomly on the report....is there anyway i can filter them out altogether ?. In other words, if the field value for melting point is just blank, then that chemical is omitted from the report........or else pushed down to the bottom ?

Thanks a lot

 

[Gasman]

Excude Nulls in the report source query.
Otherwise use NZ() with perhaps a value of 9999, something higher than actual highest melting point for the other chemicals.

Then they will be at the bottom if you sort ascending in the report.

 

[plog]

In the query you can accomplish everything you asked:

is there anyway i can filter them out altogether ?

...WHERE [MeltingPoint] IS NOT NULL

.or else pushed down to the bottom ?

SortOrder: IIf(Isnull([MeltingPoint]), -1, [MeltingPoint])

Then sort your report by SortOrder Descending.

 

[XPS35]

IIF is your best friend

SELECT Chemical, IIf(IsNull([MeltingPoint]),"N/A",[MeltingPoint]) AS MeltingPointShow
FROM tblChemical
ORDER BY IIf(IsNull([MeltingPoint]),999999,[MeltingPoint]);

 

[The_Doc_Man]

Just as a point of comment, all chemicals have melting/freezing points, evaporation/condensation points, or deliquescent/coalescent points or even "triple" points. It may be, however, that you don't know them.

You could use NULL to show "unknown" in which case you could use the NZ function. It IS fair to use NZ([MeltingPoint], 9999) to put a chemical's melting point high enough that ordinary chemicals would appear first when sorted in ascending order.

 

[CJ_London]

Have an extra column and use the nz function

sortby:nz(-meltingpoint,0)

note the minus sign

then sort it descending

you don’t have to show the column

 

[arnelgp]

Report will Ignores your Sorting Order in Query/Table.
so you need to Set the Sort Order in the Design View of your Report.

you Choose Expression from the field list. and the Expression is:

Nz([meltingPointField], 99999)

 

[Galaxiom]

IIf(Isnull([MeltingPoint]), -1, [MeltingPoint])

It is better to use

Iif(MeltingPoint Is Null, .......).

IsNull is a VBA function, while Is Null is a native SQL expression and more efficient.

BTW I would not use -1 since many materials have a melting point below zero. (Unless of course you record the temperature in Kelvin.)

 

[Emma35]

Hi....sorry guys i was away for a couple of days. I know i said Melting Point in the OP but it should have been Flash Point........doesn't make much difference but i apologise anyway. I already had a report deigned for this so i thought the best option would be to add the expression to the report........it works and all the blanks are down the bottom but it still doesn't organise the report in ascending order (in other words the chemicals with flash points which have a minus number should be at the top ).

Thanks everyone for the suggestions

 

[XPS35]

i thought the best option would be to add the expression to the report........it works and all the blanks are down the bottom but it still doesn't organise the report in ascending order (in other words the chemicals with flash points which have a minus number should be at the top ).

What expression are you using? You have got several suggestions.

 

[Gasman]

Reports have their orwn sort option. They do not take the source option.
Add a sort in the report.

 

[arnelgp]

maybe your meltinpoint field is Text?

 

[Emma35]

Ok my Flash Point field is Number, Field Size is Double and Format is General Number. The expression in the report i'm using is =Nz([FlashPoint(C°)],99999). I've attached a screenshot of how the flash point part of the report is filtered

 

[arnelgp]

try:

=Val(Nz([FlashPoint(C°)],99999))

 

[Emma35]

That's working fine now...thanks arnel.
Thank you everyone for the great suggestions and your time. I appreciate it as always
Em xx