# Access: Query Is Too Complex Error



## Daisey (Mar 30, 2001)

To Dreamboat: 

I created a data table which lists student info such as name,date,ss#,addrss, etc. I put the data of dates in ascending order, selected the information, went to filter selection and error. It said "Query is too complex." What is the problem with my table. 
Daisey


----------



## Anne Troy (Feb 14, 1999)

Open the table in design view, Daisey. I'm still thinking it's the format of the Date column.

Are you familiar with design view? The first column should say your field name. The second will be your data type. Your data type for the DATE column needs to say Date/Time. It cannot be Text.

Change it or let me know that it already says Date. Also, feel free to email your database to me, Daisey, at [email protected]

I'll be here for quite a while, so maybe we can take care of it quicker that way.


----------



## THoey (Feb 12, 2001)

But I have seen that error before. Can you go into SQL View of the query and copy the code here so we can see it? It might help trigger what caused that to appear...

Thanks...


----------



## Anne Troy (Feb 14, 1999)

Sorry, th.

She's actually using a filter to do this. I suspect a fairly new Access user, though I haven't come right out and asked her. Daisey? Are you new to Access? Do you understand what th3856 has said?


----------



## Daisey (Mar 30, 2001)

Yes, I am a new user. I took a class which I learned the basic of Access. Getting on. I looked at the design view and my date is in text and I tried to change it. I know it is going to delete some text but is there anyway so that I don't delete the information. Because it is very important information. I am going to check on the SQL view.
Daisey


----------



## Anne Troy (Feb 14, 1999)

You can copy your table to a new name first and then fix the date to see what you lose.


----------



## Daisey (Mar 30, 2001)

for th3856:
I checked the SQL View and it said SELECT[OrientationDate],[FirstName],[MiddleName],[LastName],[SocialSecurity#]FromOrientation;


----------



## Anne Troy (Feb 14, 1999)

Daisey, in your initial question, you said:

*"selected the information"*

Are you highlighting data? th? Should she be highlighting data? I don't use filters--I just use queries.

Why aren't you just creating a query in the first place, Daisey?

Go to the queries tab and hit NEW using design view. Select your table. Click and drag the * (asterisk) down into the first column. Drag the DATE field down into the second column and take the "show" checkmark out. But in the criteria of the DATE column, put

=between[Enter begin date.] and [Enter end date.]

Close and save the query.

When you double-click the query to run it, enter 1/1/2001 and hit enter, then 3/31/2001 and hit enter.

[Edited by Dreamboat on 03-30-2001 at 03:58 PM]


----------



## THoey (Feb 12, 2001)

I missed the point about it being a filter. I too use queries and code more than I use filters. But I have seen that error when I was modifying a query that did get too complex...

Daisey, follow what Dreamboat said and then you will have a working query that can be re-used, and will prompt you for the date range of information that you want...


----------



## Anne Troy (Feb 14, 1999)

Yes, I've seen it before too.


----------



## downwitchyobadself (Oct 13, 2000)

Hey Daisey,

if you need to hold onto that data, DON'T convert that table field. Create a second field in the table, call it OrientationDate2 or some such, and use the Date/Time type for it. Open the table in datasheet view, and you will see that the field is empty; all is right in the world.

If you've been careful about entering the dates in the text version of the field, you shouldn't have too much of a problem converting. Careful, meaning you've used a consistent format, such as mm/dd/yy, to enter your data.

What you want to do next is run an update query. Create a new query, select your table, choose update for query type, add the new date field. In the "update to" row, put in

CDate([OrientationDate])

Then run the query. You may get some update errors (Access will then ask you if you want to run the query anyway, etc.), but this way you can automatically update as much as possible, then go back through the table and fill in manually anything that doesn't work. Once it's all good, you can delete the text field from the table, and change the name of OrientationDate2 to OrientationDate.

By the way: What version of Access are you running, and do you have the service releases/packs on it? As the others have pointed out, the query engine is more powerful than the filter engine, and that may well be why you're getting too-complex errors. Even a text sort should be simple enough for Access, assuming you don't have 9 billion records, but the results will be unsatisfying, because text is sorted as just that, with no intelligence regarding months and days and things. E.g. you get 1, then 10, then 2. Not too useful.

Hope all this is of some help.


----------

