Not enough space

[bignate]

Hi,



I tried to run a query I made and the error message 'There isn't enough memory to perform this operation.Close unneeded programs and try the operation again' came up. This is the third time I have re created the query in this same database because of the error message. However I can run the query normally then all of a sudden when I try running the query one day the error message pops up and it then comes up any time i try to run the query after that. Even if I try to open the query in design view the error message pops up. It is a query by form, so the search criteria is put into a form and then shows in the query. Does anyone know how I can fix this or stop it from coming up again?

Thanks

[offie]

Is your database more than/really close to 2gb? Or is it in a drive that does not have any space left?

[RuralGuy]

Have you tried a Compact and Repair yet?

[bignate]

Thanks for the quick replies. The file is 2mb and I havent tried compact and repair yet because I just used the database at home and the query works but while I am at work the error message comes up. Should I still try the compact and repair or is it probably because the drive at work isnt big enough?

[RuralGuy]

Try the C&R please.

[bignate]

I just tried to run the query from home and the error emssage came up again. I tried Compact and Repair and the error stills comes up.

Thanks

[RuralGuy]

It is possible to create a query that will not fit in anyone's memory. Try starting again.

[bignate]

Start the whole databse or the query again? Because this is the third timeIi have made the query and the error message came up each time.

[RuralGuy]

Start by posting the SQL view of your query. Maybe we can see something that will help.

[bignate]

I cant go into design view of the query to show you, but i can tell you what I entered into the 'Critera' for the query. 'Forms![QBF_Form]![WhatEmployee] Or Forms![QBF_Form]![WhatEmployee] Is Null' is what i put into the criteria for each field. I would then run the query and then when i look at the design view again that query would repeat itself in loads of the empty fields below 'Criteria:' however the query would be without the 'Or Forms![QBF_Form]![WhatEmployee] Is Null' part.

[RuralGuy]

You should be able to go into SQL view.

[offie]

Why did you put in the OR Forms!... Is Null? Do you want them all to show up when the the criteria from the form is blank? That would be done with: Like "*" & Forms!... & "*"

Edit: Example on how you are using the OR statement
So If I have 3 fields and in each I can have
A or B
1 or 2
Green or Blue
Hot or Cold
And you type all of this into the same row in the design view then the possibilities tht gives are
A 1 Green Hot
A 1 Green Cold
A 1 Blue Hot
A 1 Blue Cold
A 2 Green Hot
A 2 Green Cold
A 2 Blue Hot
A 2 Blue Cold
So ovbiously if you have lots of fields, calculating all of this would be tricky.

Also, Or Forms!... Is Null, really makes no sense. If you leave it blank then it will be null. This might be another problem because you're confusing the computer on the Forms!... is. Again consider the using Like, I think it will help you get what you want.

[bignate]

If the form is blank then I want nothing to show up, im guessing I entered the wrong critera. Basically If someone enters information into only one of the fields in the form such as entering a value into 'Full Name' but leaving 'Age' blank, then i want everyone with the name that was entered to show up regardless of their age. Offie are you suggesting I should remove the whoel 'Or Forms!....IsNull' part.

Rural Guy, I dont know how to view the SQL without the error message coming up. The only ways I know how to get to the SQL view is by opening the query then changing to SQL view or by going to design view then SQL view but the databse stops responding and then the error emssage pops up.
Thanks

[RuralGuy]

Rural Guy, I dont know how to view the SQL without the error message coming up. The only ways I know how to get to the SQL view is by opening the query then changing to SQL view or by going to design view then SQL view but the databse stops responding and then the error emssage pops up.
Thanks

It looks like it may be corrupt . Try Creating a new db and *importing* just this query and see if the problem follows. Just take a minute or so.

[bignate]

I imported only the query and I can open it in design view. If I try to open it in SQL view then it stops responding and the error message comes up. I cannot open it in the normal view because the table that is linked to the query isn't there. Below you can see screenshots of the design view and you will see what I mean when I said the criteria repeats itself into loads of the fields. In the second screenshot the bit I highlighted shows that the 'Or...IsNull' part has been removed and the last screenshot shows 'Is Null' in loads of the fields. However I entered the whole thing as 'Forms![QRE_S]![WhatEmployee] Or Forms![QRE_S]![WhatEmployee] Is Null' into the critera for each field of the table once . Could this by why it is corrupting and am I entering the wrong parameters for what I want the query to do and it is confusing the computer? In the form for the query if someone only enters values into 'Full name' and 'Occupation' but not in 'Age' and someone entered 'F' into Full Name and 'Programmer' into Occupation then I would want all people that name begins with F and is a Programmer to return.