Illogical number output

[Guest]

Hi

I have a report which has various calculated fields all of which are done in the underlying SQL statement

All calculations work correctly, however 2 fields (of about 10) show a null value when equal to zero. I've tried formatting as follows
#,##0.00;-#,##0.00;#,##0.00;#,##0.0

Odd thiing is when I run the underlying SQL values display as 0 which is what I want
Can anyone help please - driving me bonkers, I thought the calculations were the hard bit!

Thanks

Sheila

 

[Duane Hookom]

When you view the underlying SQL datasheet view, are the values left or
right aligned? If they are left aligned, they are being treated as text. You
would need to wrap your expression in Val(...).

--
Duane Hookom
MS Access MVP

Hi

I have a report which has various calculated fields all of which are done

in the underlying SQL statement.

All calculations work correctly, however 2 fields (of about 10) show a

null value when equal to zero. I've tried formatting as follows:

 

[Marshall Barton]

I have a report which has various calculated fields all of which are done in the underlying SQL statement.

All calculations work correctly, however 2 fields (of about 10) show a null value when equal to zero. I've tried formatting as follows:
#,##0.00;-#,##0.00;#,##0.00;#,##0.00

Where is that format? It should be in the text box in the
report.

You need to use a different format in the null part:
#,##0.00;;;"0.00"

Note, you don't need to repeat a format that is the same as
the first one.

Odd thiing is when I run the underlying SQL values display as 0 which is what I want.

You may have a format specified in the table or query. If
so, it will be used as the default format when you create a
new query or text box, but it won't be applied to existing
objects.

 

[Guest]

Tried formatting suggestion but made no difference. Definitely right aligned - if it helps SQL is as follows

