10 Replies Latest reply: May 15, 2014 10:09 AM by adonis _ RSS

    Microsoft Access

    JOHN1961 _

      I have an MS Access database that has many tables and queries.  I am trying to open a query from the external database but the result returned zero rows but it does capture the columns from the queries.  If I choose a table it returns all of the rows data but nothing with a query.  Any idea what I might be doing wrong?

       

      Thank you.

        • Microsoft Access
          joey

          Sounds like one of the predicates in the where clause of your criteria is dropping all of the records in the table.

            • Microsoft Access
              elginreigner _

              I would agree. Does the query return results in Access? Is there variable data in the where clause that is not getting passed?

                • Microsoft Access
                  JOHN1961 _

                  The query does return results in Access.

                   

                  Thanks

                    • Microsoft Access
                      joey

                      Could you post the SQL query?

                      • Microsoft Access
                        JOHN1961 _

                        The Queries do have criteria such as amounts greater than zero or Name equals "FM*" where the * is a wildcard.  That must be why the data is not pulling because I have opened Access databases in Monarch before without any problems. 

                         

                        Thanks

                          • Microsoft Access
                            JOHN1961 _

                            Joey - I hope this is what you are asking for.  I found it in the SQL View of the query.  I'm just an accountant so I apologize that I may not be providing the correct info.

                             

                            SELECT tblFinName.Bank, tblFinName.OC, tblFinName.[DEAL NUM], tblFinName.[C#], tblFinName.Name, tblFinName.NU, tblFinName.[Stock#], tblFinName.Closer, tblFinName.Sls1, tblFinName.Sls2, tblFinName.SI, tblFinName.Setup, tblFinName.Date, tblFinName.Term, tblFinName.[Contract#], tblFinName.Recd, tblFinName.CBDate, tblFinName.ActCB, (-[DATE])/30 AS , IIf(=1,((((-[O/S]))(-[O/S]1))/2/(*(1)/2)),((((-[O/S]))(-[O/S]1))/2/(*(1)/2))(1+0.0026417777)) AS CompCB, IIf( Is Null Or <0.01,[Setup],0) AS , IIf( Is Null Or <0.01 And >0 Or Month()=Month() And Year()=Year(),IIf( Is Null,[Recd],[Recd]-[Setup]),0) AS , -[ActCB] AS CBDiff, IIf(=2,[Setup]-[Recd],IIf(<0.01 Or () Is Null Or Month()=Month() And Year()=Year(),[Setup],0)) AS , IIf(>0 And >0 Or Is Null,[Recd],0) AS PaidbyBank, tblFinName.Additional, tblFinName.CloserName, tblFinName.Sls1Name, tblFinName.Sls2Name, tblFinName.BuyRate, tblFinName.last8, Right(,3) AS LAST3

                            FROM tblFinName

                            WHERE (((tblFinName.Bank) Like "*") AND ((tblFinName.OC)=1))

                            ORDER BY tblFinName.[C#], IIf(=2,[Setup]-[Recd],IIf(<0.01 Or () Is Null Or Month()=Month() And Year()=Year(),[Setup],0));

                              • Microsoft Access
                                Olly Bond

                                Hello John,

                                 

                                My first thought would be to open the table tblFinName in Monarch - all the fields seem to come from there.

                                 

                                (-[DATE])/30 AS , IIf(=1,((((-[O/S]))(-[O/S]1))/2/(*(1)/2)),((((-[O/S]))(-[O/S]1))/2/(*(1)/2))(1+0.0026417777)) AS CompCB, IIf( Is Null Or <0.01,[Setup],0) AS , IIf( Is Null Or <0.01 And >0 Or Month()=Month() And Year()=Year(),IIf( Is Null,[Recd],[Recd]-[Setup]),0) AS , -[ActCB] AS CBDiff, IIf(=2,[Setup]-[Recd],IIf(<0.01 Or () Is Null Or Month()=Month() And Year()=Year(),[Setup],0)) AS , IIf(>0 And >0 Or Is Null,[Recd],0) AS PaidbyBank[/code]

                                 

                                looks like it could be handled with an external lookup if some of the fields aren't in that table, and with filters and calculated fields otherwise.

                                 

                                HTH

                                 

                                Olly

                                  • Microsoft Access
                                    joey

                                    My thought is condition tblFinName.Bank Like "*" is what is causing you problems. I think that is always true, or always true if the bank is not null. What are you trying to accomplish what that criteria?

                                     

                                    Try removing that statement and see if the query works.

                                      • Microsoft Access
                                        JOHN1961 _

                                        I'd like to thank everyone who helped.  I learned I can't use a query that itself has conditions in Monarch so I am going to use the table and perform the queries in Monarch.

                                          • Microsoft Access
                                            adonis _

                                            John

                                            what i like to do when using monarch is i load data from monarch into a table using a batch file, and then i create a MS Access Macro which i then in turn execute via the same batch file.  It also executes the various queries and then the TransferSpreadsheet (choosing export in the bottom) allows me to place that data into excel.

                                            Sometimes you can also use the TransferSpreadsheet (chosing import to bring a file into access which is also usefull)

                                            The macro specifies the location of the output, and whether there are many tabs or just one, and the naming conventions as well.

                                            I usually append the system date to the file, which helps as well.

                                            otherwise, you could create the process and have the batch file specify the name etc within the batch file which i do in many cases, usually not involving MS Access though if it is strictly a quick Monarch Hit, then i do it then.

                                            these are suggestions which you can think about in the future.