Queries :: Soft Coded Date Filter Causing Query To Run Slow
Jan 2, 2015
created a query (in Access 2010) that joins several linked tables (to an Oracle database). The query runs in about 20 seconds when I filter with a hard coded date (e.g., #12/31/2014#). The Oracle table column Im filtering on is defined as date/time.
When I attempt to change the hard coded value to a soft coded value (e.g., Forms![Form1]![Latest_Extract_Date]), the query runs over 5 minutes. In this case, the form field has the exact same value (12/31/2014).
Ive encountered similar issues using Access 2000, 2003, etc. This is quite frustrating. Does Access interpret #date value# is a special way? Is there a way to trick Access into the thinking a soft coded date is a hard coded date?
Hey firstly im at the end of the project ive spent months on and thats a MIS datase software. with all the bits and pieces and expert advise ive managed to learn a whole lot of things through this forum.
This is the problem i am having though;
i have a form that has two subforms embedded and since i have to use link tables because i placed the database file over a network somehow the loading seems to take forever, "it actually makes access inactive for about 50 seconds"
i would like to prevent the subforms from loading with the main form until after the main form is up and then i just click a button that makes both of them "appear" and load.
I have a database with a table that contains 360,000 rows. I built a form with four boxes where a user can specify values to limit the result set. And instead of having a new window open with the results, I built a subform and placed it on the main form to display the results.
Here is how it flows: Main Form -> user enters search criteria Search Criteria -> feed as criteria in query Query Results -> display on subform Subform -> shows on main form
The query is setup to take the values from the main form and either use it if it's not null, or return all values if the field is null.
The problem I am having is that on opening the main form, Access is taking the four null values from the main form search fields, feeding them to the query, which is then feeding the subform. So 10 minutes later when the main form finally opens, I have 360K records displaying in my subform.
What I would like is to be able to open the main form instantly, specify my search criteria, then run the query, then have the query results populate the subform.
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
I ran some code to number old records in a column to order items in a datasheet subform however after updating the column in the inventory transactions table when opening the main form that relies on it. It runs a query to determine the customers balance due and the query for that is taking forever to run like 2-3 minutes or more however reverting to an old copy of the data it runs fine and all I did was renumber records in one column most of which had 1s in them the code to re-number the records:
Code: Dim rst As DAO.Recordset Dim i As Long Dim OID As Long Dim stringSQL as String i = 1
[Code] .....
I continued playing with this today and the query that is breaking the 2nd level subquery that actually references the inventory transactions table that I changed runs fine but go up a level to subquery1 which references subquery2 and a sum of payments query and it chokes however sum of payments only refernces the payments table.
I did some more testing today Subquery2 and sum of payments query both run faster than what I can time with a stopwatch but when combined they take approximately 1:45 there are only 5 columns in subquery1 four from subquery2 and 1 from the sum of payments 3 of the first 4 are sum and the last is group by and then the one from sum of payments is group by
Subquery 2:
Code: SELECT CLng((nz([UnitsSold])*nz([UnitPrice]))*(1-nz([Discount]))*100)/100 AS [Line Total], CLng([Line Total]*(1+nz([SalesTaxRate]))*100)/100 AS [Line Total With Tax], [Line Total With Tax]-[Line Total] AS [Line Tax], [Inventory Transactions].*
[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null
Ordinarily this works fine in returning either the selected value or all values if left null.
I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].
The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.
If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.
The crosstab data isn't specifically the problem but needing to set the parameter names seems to be
I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels
It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...
If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)
If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.
Currently I have a combo box in my access 2007 application which is drawing its value through a table using a SQL Query. I would also like it to show up a hard coded value in addition to the current list.
I have got the following query but when it actually inserts the date, the dates will be "08/01/2005". Month and day will be swapped if date is less than 12. I don't know it's happening. It's driving me crazy.
It will insert "8/01/2005" for the first date and "21/08/2005" for the second date. my date format is set to australia and i have set the same in my asp page as well. it is giving the same result even if i try direct from the query.
I have 2 tables. One for customer details. With customer ID,Company Name, Customer Name and Address.The other for sales details with Order ID, Customer ID, salesprice and sales date.I would like to have a list of customer ID, Company name,total sales last year(sum of salesprice for 2013), Total sales till date(Jan 2014 til now). All should include customers details who have not made any sale as well.
I have form that user can filter the records and generate a report but I have difficult trying filter null date.
If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
I have a query with a field defined in the query as follows:
Next Bill Date: IIf(IsNull([dtmLastBillDate]), DateSerial(Year([dtmDateRecd]),Month([dtmDateRecd])+1,15), DateSerial(Year([dtmLastBillDate]),Month([dtmLastBillDate])+[lngNumMonths],15))
I can't seem to set any criteria for this field without getting an error: 'Data Type Mismatch in Criteria Expression'
Examples of criteria that I've tried: =#1/1/2005# >#1/1/2005# =Date() Year([Next Bill Date]) = Year(Now())
All of the above generate that same error. {I've seen many pages/posts regarding 'data type mismatch' but none seem to relate directly to this situation.}
I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.
Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.To make matters more frightening, this is Access 2003!
[T_Hardwarelist] ID (primary key, autonumber) Make Model Description
[T_UserHardware] AT Reference ID
[Request] is inner joined to [UserHardware] on the AT Reference column. [UserHardware] is inner joined to [Hardwarelist] on the ID column.
The select query I have is basic and does just what it says on the tin; it shows who has what hardware.The query:
Code: SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
However, when it comes to adding NEW hardware to the requestI have a form called F_Request.I have a sub form called SF_Hardware.The SF_ Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.
I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.
I m trying to make form which filters my records and generates a report..here's where I am
Code: Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Hi. Im struggling to work out how to form a suitable WHERE clause in a query.
What I wish to return are records where a date stored in a field called "started" falls into the current month.
E.g. if the current month is January 08 (which I believe it is!) all records where the month and year part of "Started" equals January and 2008 respectively.
In a separate query (and I think this is a little trickier), to return all records "started" during the previous month. E.g. (assuming Jan 08 again) where Month is December and Year is 2007.
Hi all, Please help me correct this criteria for a filter in my query. I want to show all records between todays date and the past 14 days. I've tried,
Im doing this program for a post office and stuff, their DB is Oracle and they want to front end it in Access coz its free or bundled w/ office.
Im using the almost fossilized Access 2000, the laptop Im working on is a2.6 ghz Centrino w/ 1024 MB DDR RAM abd 250 GB HD.
Now, since that I am sure that the machine's specs has nothing to do with it, I want to ask why are my queries so slow?
I have 15 tables each w/ almot 20K records.
So when I query something like this. SELECT KYOKUSYOCD AS 局所コード, BUNSITUCD AS 分室コード, REREKINO AS 履歴番号, TOKUISAKICD AS 得意先コード, TRIKAMCD AS 取扱科目コード, '得意先局所対応マスタTTOKUKMD01 の局所コード = ' & KYOKUSYOCD & ' と分室コード = ' & BUNSITUCD & ' のペアが、局所マスタTKYOKUMD01 の局所コードと分室コードとして存在していません。' AS エラー内容 FROM TTOKUKMD01 WHERE KYOKUSYOCD & BUNSITUCD NOT IN (SELECT KYOKUSYOCD & BUNSITUCD FROM TKYOKUMD01)
UNION ALL
SELECT KYOKUSYOCD, BUNSITUCD, REREKINO, TOKUISAKICD, TRIKAMCD,'得意先局所対応マスタTTOKUKMD01 の得意先コード = ' & TOKUISAKICD &' が、得意先マスタTTOKUIMD01 の得意先コードとして存在していません。' AS WHATERROR FROM TTOKUKMD01 WHERE TOKUISAKICD NOT IN (SELECT TOKUISAKICD FROM TTOKUIMD01)
UNION ALL
SELECT KYOKUSYOCD, BUNSITUCD, REREKINO, TOKUISAKICD, TRIKAMCD, '得意先局所対応マスタTTOKUKMD01 の得意先コード = ' & TOKUISAKICD & ' が、得意先マスタTTOKUIMD01 の得意先コードとして存在していません。' AS WHATERROR FROM TTOKUKMD01 WHERE TOKUISAKICD NOT IN (SELECT TOKUISITENCD FROM TTORIKMD01)
UNION ALL SELECT KYOKUSYOCD, BUNSITUCD, REREKINO, TOKUISAKICD, TRIKAMCD, '得意先局所対応マスタTTOKUKMD01 の取扱科目コード= ' & TRIKAMCD & ' が、取扱科目マスタTTORIKMD01 の取扱科目コードとして存在していません。' AS WHATERROR FROM TTOKUKMD01 WHERE TRIKAMCD NOT IN (SELECT TRIKAMCD FROM TTORIKMD01);
Its soooooo slow, as in 6 minutes loading slow, windows xp running on pentium 2 slow, very very... ok you guys get it already.
Please help me coz Im losing my mind already. I did one version in SQL Server and it worked just fine. Sad thing is, they lvoe their VBA apps coz it can be like engraved in Excel or something. Anyway please help
Below is the SQL I have on a Union Query. Each Query is based on a date range. The first Query date is Planned Immplementation Date. The second Query date is Revised Planned Implementation Date. If the Date in the Revised Planned Implementation Date is higher than the EndDateTxt Date Range I do not want the results to appear for that ECN. Any suggestions on how to accomplish this?
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process] FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID] WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes")) ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number] UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process] FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID] WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes")) ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
