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

 

[cariengon]

Dixxy -

The function listed is a two-part funcion. The OR is distinguishing either the first part OR the second part. What it is doing is if there is a value in the combo or text box, look for that value OR ignore it if the combo or text box is null or empty.

The NZ is part of the second part of the function: Nz([Forms]![FrmCustom]![Hzip], "&quot = "" Where Nz is returning a empty field if the value is null. (See Nz in the help file for more information on how this works...)

This shouldn't matter whether it's for combo or text boxes. I've used it for both.

As for your second question. Once you've added these to your Criteria Lines, you must revert to using the SQL view, and you have to make sure that you ALWAYS re-open the query in SQL view. I lost my query several times too, then I finally copied the SQL view code into a text file - just in case I accidentally tried to view it in design mode. Another reason it may be disappearing is due to having the "Name Autocorrect" function turned on. See the Microsoft KnowledgeBase article at

http://support.microsoft.com/default.aspx?scid=kb;en-us;247488

for more information on this.

I understand your frustration. It took me many hours to get it working correctly. Most of it was due to syntax errors, where I'd miss a quote or end parentheses or something...

Good luck!

 

[dixxy]

Hello,

I have a form with two comboboxes which I want to use to filter a report.

Form: frmReportSelection
combo 1: cboMonth
combo 2: cboSalesPerson

I have tried the code Carie has shared with us

[Forms]![FrmCustom]![Hzip] Or Nz([Forms]![FrmCustom]![Hzip], "") = ""

Everything works fine except in one case, If I select only SalesPerson on my form it returns ALL the records regardless of the SalesPerson. All other case works fine, if I select only month, it return all salesperson with the selected month. If I leave both feilds empty it return all, and if I select a month and a salesperson it return that correct too.

The only problem is if I seclect ONLY saleperson with no month, it returns everything.

I have included my SQL statement for you to better understand.

SELECT tblJobs.SalesPerson, Format([Date],"mmmm") AS [Month], tblJobs.Date, tblJobs.JobNumber, tblJobs.FirstName, tblJobs.LastName, tblJobs.CustomerType, tblJobs.Total
FROM tblJobs
WHERE (((tblJobs.SalesPerson)=[Forms]![frmReportSelection]![cboSalesMan]) AND ((Format([Date],"mmmm"))=[Forms]![frmReportSelection]![cboMonth])) OR (((Format([Date],"mmmm"))=[Forms]![frmReportSelection]![cboMonth]) AND ((Nz([Forms]![frmReportSelection]![cboSalesMan],""))="")) OR (((Nz([Forms]![frmReportSelection]![cboMonth],""))=""));

I would really appriciate some help on this,

Thanks in advance,

Sylvain

 

[nabiS2K]

Here try this sql statement:

SELECT tblJobs.SalesPerson, Format([Date],"mmmm&quot AS [Month], tblJobs.Date, tblJobs.JobNumber, tblJobs.FirstName, tblJobs.LastName, tblJobs.CustomerType, tblJobs.Total
FROM tblJobs
WHERE (((tblJobs.SalesPerson)=[Forms]![frmReportSelection]![cboSalesMan]) AND ((Format([Date],"mmmm&quot)=[Forms]![frmReportSelection]![cboMonth])) OR (((Format([Date],"mmmm&quot)=[Forms]![frmReportSelection]![cboMonth]) AND ((Nz([Forms]![frmReportSelection]![cboSalesMan],"&quot)="&quot) OR (((Nz([Forms]![frmReportSelection]![cboMonth],"&quot)="&quot) AND (tblJobs.SalesPerson)=[Forms]![frmReportSelection]![cboSalesMan]);
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)

 

[nabiS2K]

Actually, try this... I forgot a character

SELECT tblJobs.SalesPerson, Format([Date],"mmmm&quot AS [Month], tblJobs.Date, tblJobs.JobNumber, tblJobs.FirstName, tblJobs.LastName, tblJobs.CustomerType, tblJobs.Total
FROM tblJobs
WHERE (((tblJobs.SalesPerson)=[Forms]![frmReportSelection]![cboSalesMan]) AND ((Format([Date],"mmmm&quot)=[Forms]![frmReportSelection]![cboMonth])) OR (((Format([Date],"mmmm&quot)=[Forms]![frmReportSelection]![cboMonth]) AND ((Nz([Forms]![frmReportSelection]![cboSalesMan],"&quot)="&quot) OR (((Nz([Forms]![frmReportSelection]![cboMonth],"&quot)="&quot) AND ((tblJobs.SalesPerson)=[Forms]![frmReportSelection]![cboSalesMan]); 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)

 

[dixxy]

Mike,

YOU ARE THE MAN!!!!!! Here goes another star for you ..

It works fine, the only thing different this time is if nothing is selected on the form the report comes back empty...should this be the case??

 

[dixxy]

Mike,

I forgot one other thing, prior to this I had an & "*" in the formulas to bring back all the month if I wanted to, can I slip this in somewhere??

Sorry to be a bother,

I really appriciate the help..

Sylvain

 

[nabiS2K]

If you want to display everything...

SELECT tblJobs.SalesPerson, Format([Date],"mmmm&quot AS [Month], tblJobs.Date, tblJobs.JobNumber, tblJobs.FirstName, tblJobs.LastName, tblJobs.CustomerType, tblJobs.Total
FROM tblJobs
WHERE (((tblJobs.SalesPerson)=[Forms]![frmReportSelection]![cboSalesMan]) AND ((Format([Date],"mmmm&quot)=[Forms]![frmReportSelection]![cboMonth])) OR (((Format([Date],"mmmm&quot)=[Forms]![frmReportSelection]![cboMonth]) AND ((Nz([Forms]![frmReportSelection]![cboSalesMan],"&quot)="&quot) OR (((Nz([Forms]![frmReportSelection]![cboMonth],"&quot)="&quot) AND (((tblJobs.SalesPerson)=[Forms]![frmReportSelection]![cboSalesMan]) OR (((Nz([Forms]![frmReportSelection]![cboMonth],"&quot)="&quot AND ((Nz([Forms]![frmReportSelection]![cboSalesMan],"&quot)="&quot);

I think this is it. It is hard reading/writing this SQL in a tiny window. 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)

 

[dixxy]

Mike,

You give me no choice but to give you ANOTHER STAR!!!!!

YOU ARE THE MAN!!!

The only thing is the last statment is missing a ) at the end...but that is no problem...

Thanks you VERY MUCH...

The only thing that I could see possible from here is adding another field to the form...
Would this be possible?

Sylvain

 

[nabiS2K]

You can add as many as Access will allow you too without crashing. You will just have to be aware that adding another field will add another dimension to your current query. This could get complicated with the current design.

If you want to add more fields, I would recommend the IIF approach that I had explained in the beginning of this thread. The main reason I suggested the IIF is because it is less confusing if you use 3 or more fields as criteria for a query than the And/Or thing. It's possible that I forgot a paranthesis or some other silly thing originally and that's why everyone else had so much trouble with it.

The key is to understand the priniciple: The IIF statement will use the criteria of the text box/combo box if the control is not blank, otherwise it will use the fields own value as the criteria (Example: If the field had the value "Blah Blah" the IIF statement should return "Blah Blah" if text box is blank) . I hope this is clear.

I have had the IIF statement work with much success. It still remains my preferred method. It is also easier to utilize with the LIKE statement (at least in my mind anyway).

Who would have thought that this thread would still be going 9 months later?
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)

 

[dixxy]

Mike,

I am experimenting with the IIf statment, because you know how it is, if I give the app this way they will aske me if I can add another field to the form to build the report.

The IIF seems to work except for one thing. My month field is an expression field, and if I put Month in the [My Field] I get prompt to enter a 'Parameter value' for the month when I run the query...

Do you know why this would be?

Sylvain

 

[nabiS2K]

In the case of a calculated field, you should not put the name of the field (because it isn't a field in the underlying recordset), instead use the exact same expression you use to calculate the field in your query.

In this case, you should put Format([Date],"mmmm&quot in the [MyField] part of the IIF statement. 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)

 

[dixxy]

Thanks you Mike,

You have been a really BIG help! I got My form set up with 5 different fields for selecting criteria for the reports...Really neet!!!

Thanks,

Sylvain

 

[fun4me]

Hi everybody

After reading all of this, I am totally confused. I have similar problems and am pulling my hair out. I have inherited a huge problem database and in the midst of rewriting, butttttt at the same time fix up the old one.
Problem: Most of the queries that run their reports (over 100 reports) prompt the user via the query criteria. The users have been trained to enter * when prompted. We know that this really is just for a value in the field not for ALL (including blanks and nulls)

I cannot get this statement to work in this example. If the field data which has a criteria statement is null then the records are omitted. I have tried some of the above statements but they still omit the records.

I need to know what my criteria statment should look like in the query and also in another situation.
The other situation: i have a form with the filters and the query refers to the values in the textboxes.

ugh...

This is the whole query :
Form name is: frmNewReportsMenu
field name: RECRUITMTHD
text box on form: txtRecruitMthd

SELECT DISTINCTROW NAMES.RCRD, NAMES.RECRUITMTHD, NAMES.GROUP, NAMES.ORGCODE, NAMES.TITLE, NAMES.SERIES, NAMES.GRADE
FROM (NAMES INNER JOIN ORG ON NAMES.ORGCODE = ORG.ORGCODE) INNER JOIN HEADER ON NAMES.GROUP = HEADER.ACRONYNM
WHERE (((NAMES.RECRUITMTHD)=IIf(nz([Forms]![frmNewReportsMenu]![txtRecruitMthd].[value],"&quot="",[Names]![RecruitMthd],[Forms]![frmNewReportsMenu]![txtRecruitMthd].[value])));

I would greatlly apreciate it.

 

[DSburgh]

This post has some great info in it, but I am still having trouble getting my combo box to display all the values. I have a "Show All" value in the combo box but it shows nothing when selected. It works fine for all other selections. Here is my code in the mfg criteria of the query:

IIf([Forms]![frm_export]![cbo_mfg_filter].[Value]="Show All",[Forms]![frm_export]![cbo_mfg_filter].[Value])

 

[bernie10]

How about this:

Like IIf([Forms]![frm_export]![cbo_mfg_filter]="All","*",[Forms]![frm_export]![cbo_mfg_filter])

HTH,

Collen

 

[bernie10]

Sorry, meant to say:

Like IIf([Forms]![frm_export]![cbo_mfg_filter]="Show All","*",[Forms]![frm_export]![cbo_mfg_filter])

Collen

 

[DSburgh]

It is still doing the same thing. I also tried using NOT "" instead of the * with the same result. The query is being displayed in a list box if that matters, but even running the query with the form open doesn't change the results.

 

[bernie10]

And this?

Like IIf(Trim([Forms]![frm_export]![cbo_mfg_filter])="Show All","*",[Forms]![frm_export]![cbo_mfg_filter])

If that doens't work, could you post a few sample values from the field from your table.

Collen

 

[DSburgh]

bernie10, I got it working now. Thanks.