SELECT T_Invoices.[Event Code], T_Invoices.[Type of Sale], [T_Payment Schedule].[Invoice ID], [T_Company Details].[Company Name], T_Invoices.[Contract Signed and Returned], T_Invoices.[Stand Hall/Number], [Stand Height]*[Stand Width] AS [Size], T_Invoices.[Price per Square Metre], T_Invoices.[Net Cost] AS [100% Stand Rental], [Net Cost]*0.1 AS [Initial Stand Due], IIf([SUmofAmount Paid]>[Initial Stand Due],[Initial Stand Due],[SumofAmount Paid]) AS [Initial Stand Paid], [Initial Stand Due]-[Initial Stand Paid] AS [Initial Stand Outstanding], Max([T_Payment Schedule].[Service Charge Paid]) AS [MaxOfService Charge Paid], [MaxofService Charge]-[MaxofService Charge Paid] AS [Service Charge Outstanding], Max([T_Payment Schedule].[Service Charge]) AS [MaxOfService Charge], [SumofAmount Paid] AS [Total Stand Revenue Paid], [Net Cost]-[SumofAmount Paid] AS [Total Stand Revenue Outstanding], [Total Stand Revenue Outstanding]+[Service Charge Outstanding] AS [Total Balance Outstanding], Min([T_Payment Schedule].[Invoice Cancelled]) AS [MinOfInvoice Cancelled], T_Invoices.[Stand Height], T_Invoices.[Stand Width], T_Invoices.[Net Cost], Sum([T_Payment Schedule].[Amount Paid]) AS [SumOfAmount Paid], T_Invoices.[Sales Person], T_Invoices.[Stand Type
FROM ([T_Company Details] INNER JOIN [T_Contact Details] ON [T_Company Details].[Company Code] = [T_Contact Details].[Company Code]) INNER JOIN (T_Invoices INNER JOIN [T_Payment Schedule] ON T_Invoices.[Invoice ID] = [T_Payment Schedule].[Invoice ID]) ON [T_Contact Details].[COntact Code] = T_Invoices.[Contact Code
GROUP BY T_Invoices.[Event Code], T_Invoices.[Type of Sale], [T_Payment Schedule].[Invoice ID], [T_Company Details].[Company Name], T_Invoices.[Contract Signed and Returned], T_Invoices.[Stand Hall/Number], T_Invoices.[Price per Square Metre], T_Invoices.[Net Cost], T_Invoices.[Stand Height], T_Invoices.[Stand Width], T_Invoices.[Sales Person], T_Invoices.[Stand Type
HAVING (((T_Invoices.[Event Code]) Not Like "BASE") AND ((T_Invoices.[Type of Sale])="EXHIBITION STAND"))

2 fields that are giving me the problem are [Initial Stand Outstanding] and [MaxofService Charge Paid

Thanks for your hel
Sheila

 

[Duane Hookom]

I don't use a calculated column when defining another calculated column. You
use [Initial Stand Paid] to calculate [Initial Stand Outstanding] .
..
--
Duane Hookom
MS Access MVP

Tried formatting suggestion but made no difference. Definitely right

aligned - if it helps SQL is as follows:

SELECT T_Invoices.[Event Code], T_Invoices.[Type of Sale], [T_Payment

Schedule].[Invoice ID], [T_Company Details].[Company Name],
T_Invoices.[Contract Signed and Returned], T_Invoices.[Stand Hall/Number],
[Stand Height]*[Stand Width] AS [Size], T_Invoices.[Price per Square Metre],
T_Invoices.[Net Cost] AS [100% Stand Rental], [Net Cost]*0.1 AS [Initial
Stand Due], IIf([SUmofAmount Paid]>[Initial Stand Due],[Initial Stand
Due],[SumofAmount Paid]) AS [Initial Stand Paid], [Initial Stand
Due]-[Initial Stand Paid] AS [Initial Stand Outstanding], Max([T_Payment
Schedule].[Service Charge Paid]) AS [MaxOfService Charge Paid],
[MaxofService Charge]-[MaxofService Charge Paid] AS [Service Charge
Outstanding], Max([T_Payment Schedule].[Service Charge]) AS [MaxOfService
Charge], [SumofAmount Paid] AS [Total Stand Revenue Paid], [Net
Cost]-[SumofAmount Paid] AS [Total Stand Revenue Outstanding], [Total Stand
Revenue Outstanding]+[Service Charge Outstanding] AS [Total Balance
Outstanding], Min([T_Payment Schedule].[Invoice Cancelled]) AS [MinOfInvoice
Cancelled], T_Invoices.[Stand Height], T_Invoices.[Stand Width],
T_Invoices.[Net Cost], Sum([T_Payment Schedule].[Amount Paid]) AS
[SumOfAmount Paid], T_Invoices.[Sales Person], T_Invoices.[Stand Type]

FROM ([T_Company Details] INNER JOIN [T_Contact Details] ON [T_Company

Details].[Company Code] = [T_Contact Details].[Company Code]) INNER JOIN
(T_Invoices INNER JOIN [T_Payment Schedule] ON T_Invoices.[Invoice ID] =
[T_Payment Schedule].[Invoice ID]) ON [T_Contact Details].[COntact Code] =
T_Invoices.[Contact Code]

GROUP BY T_Invoices.[Event Code], T_Invoices.[Type of Sale], [T_Payment

Schedule].[Invoice ID], [T_Company Details].[Company Name],
T_Invoices.[Contract Signed and Returned], T_Invoices.[Stand Hall/Number],
T_Invoices.[Price per Square Metre], T_Invoices.[Net Cost],
T_Invoices.[Stand Height], T_Invoices.[Stand Width], T_Invoices.[Sales
Person], T_Invoices.[Stand Type]

HAVING (((T_Invoices.[Event Code]) Not Like "BASE") AND ((T_Invoices.[Type

of Sale])="EXHIBITION STAND"));

2 fields that are giving me the problem are [Initial Stand Outstanding] and [MaxofService Charge Paid]

Thanks for your help
Sheila

 

[Guest]

So are you saying this is what is causing the error? Can't see why it would - the second one with the problem is not calculated. Thanks for responding anyway.

Sheila

 

[Duane Hookom]

Rather than ask a question that I can't test, why don't you test it yourself
and report back? I would like to find out if it works. My experience has
been to never use a calculated "Alias" in another calculation in a query. I
haven't experienced the issue you are having. This isn't conclusive proof
that this will work but it should be worth a try. I make lots of attempts
that don't work but hopefully I learn something from them.

--
Duane Hookom
MS Access MVP

So are you saying this is what is causing the error? Can't see why it

would - the second one with the problem is not calculated. Thanks for
responding anyway.