0

Initially there were 1000 records to evaluate.Now there are 40000.Please help! I'm only trying to obtain the week number of a transaction based on transaction date and start date.

SELECT [1_Webtime_By_Date].Badge,Int(((([1_Webtime_By_Date].Date-Forms![Date Form].StartDate)+1.99)/7)+1) AS Week
FROM 1_Webtime_By_Date
GROUP BY [1_Webtime_By_Date].Badge,Int(((([1_Webtime_By_Date].Date-Forms![Date Form].StartDate)+1.99)/7)+1);
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Vivek Adi
  • 1
  • 3
  • It worked fine for 1000 records – Vivek Adi Jan 06 '14 at 09:34
  • Try SELECT DISTINCT [1_Webtime_By_Date].Badge,Int(((([1_Webtime_By_Date].Date-Forms![Date Form].StartDate)+1.99)/7)+1) AS Week FROM 1_Webtime_By_Date – PeterRing Jan 06 '14 at 09:52
  • Nope doesn't work shows the same error. I tried simplifying the expression to just ([1_Webtime_By_Date].Date-Forms![Date Form].StartDate) just in case it's related to the math memory capability of access. Still shows the same error. – Vivek Adi Jan 06 '14 at 09:56
  • Well than please give us some data example with the exact types what you are using. – PeterRing Jan 06 '14 at 09:58
  • [1_Webtime_By_Date].Date is of type Date e.g. 2013-12-31 and the date used in the form is of type General Date and is entered as 02-12-2013 – Vivek Adi Jan 06 '14 at 10:04

2 Answers2

1

This is a known issue with the compiler used by Access. Limits of 64K segments were lifted after Access 97 but the amount of data you are quering is simply too much for Access. There are a few tips given on the following page that may help but it seems to me that you need to use a proper database system such as MS SQL. There is a free version available (SQL Express) if it's cost that is the problem.

ACC: "Out of Memory" or "Query Too Complex" with Query/Report

SQL Server Express Download page

You may find that using SQL Server as a database and Access as a front end may help your problems if you are tied to using Access for end users.

The best tip given is to use aliasing to shorten the length of your query and to try to remove nested queries:

Access SQL: FROM clause

twoleggedhorse
  • 4,938
  • 4
  • 23
  • 38
0

My first inclination is to add some white space before and after both the minus signs. My next test would to make the form field into a DATE type, and use something like

     DATEPART(ww,[1_Webtime_By_Date].Date - Forms![Date Form].StartDate ) AS WK
donPablo
  • 1,937
  • 1
  • 13
  • 18