cannot use criteria on query (1 Viewer)

mp0u8015 · Nov 19, 2010

I have a database 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 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...

DCrake · Nov 19, 2010

Create another column in your query

YearDate:Year([Date])

and filter on that column

Brianwarnock · Nov 19, 2010

DCrake said:
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

DCrake · Nov 19, 2010

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

mp0u8015 · Nov 19, 2010

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....

Brianwarnock · Nov 19, 2010

Can you post the SQL and explain "broke down"

Brian

Brianwarnock · Nov 19, 2010

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

mp0u8015 · Nov 19, 2010

Brianwarnock said:
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?

boblarson · Nov 19, 2010

mp0u8015 said:
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?

Brianwarnock · Nov 19, 2010

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

boblarson · Nov 19, 2010

Brianwarnock said:
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.

mp0u8015 · Nov 19, 2010

boblarson said:
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 db

RD_DETAIL.OD_ACCOUNT, db

RD_DETAIL.OD_ORDER_NUMBER, db

RD_DETAIL.OD_DATE, db

RD_DETAIL.OD_STOCK_CODE, db

RD_DETAIL.OD_DETAIL, db

RD_DETAIL.OD_NETT
FROM db

RD_DETAIL;

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

boblarson · Nov 19, 2010

So does db

RD_DETAIL have a primary key with index set?

mp0u8015 · Nov 19, 2010

boblarson said:
So does dbRD_DETAIL have a primary key with index set?

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

boblarson · Nov 19, 2010

mp0u8015 said:
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.

mp0u8015 · Nov 19, 2010

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?

boblarson · Nov 19, 2010

mp0u8015 said:
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.

boblarson · Nov 19, 2010

mp0u8015 said:
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.

Brianwarnock · Nov 19, 2010

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

cannot use criteria on query (1 Viewer)

New member

Remembered

Retired

Remembered

New member

Retired

Retired

New member

Smeghead

Retired

Smeghead

New member

Smeghead

New member

Smeghead

New member

Smeghead

Smeghead

Retired

Similar threads

Users who are viewing this thread