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

 

[slocat]

Mike,

I am also trying to create a similar form where the user can choose which criteria to base the report on. Your code:

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

has been very useful for most of my form fields, text or otherwise.

The only problem is dealing with fields which may be empty in the database. The records with empty fields are eliminated from all resultsets even if that criteria is not being used.

For example, if I have form fields for LastName, FirstName and City I can use any or all of them as criteria and get a valid report. Using no criteria at all gives me the full recordset. If I add a form field for college it works as long as there is a college used as a criteria. If the college form field is left blank, anyone who didn't list a college is eliminated from the results. Using no criteria gives me the full recordset minus those with no listing for college.

I'd appreciate any suggestions you might have.

Thanks!

 

[nabiS2K]

To add Null values, place Or Null in the appropriate part of the IIF statement. To add a zero-length string, place Or "" in the appropriate location.

If for some reason this doesn't work, copy the first if statement, paste it in the same criteria with an "Or" in between. Then, change the appropriate part of the IIF statement to Null or "".

Let me know if this doesn't make sense.
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)

 

[slocat]

Mike,

I thought it made sense, but I'm not getting it to work. Here's my code:

IIf(nz([Forms]![frmCustom]![HZip].[Value],"&quot="" Or (nz([Forms]![frmCustom]![HZip].[Value],"&quot=Null),[H_Zip],[Forms]![frmCustom]![HZip].[Value])

I'm getting the same results as before. Where am I going wrong?

Many thanks!

 

[Teddo]

Mike,

The above discussion also relates to me, I have just discovered!

I have tried putting the 'Or is null' into the criteria statement, but to no avail. I have also tried the copy and paste option you outlined, and that didn't work either.

Is there anything else I can do, or could you show me where to put the 'Or Is Null' command. The closest i came to getting a result with this was when I put it directly after [MyField]. This resulted in Access telling me the expression was too complicated when I tried to run the query.

Sorry to keep bothering you!

Ted

 

[scriverb]

ofird, I have been following this question for sometime now and the dialog between the two main parties has been useful. But, In answer to your lost question in the middle of all of this I believe is yes, building the WHERE statement dependent upon form entries can be done successfully in code. This is what I do a lot.

I have built a table in the front-end application for these type of dynamic queries.(i.e. tblQuerySQL) When I say dynamic I mean where the WHERE statement gets modified often. I have a Memo field where I store the Select . . . From . . . portion of the SQL Statement. I leave off the WHERE portion and the semi-colon. I just use the AutoNumber(RecCounter) as the identifier for selection.

Example: "Select tblTable1.CustomerID, tblTable1.CustomerName, tblTable1.CustomerAddress, tblTable1.CustomerCityStZip, tblTable1.CustomerType, tblTable1.CustomerTitle FROM tblTable1 "

Usually I just have a blank query built in my front-end database called qryTempQuery that keeps getting modified whenever I need to use it.

When I am ready to build my SQL string I call up the main body of the SQL from the table using a function call with the RecCounter number as a parameter:

