Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    12

    Unanswered: How to make such a SCANNER by making use of Access Database Table?

    How to make such a SCANNER by making use of Access Database Table?

    A lot of stock market websites give us the ability for scanning the stocks which fulfill particular criteria / conditions etc. based on different parameters. Just as shown in these snapshots

    Stock Screener - TA smallover channelup2 nob50 pb
    http://i58.tinypic.com/517x5i.png
    http://i62.tinypic.com/27ymp6u.png

    I want to make something similar for my personal offline use. I have already got the required data for all the fields in my access database in one single table itself.

    What are the various options available for creating such a Scanner, either inside access itself or in any other 3rd party platform. Because if it is not possible to design something similar inside Access, then I am also open to the idea of using access data table as a back end and using any 3 rd party app as a front end if that can do this work very efficiently.

    I hope that it will give me the ability to apply 10+ filters easily at one go, just by using the drop down type of feature, without having to switch back into the query design mode again and again for changing the filter parameters.

    My current method of using a simple query in which I apply all these filters, is taking me a lot of time and effort, for changing the filter values again and again. So the current method is extremely inefficient. Therefor I am looking for an easier way to get the final query output data, which shows the filtered results and in which I can very easily change the filter conditions quickly.

    Any ideas in this regard are welcome.

    Thanks a lot
    Attached Thumbnails Attached Thumbnails Scanner 1.png   Scanner 2.png  

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Wow, somebody else asked the exact same question this morning elsewhere.

    My answer to them:

    My tendency with a lot of optional parameters is to build dynamic SQL, as demonstrated in the sample db here:

    Building SQL in VBA

    It doesn't have to drive a subform, it could open a report or whatever, just demonstrating the concept of building SQL based on selections.
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use what's called dynamic queries (How to build a dynamic query with values from a search form in Access 2007, in Access 2003, or in Access 2002, How to implement Query by Form (QBF) with dynamic QueryDef in Access 2000). This mean that you build (or change) the SQL string that expresses the query according to the values that appear in a form. You can also use a parameters query. In both cases you use a DAO.Querydef objects. For a dynamic query, you assign the SQL String to the SQL property of the Querydef. With a parameters query, you loop through the Parameters collection of the Querydef to assign the value of each parameter. Depending on your skills in Access and VBA, you could also have a look at: Access Help and How-to - Microsoft Office Creating Dynamic Queries using Class Modules - UtterAccess Discussion Forums
    Have a nice day!

  4. #4
    Join Date
    Oct 2012
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    Wow, somebody else asked the exact same question this morning elsewhere.

    My answer to them:

    My tendency with a lot of optional parameters is to build dynamic SQL, as demonstrated in the sample db here:

    Building SQL in VBA

    It doesn't have to drive a subform, it could open a report or whatever, just demonstrating the concept of building SQL based on selections.
    Thanks pbaldy

    Checking out the sample db.

  5. #5
    Join Date
    Oct 2012
    Posts
    12

    Thumbs up

    Quote Originally Posted by Sinndho View Post
    You can use what's called dynamic queries (How to build a dynamic query with values from a search form in Access 2007, in Access 2003, or in Access 2002, How to implement Query by Form (QBF) with dynamic QueryDef in Access 2000). This mean that you build (or change) the SQL string that expresses the query according to the values that appear in a form. You can also use a parameters query. In both cases you use a DAO.Querydef objects. For a dynamic query, you assign the SQL String to the SQL property of the Querydef. With a parameters query, you loop through the Parameters collection of the Querydef to assign the value of each parameter. Depending on your skills in Access and VBA, you could also have a look at: Access Help and How-to - Microsoft Office Creating Dynamic Queries using Class Modules - UtterAccess Discussion Forums
    Thank you Sinndho for providing the valuable links. Very Helpful.

  6. #6
    Join Date
    Oct 2012
    Posts
    12
    And yes, that was me too.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •