Results 1 to 7 of 7
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    Queries majorly slower using form?

    I've noticed that when i run my queries using a form (really a textbox) as a date range, my queries take forever to run whereas before using the form, it would take literally seconds. Any idea to speed this up? Thanks in advance.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You need to provide more details otherwise anyone answering is just guessing

    What type of query? How many fields / records?
    Are you running the query from the form or its SQL equivalent?
    Suggest you include your query SQL

    From what you've said so far, I would suggest using two textboxes for the start & end dates of your date range
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Apologies for the late reply and unclear example. I have quite a bit of queries, but the one I was working specifically on the form at the moment has 20 fields, 6 rows of criteria utilized in 5 of the fields. 2 of the fields contain simple expressions while another 2 contain more in-depth expressions. The query is a regular select query and is currently being ran in a subform on the form. When run from the form, I am using the code:
    Code:
    me.subform.sourceobject = "Query." & "Query Name"
    . This query run through SQL runs in seconds, but as soon as I run it through the form using the textbox input as the date ranges, the runtime is considerably slower. To the point of inconvenience and a waste of time even using the form (although this isn't my goal/hopes).

  4. #4
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    So I found where the problem is lying. For the query run through my form, the date filter is:
    Code:
    >=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate]
    This runs perfectly and fast, but as soon as I change it to:
    Code:
    IIf([Forms]![Invoices]![yestsel]=True,Date()-1,>=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate])
    It begins to take ages!

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by schulzy175 View Post
    So I found where the problem is lying. For the query run through my form, the date filter is:
    Code:
    >=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate]
    This runs perfectly and fast, but as soon as I change it to:
    Code:
    IIf([Forms]![Invoices]![yestsel]=True,Date()-1,>=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate])
    It begins to take ages!
    I'm surprised it completes at all. First of all what is the point of a date range >=from date and >=todate
    It should be <=todate.

    Now consider your iif statement.
    If the condition is true it will enter yesterday's date which is fine.
    However what do you want it to enter when the condition is false?
    Using any date range here is wrong
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    I used a simple workaround for the date ranges and in regards to the >=todate, that was just a typo on my part. I understand what that would be doing. The way it should all work is if the checkbox yesterday is checked, the query will be ran for yesterday, but if left unchecked, the query will be run through the fromdate and todate specified by the user. The workaround I used was if yesterday is checked, it will enter yesterdays date in the fromdate and todate, but if left unchecked then the user is able to set the date range. So in the end, the queries logdate is run only through
    Code:
    >=[Forms]![Invoices]![fromdate] And <=[Forms]![Invoices]![todate]
    rather than the iif statement.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Perhaps that makes sense in context but it seems a very odd approach to me.
    Good luck with your project
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-29-2017, 04:24 PM
  2. Replies: 2
    Last Post: 03-20-2013, 06:52 AM
  3. incrementally slower routine -- vba
    By bill shockley in forum Access
    Replies: 2
    Last Post: 07-01-2012, 02:12 PM
  4. Database slower under Windows 7
    By krzycho23 in forum Access
    Replies: 5
    Last Post: 04-27-2012, 01:07 AM
  5. Query filters slower, if left open
    By Frenotx in forum Queries
    Replies: 5
    Last Post: 01-25-2012, 03:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums