I have a database (http://www.access-programmers.co.uk/forums/showthread.php?t=201725#) query that is very simple with 7 columns from a linked table. The columns are for example

Customer/Invoice ref/invoice date/description/net value/vat/gross value

the problem i have is that tehre must be approximately 500,000+ rows in the query.

I want to show only those items from 2010, however when I put in a criteria such as DATE = 2010 I get the message "ODBC--Call failed [microsoft][ODBC SQL Server (http://www.access-programmers.co.uk/forums/showthread.php?t=201725#) Driver] timeout expired (#0).

Im guessing this is due to the size of the query and the sorting necessary.

Does anyone have any idea as to how I could resolve this?

Thanks in advance...

Create another column in your query

YearDate:Year([Date])


and filter on that column

Create another column in your query

YearDate:Year([Date])


and filter on that column

I take it you mean
YearDate:Year([Invoice Date])

else you would be telling the poster not to use Date as a field name, I should add that spaces are best avoided too, use _ instead

Brian

Its been a long day, you were right Brian I should have specified Invoice Date.;)

i have tried this already and it fell down. I also tried a second table with one column YEAR and one row 2010. tried to link this and again it broke down....

Can you post the SQL and explain "broke down"

Brian

Question Is Sql Server involved here, if so I probably can't help and you may get a better response by posting in that forum.

Brian

Question Is Sql Server involved here, if so I probably can't help and you may get a better response by posting in that forum.

Brian


Hi Brian yes it is. How very annoying. is there any way to tranfer the post to that forum?

is there any way to tranfer the post to that forum?

Yes, I can move it.

One quick question - you didn't provide the SQL for your query as Brian had asked. Can you do so?

Also, do your SQL Tables all have primary keys defined? Are you using any VIEWS here in the query?

I didn't give him time to post the SQL before the penny dropped that it was SQL Server of which I have no experience but have noticed that certain things such as Date - Getdate are different. However I'm sure it will be useful to people who can help.

Brian

I didn't give him time to post the SQL before the penny dropped that it was SQL Server of which I have no experience but have noticed that certain things such as Date - Getdate are different. However I'm sure it will be useful to people who can help.

Brian
I have SQL Server experience but hopefully having the SQL will help.

Also, mp0u8015 - Where are you building this query? Is it in Access or is it a VIEW in SQL Server? WHERE you are doing this is a key piece of information as well as WHAT the SQL is as they are intertwined.

I have SQL Server experience but hopefully having the SQL will help.

Also, mp0u8015 - Where are you building this query? Is it in Access or is it a VIEW in SQL Server? WHERE you are doing this is a key piece of information as well as WHAT the SQL is as they are intertwined.

SQL is

SELECT dbo_ORD_DETAIL.OD_ACCOUNT, dbo_ORD_DETAIL.OD_ORDER_NUMBER, dbo_ORD_DETAIL.OD_DATE, dbo_ORD_DETAIL.OD_STOCK_CODE, dbo_ORD_DETAIL.OD_DETAIL, dbo_ORD_DETAIL.OD_NETT
FROM dbo_ORD_DETAIL;


it is a simple query by choosing the columns from the query in design mode in access

So does dbo_ORD_DETAIL have a primary key with index set?

So does dbo_ORD_DETAIL have a primary key with index set?

No, how do i set these after bringing in the table?

No, how do i set these after bringing in the table?
What do you mean after you bring them in? You are LINKING to the SQL Server tables aren't you? And that means you need to have them set in the actual table in SQL SERVER, not AFTER you connect to them in Access.

Sorry, as you can see im quite new to access and am mixing up with importing the table where it asks to define a primary key.

The answer therefore is I dont know if a primary key with an index set. What should I do from here?

Sorry, as you can see im quite new to access and am mixing up with importing the table where it asks to define a primary key.

The answer therefore is I dont know if a primary key with an index set. What should I do from here?

Who's responsible for maintaining the SQL Server database? Ask them if there is a primary key and indexing on the table(s). If not, then ask them if they can add those as you are having issues because of the lack thereof.

Sorry, as you can see im quite new to access and am mixing up with importing the table where it asks to define a primary key.
If it asks you to define which field is a primary key then it likely doesn't have one set.

Ok as I said earlier i no nothing about SQL Server and i only came back out of curiosity, but the title of the thread is about criteria, so why is there no Where clause.

Sorry to be so ignorant.

Brian