Group by null value error

[marcin2k]

Hello,
I have two tables that I am combining in a query and grouping the data.
Table1: OrderID Table2: OrderID
OrderDesc ItemID
ItemDesc
My query has a relationship set up so that it displays all records from Table1 and only those records from Table2 where the fields are equal (liked by ID)

THe problem is that if an order has no item (no data in table2) it shows under ItemID in my query "#Error". It does not allow me to group this field after wards and I have to be able to group it by OrderID and ItemID to display it properly in a report.

Any ideas of how to get around this?

THanks a lot

 

[GingerR]

For ItemID, you can make it a calculated field instead:

Item: iif([Table2]![OrderID] is null,0,[ItemID])

that's if ItemID is a number. If it's text, make it something like "None" instead of a 0.




Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

You may simply use the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[marcin2k]

Its a number field, i tried the IIF statement to check if its null and it does not do anything. ITs still displaying #Error inside the field ItemID since there is no record assoiciated to that specific order

 

[marcin2k]

Ok got around it, changed the field to a string and it seemed to work fine with Cstr

THanks for the ideas