Selecting Query criteria from forms 8

[Teddo]

I am designing a database in which we will input information from a questionnaire we have distributed. It has 5 tables [Details]; [Entrepreneurs]; [Influences]; [Starting a business]; [What would you do? + your future]. each of these corresponds to a section on the questionnaire, and each one has a form linked to it, through which the data is inputted. Data is entered via a combination of checkboxes, combo boxes and text boxes, depending on the type of data.

I then have a query [Detailsquery], with a form linked to it [Detailsqueryform] from which the user can select which criteria they want to view a report on, again via a combination of checkboxes, combo boxes and text boxes. So, if the user wanted to view a report on all the males who attended Leicester University, whose parents run their own business and who regard themselves as entrepreneurial, they would select or type in the relevant data, and the query (through the report) should return the relevant fields, ignoring the others.

The problem I am having is that when I select or type in data in [Detailsqueryform], the query will only run if I enter a value in EVERY field. If I leave fields blank (as I would in the above example), the query returns a blank result.

How do i get my query to ignore fields where I haven't entered any value?

Thanks in advance for your help.

Ted

 

[nabiS2K]

Ted,

I have always used the IIF statement in the Criteria of the query. If the item is blank, I just make the IIF value equal to itself.

Example:
WHERE [MyField]=IIF(Forms!MyForm!MyBox.Value = Null Or Forms!MyForm!MyBox.Value = "",[MyField],Forms!MyForm!MyBox.Value)

Let me know if I can help anymore.
God Bless
Mike ;-)

 

[Teddo]

Mike,

Thanks for this, but i can't seem to get it to work. Every time I write this in the criteria row, it sets up a new field called "Where[Myfield]" which has the rest of the criteria you gave in the criteria row.

What am I doing wrong? I'm a relatively new user, and so am not adept at the niceties of Access!!

Many thanks

Ted

 

[nabiS2K]

You need to change the MyField to the Field in the query you want to have the criteria. You need to change MyForm to the name of the form in which the object box is located. You need to change MyBox to the name of the object box.

Does this help? My statment was just an example with generic names, sorry...
God Bless
Mike ;-)

 

[Teddo]

Oh, yes. I should have mentioned I did that; but it did what I described. I was just using the same generic examples as you did.

Sorry!

Ted

 

[nabiS2K]

Oh I see...

Ok just had the IIF(Forms!MyForm!MyBox.Value = Null Or Forms!MyForm!MyBox.Value = "",[MyField],Forms!MyForm!MyBox.Value) to the criteria of the field in the query that you want to filter. It looks like the SQL statement needed a space between the where and the [MyField], but it is easier to just copy/paste into the criteria of a query field.
God Bless
Mike ;-)

 

[Teddo]

Thanks for your help!

It works fine on checkboxes, but for combo boxes and text boxes, it doesn't seem to work. I'm just doing a small query to trial the different criteria, and it works fine for the two checkboxes I have on the query (and form). However as soon as I enter the same criteria (with names changed appropriately) in the combo box and/or text box fields, the query returns a blank result.

I'm getting closer, but still can't crack it!

Thanks for your help thus far.

Ted

 

[nabiS2K]

The only part you are probably missing is to be sure that the combo box, text box, or list box have the exact same values as the underlying table. If you want some flexibility with the criteria using a text or combo box, you can try using the Like command with wildcards.

You know what might be even more effective than the previous IIF statement that I gave you... try changing the statement to:

