7 Replies Latest reply: May 15, 2014 10:06 AM by Data Kruncher RSS

    Monarch not reading Access query

    RalphB _

      I had been importing an Access query into Monarch successfully but due to change in query requirements, I had to modify the query and add the nz() function.  When I did that, Monarch cannot find the query.  The database is an Access 2000 db and I am using Access 2003 and Monarch 10.5 Pro.

       

      I thought maybe I had a problem with the software at work so I tested it at home with a different db (Access 2000) with a new query that had the nz() function and Monarch 9.1 Pro could not find that one either so the problem is not with V10.5.

       

      I was able to do a workaround but would be nice to be able to use the nz() function.

       

      Has anyone else had this problem?

       

      Maybe this could be looked at for future Monarch upgrades.

        • Monarch not reading Access query
          Olly Bond

          Dear Ralph,

           

          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:

           

          if(isnull()=1;[expression];[field1])

           

          HTH,

           

          Olly

            • Monarch not reading Access query
              RalphB _

              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.