Substitute null on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 19 Jan 2008 12:19 GMT

I have a form "frmRepairerTotals" - based on a query "qryRepairerTotals"
which has two fields :- Repairer1 (text) and RepTotal (GroupBy Sum). This
query is based on a union query "qryRepairAmounts" which has the following
SQL statement :-

SELECT Repairer1,
IIF(Repairer2 Is Null, [Total], [Total]/2) As Reptotal
FROM [JobDetails Query]
UNION ALL SELECT Repairer2 As Repairer1,
([Total]/2) As RepTotal
FROM [JobDetails Query]
WHERE Repairer2 Is Not Null;

Repairer1 can be null, and at present shows that way in my form. I would
like if possible to have a value of "Unclaimed" in the Repairer1 field in my
form in lieu of the current null - is this possible?

Sandy

Reply to this Message

Bob Quintal - 19 Jan 2008 12:03 GMT

> I have a form "frmRepairerTotals" - based on a query
> "qryRepairerTotals" which has two fields :- Repairer1 (text) and
[quoted text clipped - 15 lines]
>
> Sandy

the Nz() function allows the substitution of a value for null.
SELECT Nz(JobDetails Query.repairer1,"Unclaimed" as repairer1, ...

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message

Sandy - 19 Jan 2008 13:12 GMT

Bob

Thank you - does the job perfectly.

Sandy

>> I have a form "frmRepairerTotals" - based on a query
>> "qryRepairerTotals" which has two fields :- Repairer1 (text) and
[quoted text clipped - 18 lines]
> the Nz() function allows the substitution of a value for null.
> SELECT Nz(JobDetails Query.repairer1,"Unclaimed" as repairer1, ...

Reply to this Message