Jonie Bonfield
asked on
entering $0 when value is Null
SELECT cme.cmeID, cme.provID, providers.fullname, provtype.provtype, cme.FY, cme.totalfycme AS TotalCME, Sum(cmeexps.[$amt]) AS TotalExp, providers.lastname
FROM provtype INNER JOIN (providers INNER JOIN ((FYs INNER JOIN cme ON FYs.[FY] = cme.FY) INNER JOIN cmeexps ON cme.cmeID = cmeexps.cmeID) ON (providers.provID = cme.provID)) ON provtype.provtypeID = providers.provtypeID
GROUP BY cme.cmeID, cme.provID, providers.fullname, provtype.provtype, cme.FY, cme.totalfycme, providers.lastname, cme.fycme, cme.[$increquest]
HAVING (((cme.FY)="2018") AND ((Sum(cmeexps.[$amt])) Is Null Or (Sum(cmeexps.[$amt])) Is Not Null))
ORDER BY providers.lastname;
In the HAVING LINE it tells it to still show those people that have $0 in expenses, however it will not show the "$0" it just shows a blank. How do I tell it to show $0.00 when the person has no expenses?
FROM provtype INNER JOIN (providers INNER JOIN ((FYs INNER JOIN cme ON FYs.[FY] = cme.FY) INNER JOIN cmeexps ON cme.cmeID = cmeexps.cmeID) ON (providers.provID = cme.provID)) ON provtype.provtypeID = providers.provtypeID
GROUP BY cme.cmeID, cme.provID, providers.fullname, provtype.provtype, cme.FY, cme.totalfycme, providers.lastname, cme.fycme, cme.[$increquest]
HAVING (((cme.FY)="2018") AND ((Sum(cmeexps.[$amt])) Is Null Or (Sum(cmeexps.[$amt])) Is Not Null))
ORDER BY providers.lastname;
In the HAVING LINE it tells it to still show those people that have $0 in expenses, however it will not show the "$0" it just shows a blank. How do I tell it to show $0.00 when the person has no expenses?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
got ittttttt thank you so much, I have been scouring the google pages for weeks trying to get this. Its not super important, but it is my personal OCD kicking in.
So thank you all very much!!!!
So thank you all very much!!!!
>Unfortunately it also changed the other results too. They now do not show up as $
Let queries serve up the data such as 0, and not have to screw with cosmetic formatting.
Let the reporting layer (Access Form/Report, SSRS, whatever) deal with the cosmetic formatting.
Let queries serve up the data such as 0, and not have to screw with cosmetic formatting.
Let the reporting layer (Access Form/Report, SSRS, whatever) deal with the cosmetic formatting.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you're welcome, Jonie ~ happy to help. Please remember to Close the question
Jonie, you forgot to close the question
ASKER
Unfortunately it also changed the other results too. They now do not show up as $. Is there an easy way to make the results all currency format?