IIF(nz(Forms![MyForm]!MyBox.Value,"&quot="",[MyField],Forms![MyForm]!MyBox.Value)

The above statement will convert a null value to a zero-length string and evaluate if the object box is zero-length. If you also want to return all the records if the object box value is only spaces, you can trim the spaces, converting it to a zero-length string.

IIF(Trim(nz(Forms![MyForm]!MyBox.Value,"&quot)="",[MyField],Forms![MyForm]!MyBox.Value)
God Bless
Mike ;-)

 

[Teddo]

Mike,

This certainly seems to be closer to what I need; but it doesn't appear to be ignoring the areas where I have not entered anything.

Is there any way I can get it to ignore those fields where I don't select anything?

Cheers

Ted

 

[nabiS2K]

This should work. I have used it many times in fact. Make sure MyField is the exact field that the column references. The concept is to have the criteria for a null value evalute as MyField = MyField, resulting in no restriction. You might want to be sure to specify the exact table ([MyTable].[MyField]), instead of just the field.

If this still doesn't work, copy and paste in a reply the SQL statement that is giving you touble. I'll take a look at it for you.
God Bless
Mike ;-)

 

[Teddo]

Mike,

Thanks for this. I'm jsut on my way home, but will certainly look at it tomorrow.

Cheers for your help.

Ted

 

[ofird]

Hi,

I have read your discussion, very interesting.
I am having a similar problem, but I am trying to find a different kind of solution. Tell me if you think its possible:
Define a query in your project with ONLY the common fields, you would always like to show.
In your code, get the query object and add the criterias you would like to the query before excuting it.
Is it possibe to add an addition to the existing SQL (like "AND where grade>90&quot?

 

[nabiS2K]

ofird,

Ye,s it is very easy to edit a SQL statement in code. The SQL statement of a QueryDef is simply a string expression. Since it is a string, you can use Left, Right, Mid, Instr, Trim, etc. to edit it.

For example:
You mentioned wanting to edit the where clause. You could use the Instr command to locate the WHERE clause in the SQL statment and parse it out of the string and input a new WHERE clause.

However, there is a draw back to what you are asking. Since you would be editting the query and saving the query, you would eliminate multiple users from properly using the query. If you wanted to continue editting the SQL for a multi-user environment, you might consider saving temporary queries in Access. You would need to save the query to use it as a record source for a form or report(as far as I know anyway), but you could delete the query after the user was finished.

Let me know if I can help you further. God Bless
Mike ;-)

 

[ofird]

Hi Mike,

Thank you for your help, can you show me the code of openeing a query which is defined in my project and adding the string to it?
I am defining a queryDef in my code and it doesn't recoginize it as a data type.
Is there a diference in access 97 and 2000,XP?
I think I am confused with the DAO and ADO, what's the real difference betqeen them?
Sorry to be such a bugger, if you can help I will appreciate it ;-).

God Bless yo too lad.

Ofir

 

[Teddo]

Mike,

I'm back in and trying to work my way around this. I've got a small test form (Detailsquery) and query (Details Query)going. The form has fields [Gender] (Combo box), [EthnicOrigin] (Combo box) and [University] (Text box).

I've just discovered that when I put in entries on the form, it updates the main table where the entries are stored. What am I doing wrong with this?

Also, it will sometimes run a query correctly from the data I input in the form, and sometimes it won't. The criteria statement you gave me seems to work, but I'm not sure where else in the design I've gone wrong.

Any help on this would be appreciated!

Many thanks

Ted

 

[Teddo]

OK,

I've messed around with it some more and stopped the adding query entries to table problem.

The criteria you gave me above: IIF(nz(Forms![MyForm]!MyBox.Value,"&quot="",[MyField],Forms![MyForm]!MyBox.Value)

is working fine for the two combo boxes. So, if I select 'Male' and 'White' from the 'Gender' and 'Ethnic Origin' combo boxes it gives only the white males in the database, just as if I select 'Male' or 'White' it gives the males or the whites. In the 'University' textbox, however, I have encounteered a problem. If I enter 'Coventry' (for example) it simply returns all results whether 'Coventry' has been entered for their university or not. however if I select 'Male' from the 'Gender' combo box and type 'Coventry' into the 'University' text box, it returns only the males who went to Coventry.

This seems slightly bizarre to me and I can't seem to find a way round it.

Please help!

 

[nabiS2K]

First, any items in the form you will not want to bind to a field. You are using the boxes to determine a report's output, not update records. If the control is bound to a field (its control source is set to a field), then the record will be updated with whatever the user enters in the control.

Second, you need to be sure every criteria for a field matches the correct control on the form exactly.

Third, you need to be sure each of the controls has updated before attempting to requery. Otherwise, Access will report a null value when executing the query and generate results similiar to your Coventry issue.

At this point, I would recommend doing whatever you can to locate any differences between the University field and the other fields.

Let me know if I can help you more. God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[Teddo]

Mike,

Thanks a lot for all your help on this. The criteria seems to be working fine... you are a star.

The problem I am now having is that when I set the query to run with two of the tables in, it is fine and returns the correct results. As soon as I add a third, it causes me problems. Namely it won't return any results.

I think that the problem stems from the relationship between them in the query. Each table has an autonumber field [Entry no] which seem to link together automatically when I add the tables to the query.

As I said, as soon as I add the third table, the results come back empty.

Can you help?

Many thanks

Ted

 

[nabiS2K]

Sure...

Can you provide more info? A copy of the SQL statement that returns zero records would help.
God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[Teddo]

Mike,

Thanks for the offer on this problem. I have managed to solve it by simply incorporating all the tables into one big one and juggling things around to fit with that.

The problem I'm having now is that the expression you gave me for the criteria cell works fine for combo boxes but not for text boxes. It doesn't seem to matter what value i put in the text box, it still returns all entries, regardless of whether they match or not.

is there any way round this?

Cheers

Ted