FUNCTION IN DATABASE MODULE:
Function QuerySQL(QueryID As Long)
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblQuerySQL", dbOpenDynaset)
MyRS.FindFirst "RecCounter = " & QueryID
If Not MyRS.NoMatch Then
QuerySQL = MyRS("QuerySQL&quot
Else
QuerySQL = "" 'No record found for QueryID parameter passed to function
End If
MyRS.Close
MyDB.Close
End Function

BUILD QUERY STRING WITH FUNCTION CALL:
DIM db as DAO.DataBase
Set db = CurrentDB
db.QueryDefs("qryTempQuery&quot.SQL = QuerySQL(1) & " WHERE "
If Not IsNull(me![ComboBox1]) then
db.QueryDefs("qryTempQuery&quot.SQL = db.QueryDefs("qryTempQuery&quot.SQL & " tblTable1.CustomerType = " & me![ComboBox]
end if
If Not IsNull(me![ComboBox2]) then
db.QueryDefs("qryTempQuery&quot.SQL = db.QueryDefs("qryTempQuery&quot.SQL & "AND tblTable1.CustomerTitle = " & me![ComboBox2]
end if
db.QueryDefs("qryTempQuery&quot.SQL = db.QueryDefs("qryTempQuery&quot.SQL & ";"

'RUN THE QUERY
DoDmd.OpenQuery "qryTempQuery"

The ComboBox's should be set to Null before the start of the selections. If they are picked then the WHERE statment will include them. If they are not they are left out of the WHERE statement.

The downside to this process is that ACCESS Jet will reoptimize a new query that has not been run before so if your database is very very large there couuld be a few minutes on the front-end of the execution while it analyzes the database, indexes, etc. and decides how to run the query the most efficient way.

Just another way to dynamically build your query definitions.

I hope this helps you in you application.

Bob Scriver

 

[scriverb]

nabiS2K: By the way I really like the way you handled the Criteria line IIF statement. I have often struggled with that one trying to have nothing idenfied as criteria if nothing is selected in the combobox or a text field is left blank. I used your first example and it worked like a charm:

TABLE: Customer
FORM: frmCustomers
COMBOBOX Name: Code

IIf(IsNull([Forms]![frmCustomers]!

) or [Forms]![frmCustomers] = "",[Customer]![Code],[Forms]![frmCustomers]![Code])[COLOR=black]

Solved some problems for me.  Have another STAR!!!

 

[nabiS2K]

Thank scriverb... I am happy to 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]

nabiS2K:

Thanks for all your help above. That database is now done and dusted, and the code you gave me I have found very useful for a new database I am designing.

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

needs modifying slightly for a field I have involving currencies. I need the query to find all values up to and including that entered in [MyForm]![MyBox]. Presumable I just need to insert the <= operator; the question is, where do i put it?

Or am I barking up the wrong tree entirely?

I would certainly appreciate your help on this!

Many thanks

Ted

 

[nabiS2K]

I am have never worked with a Currency data type. I prefer Double fields personally; however, I am assuming this change would work.

<=IIF(Val(nz(Forms![MyForm]!MyBox.Value,0)=0)),[MyField],Val(Forms![MyForm]!MyBox.Value))

The above code converts all null values to a 0. If you are not wanting nulls converted to zero, modify or remove the nz statement. God Bless,
Mike

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

 

[Teddo]

nabiS2K:

Thanks for the help again.

The code immediately above did not work; Access came back and said it was too complicated.

However, simply putting the <= operator in front of the code you had given me previously seems to work a treat.

Thanks again; another star!!!

 

[slocat]

I'm still having the same problem. I have tried many versions of the following:

IIf(nz([Forms]![frmCustom]![HZip].[Value],&quot;&quot=&quot;&quot; Or nz([Forms]![frmCustom]![HZip].[Value],Is Null) Is Null,[H_Zip],[Forms]![frmCustom]![HZip].[Value])

and they all have the same result: any record with the H_Zip field empty in the database is eliminated from all resultsets, even if that field is not a criteria for the search.

Do you have any other ideas to get around this? It would be a great help! Thanks

 

[tlbroadbent]

Simplify the expression!

And (table.h_zip = [Forms]![frmCustom]![HZip]
Or Nz([Forms]![frmCustom]![HZip], &quot;&quot = &quot;&quot

If the form control contains a value the query will search for it. If the control is empty or NULL the any value in the table column will be selected. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[slocat]

Perfect! The whole form works like a charm. Thanks Terry and Mike for all your help!

Sally

 

[drrep]

How would the IFF statement would apply to a Memo field?

 

[PLSM]

Hi All.

I realise it's been a long time since there was any activity in here, which I assume means that solutions work fine. I have read through all of it, and tried many solutions. Nothing works as yet. I actually contacted Teddo who referred me to you guys.

I have a very similar problem to Teddo. I have 7 tables, one unbound form and 7 subforms. One of the subforms act as the master in a master/child relationship to the other subforms. All subforms have different tables as record sources. On the main unbound form I have 7 combo boxes that should act as filters to what is viewed in the subforms.

I have some code that change the recordsource of the 'master' subform to a query. That query contain the whole of the 'master' subform table, and the few fields from other tables that correspond with the filter combo's. If I could work out how to make the query ignore any filter combo's boxes that are empty, then the master/child relationship should filter what is shown.

Phew... that was quite a bit. I would greatly appreciate any help you can provide.

Cheers,
PLSM

 

[PLSM]

I have tried the following with some success:

IIf(Trim(nz([Forms]![frmNewMain]![cboFilter1].[Value],&quot;&quot)=&quot;&quot;,[tblBackupJob]![Archive],[Forms]![frmNewMain]![cboFilter1].[Value])

However, it does produce 2 errors:

1. When run, it brings up an input box which says &quot;[Forms]![frmNewMain]![cboFilter1].[Value]&quot;

2. When a correct filter is input in the above input box, the query returns way too many results of the same thing. That is, not just &quot;result1&quot;, &quot;result2&quot;, etc... But &quot;result1,&quot;result1&quot;,&quot;result1&quot;,&quot;result1&quot;... then &quot;result2&quot;,&quot;result2&quot;....

Can anyone help? Thanks in advance. Brgds,
PSLM

 

[nabiS2K]

PSLM,

Your first problem comes from the object &quot;cboFilter1&quot; on the form &quot;frmNewMain&quot; does not exist in your database. You would need to change &quot;[Forms]![frmNewMain]![cboFilter1].[Value]&quot; to some object or value in your database.

Regarding the second problem, I am not sure why your would see duplicate rows. However, if you paste the SQL statement, I will review it for you. It is probably just a matter of applying the priniciples here to the structure of your data/database.

God Bless,
Mike

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

 

[PLSM]

Mike,

Thanks for your help.

If I change it to an object in the database, the filter combo's will have to connect to a table or query right?

I don't really want that as it is only for filtering... or am I completely misunderstanding you?

Brgds,
PLSM Brgds,
PSLM

 

[cariengon]

I just wanted to add a note about how this SOLUTION WORKED for me. It looks like many people have been here and asked questions, but have never posted the solution when it worked!

I was having the problem of passing several textboxes from a form to a the criteria in a query. If the textbox was null or empty in any of the fields, it wouldn't properly pull the data for the textboxes that were populated. I have 3 textboxes and 1 combobox, only 1 of the textboxes need to be populated by the user.

The posting by tlbroadbent on July 13, 2002 was my answer - but after pulling my hair out trying to figure the exact context to use in my critera line in my query... (Terry posted in the SQL context and not what should be entered in the Criteria line in the query).

So, for those of you needed this solution - for passing multiple textbox values to a Query Criteria line - try the following:

[Forms]![FrmCustom]![Hzip] Or Nz([Forms]![FrmCustom]![Hzip], &quot;&quot = &quot;&quot;

([Forms]![frmCustom][HZip] is the textbox or combobox on the form)

What this does is return ANY value in the column, just as if there was nothing put in the Criteria, if the value in the textbox that is being refered to is Null or Empty (&quot;&quot.

I actually expanded mine and included the Like &quot;*&quot;, because I didn't want the user to have to know the exact number they were looking for... This is my syntax exactly as I put it in the criteria:

(Like &quot;*&quot; & [Forms]![7000_ManualSettle_Select_frm]![txtContractID] & &quot;*&quot Or nz([Forms]![7000_ManualSettle_Select_frm]![txtContractID],&quot;&quot=&quot;&quot

I'm amazed at how simple this solution was - and actually worked. I tried every other combination out there and kept getting no results.

THANKS Terry!!!

Warmest Regards -
Carie

 

[dixxy]

Carie,

I have come about your thread, and I have been pulling out my hair ever since..I have been looking for this solution for a VERY LONG time, it sounds like it work perfectly, but not for me...

First do you need to put the &quot;Nz&quot; after the &quot;Or&quot;.

Second when I put that code in my query criteria, save it and reopen the query in design view the code disapear..Why?

Does it mater if I have 2 comboboxes instead of textboxes?