In Options, Input, you should have the option to apply nz() to your database in Monarch. It's labelled "Replace null values with default values". The defaults are 0 for null numeric, "" for null strings, and 1/1/1900 for null dates. These defaults can be altered in the registry.
If this isn't flexible enough, you could import them as (null) and then use a calculated field in Monarch to replace nulls with another expression:
Ollie, thanks for the response however, the options in the model are already set that way. I learned that back in Monarch V6. I have used your suggestion on several other models in the past but that will not work in this instance because the entire query and not just one column that is not visible in Monarch.
When you open a database in Monarch you can see all the tables and queries associated with it but if you change a query to include the nz function that query disappears from the list therefore you cannot use it in Monarch! I even set up a query that calls that query and neither one of them show up in the list. That is the problem--disapearing queries.
Like I said, I was able to do a work around this time but there are databases that I access at times with Monarch that are developed and used by other associates as a regular database and if a query that I need has that function in it I cannot use it and cannot always create a work around query. Hence the problem.
The work around I have is in the query itself.
The original formula was adding a column from a table that had values that I needed to to a DLookUp("[Wash]","Disbursement Checks Wash","[Account] = '108102'") portion which originally was returning null values and thereby returning zero amount in error.
When I added the nz(dlookup(....),0) to the formula, it calculated everything correctly and was visible in the Access query window but disappeared in Monarch.
I ended up removing the nz() function and changing it to the following which returned the correct values and was visible in Monarch.
IIf(IsNull(DLookUp("[Wash]","Disbursement Checks Wash","[Account] = '108102'")),0,DLookUp("[Wash]","Disbursement Checks Wash","[Account] = '108102'")))
This works but the nz() function is much easier and less cumbersome to implement.
That is the weird part, when the query contains the nz() function, the entire query is no longer visible in Monarch and cannot be called even with scripting. I even created a query to call that query thinking that it would be visible in Monarch but that is not the case.
I would be interested if anyone else is experiencing the same thing.
Hmm. I wonder if this has something to do with how Monarch is developed with and/or employs [URL="http://bytes.com/topic/net/answers/791410-nz-not-working-vb-net"].Net[/URL]?
The [URL="http://en.allexperts.com/q/Using-MS-Access-1440/Access-functions-vb-net.htm"]whole[/URL] [URL="http://www.codeguru.com/forum/showthread.php?t=398956"]thing[/URL] seems really [URL="http://www.eggheadcafe.com/forumarchives/VisualBasicdatabase/Sep2005/post23635159.asp"]odd[/URL].