Multiple queries returning all fields

[Sirkevin]

Following on from an earlier post, I thought I would ask a bit of further advice as I need a quick answer to this problem.

The query in my database seems to be returning all of the fields rather than just selecting those which match the criteria selected from dropdowns in a form. I have included the format for accepting nulls (as suggested by Jon K posted 03-05-2006

Basic Criteria Format
The example basically sets the criteria for each field in a separate column in the query grid like this in query Design view:-
-------------------------------------
Field: [FieldName]=[Forms]![FormName]![ControlName] or [Forms]![FormName]![ControlName] Is Null

Show: uncheck

Criteria: True
-------------------------------------
The [Forms]![FormName]![ControlName] Is Null enables us to leave the text box/combo box on the form blank. The True in the criteria row tells Access to treat the expression in the field row as the criteria.

The problem is, it does not seem to work in my database.

I have attached a copy of the bits I need help with (qrymain1 and searchform2 are the query and Form to use).

Hope you can help as I need to sort this ASAP.

Thanks

K

 

[marleymanner1]

The query in my database seems to be returning all of the fields rather than just selecting those which match the criteria selected from dropdowns in a form.

Sirk,

Jon K's post about this (as I have read it, and know from experience), refers to RECORD querying. All of the fields will always be returned in a query, unless you specify in the query grid itself which ones you don't want.

Jon K's post simple refers to specific fields within the records, and weather or not to return the records if the specified field is NULL. That's all. I think you may be confused on what he meant by his post and what you really need...


On a side note, the following is a guess as to what you might need. Here is your original SQL:

SELECT [Task Screen Data].[Task Reference], [Task Screen Data].Client, 
  [Task Screen Data].[Administrator Dealing], [Task Screen Data].[Date Received], 
[Task Screen Data].[Task Type], [Task Screen Data].[Member Task], 
  [Task Screen Data].[Administrator Completed], [Task Screen Data].Completed
FROM [Task Screen Data]
  WHERE ((([Forms]![SearchForm2]![tskref1] Or [Forms]![SearchForm2]![tskref1] Is Null)=True) AND 
(([Forms]![SearchForm2]![cname1] Or [Forms]![SearchForm2]![cname1] Is Null)=True) AND 
  (([Forms]![SearchForm2]![admin1] Or [Forms]![SearchForm2]![admin1] Is Null)=True) AND 
(([Forms]![SearchForm2]![daterecd1] Or [Forms]![SearchForm2]![daterecd1] Is Null)=True) AND 
  (([Forms]![SearchForm2]![tskdesc1] Or [Forms]![SearchForm2]![tskdesc1] Is Null)=True) AND 
(([Forms]![SearchForm2]![memtsk1] Or [Forms]![SearchForm2]![memtsk1] Is Null)=True) AND 
  (([Forms]![SearchForm2]![admin2] Or [Forms]![SearchForm2]![admin2] Is Null)=True) AND 
(([Forms]![SearchForm2]![complete1] Or [Forms]![SearchForm2]![complete1] Is Null)=True));

Here is the SQL I would probably use:

SELECT [Task Screen Data].[Task Reference], [Task Screen Data].Client, 
  [Task Screen Data].[Administrator Dealing], [Task Screen Data].[Date Received], 
[Task Screen Data].[Task Type], [Task Screen Data].[Member Task], 
  [Task Screen Data].[Administrator Completed], [Task Screen Data].Completed
FROM [Task Screen Data]
  WHERE (([Task Screen Data].[Task Reference] = [Forms]![SearchForm2]![tskref1] Or 
  [Forms]![SearchForm2]![tskref1] Is Null) AND 
([Task Screen Data].Client = [Forms]![SearchForm2]![cname1] Or 
[Forms]![SearchForm2]![cname1] Is Null) AND 
  ([Task Screen Data].[Administrator Dealing] = [Forms]![SearchForm2]![admin1] 
  Or [Forms]![SearchForm2]![admin1] Is Null) AND 
([Task Screen Data].[Date Received] = [Forms]![SearchForm2]![daterecd1] Or 
[Forms]![SearchForm2]![daterecd1] Is Null) AND 
  ([Task Screen Data].[Task Type] = [Forms]![SearchForm2]![tskdesc1] Or 
  [Forms]![SearchForm2]![tskdesc1] Is Null) AND 
([Task Screen Data].[Member Task] = [Forms]![SearchForm2]![memtsk1] Or 
[Forms]![SearchForm2]![memtsk1] Is Null) AND 
  ([Task Screen Data].[Administrator Completed] = [Forms]![SearchForm2]![admin2] Or 
  [Forms]![SearchForm2]![admin2] Is Null) AND 
([Task Screen Data].Completed = [Forms]![SearchForm2]![complete1] Or 
[Forms]![SearchForm2]![complete1] Is Null));

 

[Sirkevin]

Thanks for your help MM1, however the SQL you have posted seemed to confuse Access. It stated that the Query was too complex.

Just going back to Jon's post, it was exactly what I wanted. He posted a database with the explanation, which did exactly what I wanted (attached to this post).

I want the records to be filtered in the query depending upon the dropdowns chosen in the form. If some of the fields are left blank they are ignored and only completed fields are used in the queries paramenter criteria. I would like it to be that only exact matches are chosen and not if say 1 of 3 fields chosen match.

I cannot see how his and my query criteria differ at the moment (in technique). I am sure I am missing something blindingly obvious here.

Here is his database for reference:

 

[marleymanner1]

Here is your DB with my SQL. It works fine for me. I even copied and pasted it into the SQL view of the query from my last post.


As another side note Sirk, you may want to rethink how you ask questions. You might not be getting what you want out of this, and it might be because the question is not being asked right. I noticed that you have more than one thread going on this issue, and multiple people are giving you answers, so just don't get confused. My viewpoint, although I'm right about this issue in particular, will differ from everyone else's I'm sure.

 

[Sirkevin]

I absolutely agree with you, I was getting quite confused due to the varied methods and answers people were kindly providing me. Like you say, there are so many different ways to do this by the looks of things.

I also agree that the database you have uploaded works spot on. I do not know if it something I am doing wrong, however as soon as I copy and paste the code into another database (simple copy and paste into SQL view) it informs me that the query is too complex - not quite sure why this is. It doesn't quite make sense to me. Your uploaded database is fine!!

If you have any ideas I would love to hear them.

Thank you very much for your help so far, it is much appreciated.

K

 

[Brianwarnock]

Hi again , you haven't followed Jon's work correctly, you have not set your fields = to the criteria
eg
Expr1: [Administrator dealing]=[Forms]![SearchForm2]![admin1] Or [Forms]![SearchForm2]![admin1] Is Null

Expr1 can be some name of your choosing.

Brian

 

[marleymanner1]

If you have any ideas I would love to hear them.

take the extra spaces out of the SQL first of all. Next, check the control names and see if they match. If that doesn't work, simply export the query object from my DB into yours if all ur doing is copying the SQL anyway!

 

[Sirkevin]

Thanks both Brian and MM.

I have managed to get both methods to work now.

Your help has been much appreciated.

Cheers

K