How to constrain/define an "AS" value in a query

[TOPSie]

Sorry folks, I am not sure how to phrase this as a google-able search.



I have the following line in a Query:-

Sum(Nz([tblNominalEntries].[Ammountdr]))-Sum(Nz([tblNominalEntries].[Ammountcr])) AS Balance,

in tblNominalEntries both Ammountdr and Ammountcr are defined as Currency

So one would expect Balance to be Currency - but it isn't. Here is a line from the Query output:-

Number	SumOfAmmountCr	SumOfAmmountDr	Name	Balance	BSType
1200	£3,105,249.98	£3,152,875.87	Bank Current Account	47625.8900000001	Current Assets

How do you tell Access/SQL the "Type" of the AS bit in such an expression?

Thanks

[Micron]

Try Ccur(Sum(Nz([tblNominalEntries].[Ammountdr]))-Sum(Nz([tblNominalEntries].[Ammountcr])))

It's interesting that you get that result with 2 currency fields.

EDIT - likely because some of the records contain null, so Nz without a [value if null] parameter is returning a zls, so you end up with a string result.
Maybe you should use Nz([tblNominalEntries].[Ammountdr],0) and if you don't like negative results that you might get, convert those with Abs function. Or just use Ccur I guess.

[CJ_London]

sorry micron

so Nz without a [value if null] parameter is returning a zls

not true, nz will return a value in context

?nz(null)=0
True
?nz(null)=""
True


?nz(null)+25
25
?nz(null)*25
0
?nz(null)+"abc"
abc
?nz(null) & "abc"
abc

and without the nz function
null+25
Null
?null*25
Null

?null+"abc"
Null
?null & "abc"
abc

[Micron]

I think I said that incorrectly. I was thinking that since this was about a query and the if the table field is null, then that is what should happen. However, the query field is based on an expression, which may or may not give it that context. Probably not in cases where both fields in the expression are null. Regardless, I cannot think of a time when I left it up to Access to determine my context, so have always provided the [value if null] parameter.

[Edgar]

Access probably stores currency in a funny way, so why not just rounding 4 decimals to make sure the last fractional part is ignored?
Round(Sum(Nz([tblNominalEntries].[Ammountdr])) - Sum(Nz([tblNominalEntries].[Ammountcr])), 4) AS Balance

[CJ_London]

just some more examples building up to the initial calculation

?vartype(null)
1
?vartype(nz(null))
0
?vartype(nz(null)+25)
2
?vartype(nz(null)+nz(25))
2
?vartype(nz(null)+nz(25.05))
5
?vartype(nz(null)+nz(ccur(25.05)))
6
?vartype(ccur(nz(null)+nz(25.05)))
6

vartype enums can be found here https://support.microsoft.com/en-us/...3-2b894389e82d

0=Empty (uninitialized)
1=Null (no valid data)
2=Integer
5=Double-precision floating-point number
6=Currency value

so in vba the type is currency, however I suspect in sql, because of the summing and perhaps the existence of round numbers it defaults to double. As an experiment, suggest create an unbound control on a form and put your calculation there. Alternatively in the query, set the format property to currency. At the end of the day, currency is basically a double with only 4dp when stored in a table, just with the format option of including a currency symbol.

[NoellaG]

To get it as a currency: you can set the format property of the field in the query design window to currency. Right click the column in the design window, choose properties and set the format property to currency