A
Allen Browne
Typecast the field. Do not use Format(), because that outputs Text.
Access determines the data type of the column by examining the first few
rows from first SELECT statement in the UNION query.
If they are all Null, it may treat the type as text. If it is a calculated
field, the type can be incorrectly identified. You can work around these
issues by explicitly typecasting with CCur(), CLng(), CDbl(), CVDate(), etc.
Example:
Diff: CCur(Nz([Credit] - [Debit],0))
The Nz() is needed because - with the exception of CVDate() - these
functions cannot handle Nulls.
If you need Nulls in the field, you may be able to get away with something
like this:
MyCalcField: IIf((False), CCur(0), Null)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
news:[email protected]...
Access determines the data type of the column by examining the first few
rows from first SELECT statement in the UNION query.
If they are all Null, it may treat the type as text. If it is a calculated
field, the type can be incorrectly identified. You can work around these
issues by explicitly typecasting with CCur(), CLng(), CDbl(), CVDate(), etc.
Example:
Diff: CCur(Nz([Credit] - [Debit],0))
The Nz() is needed because - with the exception of CVDate() - these
functions cannot handle Nulls.
If you need Nulls in the field, you may be able to get away with something
like this:
MyCalcField: IIf((False), CCur(0), Null)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
news:[email protected]...