Results 1 to 3 of 3

Thread: Time Efficiency

  1. #1
    Join Date
    Dec 2012

    Unanswered: Time Efficiency

    With the database that I'm working with I'm finding that having to search through ~100,000 records (and growing with each day) requires some time that significantly slows down work; there is a Yes/No value that would allow me to filter relevant vs. irrelevant records for general purpose day-to-day queries, while still keeping the older records to preserve their IDs and data in case we need them for future reference, but I'm not sure if the application that I'm going to try for will make it any more efficient. Would putting in what's essentially an "if 'no', ignore and move on" step before the "does this record have the ID number 19383" step make the searching any more efficient?

    Question is how do I code a button (checkbox) to 'ignore if Yes/No', depending on the checkbox being ticked or not, sending that info from the search form to the relevant query, and would that make the searching process any faster?
    Last edited by RedFredHunter; 12-10-12 at 22:30.

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 8
    Try a Google search for SQL Optimisation. I haven't done a lot on it yet, but I think that putting the boolean field earlier in the WHERE clause will speed things up for you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 1

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    a standard checkbox has one of two values either yes or no (or more accurately as far as VBA is concerned true / false
    the issue is how you retrieve the data
    you could modify the underlying SQL or you could apply a filter
    you could use a command button and toggle the text on the button

    for now I'd suggest trying to apply a filter (on the grounds its minimum coding effort)

    using the command button approach that controsl on click event
    if mycommandbutton.caption = "Show all Records" then 'we want to apply the filter to remove historic rows
      me.filter = 'mycolumn = ' & vbtrue
      me.filteron=true 'appl;y the filter
      'you may need to refresh the form... if so me.refresh
      mycommandbutton.caption = "Show current records" 
    else 'we want to remove the filter
      me.fitleron = false
      mycommandbutton.caption = "Show all records"
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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