ignore criteria

[shutzy]

i have a querie that displays in a report. i have in the criteria to select a specific employee. i would like to be able to have the querie ignore this criteria if 'x' is not selected. ie. be able to view all the results from all emplyees. i know it is possible but dont know how to do it.

any help please.

 

[vbaInet]

Let us see the query and highlight the criteria in concern.

 

[shutzy]

this is the sql from my querie. i want to be able to ignore the date and the employee criteria and if possible just 1 at a time or both at once.

SELECT tblItems.Items, Count(tblOrdersItems.OrderItemsID) AS CountOfTreatmentItemsID, tblCategories.Catergorie, tblItems.Items, Sum(tblOrdersItems.Cost) AS SumOfTreatmentCost
FROM tblCategories INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.[OrderID] = tblOrdersItems.[OrderID]) ON tblItems.[ItemsID] = tblOrdersItems.[OrderItemsID]) ON tblCategories.CategoriesID = tblItems.CategoriesID
WHERE (((tblOrders.OrderDate)>[Forms]![rptReportsMainScreen]![txtboxDateFrom] And (tblOrders.OrderDate)<[Forms]![rptReportsMainScreen]![txtboxDateTo]) AND ((tblOrders.Employee)=[Forms]![rptReportsMainScreen]![Employee]))
GROUP BY tblItems.Items, tblCategories.Catergorie, tblItems.Items;

 

[shutzy]

this is the sql from my querie. i want to be able to ignore the date and the employee criteria and if possible just 1 at a time or both at once.

SELECT tblItems.Items, Count(tblOrdersItems.OrderItemsID) AS CountOfTreatmentItemsID, tblCategories.Catergorie, tblItems.Items, Sum(tblOrdersItems.Cost) AS SumOfTreatmentCost
FROM tblCategories INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.[OrderID] = tblOrdersItems.[OrderID]) ON tblItems.[ItemsID] = tblOrdersItems.[OrderItemsID]) ON tblCategories.CategoriesID = tblItems.CategoriesID
WHERE (((tblOrders.OrderDate)>[Forms]![rptReportsMainScreen]![txtboxDateFrom] And (tblOrders.OrderDate)<[Forms]![rptReportsMainScreen]![txtboxDateTo]) AND ((tblOrders.Employee)=[Forms]![rptReportsMainScreen]![Employee]))
GROUP BY tblItems.Items, tblCategories.Catergorie, tblItems.Items;

 

[vbaInet]

Here's what the WHERE clause should look like:

WHERE ((tblOrders.OrderDate BETWEEN Nz([Forms]![rptReportsMainScreen]![txtboxDateFrom], [COLOR=Red]DMin(...) [/COLOR]
                                And Nz([Forms]![rptReportsMainScreen]![txtboxDateTo], [COLOR=Red]DMax(...)[/COLOR])) AND 
    (tblOrders.Employee = [Forms]![rptReportsMainScreen]![Employee] Or [Forms]![rptReportsMainScreen]![Employee] Is Null))

The DMin() and DMax() functions will get the min and max of the Order Date field. So create two textboxes on the Header section of your form and put the DMin() and DMax() functions in there, then replace DMin() and DMax() in the query above with the full reference to those textboxes.

 

[shutzy]

sorry vba, does this solve the 'all dates' and 'all employees' ie. ignore criteria. i havnt put it in yet but i want to focus on that particular question. the DatesFrom & DatesTo format may be wrong but it works for the time being and i would rather change somethiing that will have a positive effect with regards to my original post. i will try the code you have provided.

 

[vbaInet]

I'm sure you mentioned that in your first post and I would imagine that would have been put into consideration. Look at the Employee criteria and you will see that was taking care of too.

Give it a try first before posting. It's a waste of time if we keep coming back on here to answer questions if the proposed solution hasn't yet been tried out.

 

[shutzy]

yes im sorry. i suppose im too quick to skim read things, comment and then realise my mistake afterwards.

thanks for the correction.

 

[vbaInet]

Let us know how you get on.

 

[shutzy]

hi vba. thanks for the sql. im sorry but i have tried it and it dosnt work. i dont really understand what the code is to know if i have done it right.

i thought of another possibility that might work. if i have a check box and on 'true' it would disregarde the field 'Employees'. is this possible or am i barking up the wrong tree.

 

[jdraw]

Can you show the query sql for the attempt you made, where you said i have tried it and it dosnt work.?
vbaInet gave you the essence of the revised WHERE clause -- What exactly didn't work?

 

[shutzy]

hi jdraw, i do appreciate that vba does know exactly what he is doing and his instructions are very good as he has helped me many times before. the bit i dont understand is 'two textboxes on the Header section of your form and put the DMin() and DMax() functions in there,'. i know that all it takes is an explanation but i also know that there is always more than 1 way to skin a cat. is it possible to put in what i said about 'if i have a check box and on 'true' it would disregarde the field 'Employees' '. i feel that this tool(if there is one) maybe more beneficial to learn of as i have many different queries and i think it is becoming abit of a jumbled mess and if i could use the tool i have in mind it could clear alot of those up for me. it maybe wrong and who am i to say 'na i want something better or different' but that would definately be prefered. i hope you understand.

 

[vbaInet]

The checkbox idea won't work.

Are you struggling with what to put in the DMin and DMax functions?

 

[nanscombe]

Here is a demo database I've put together based on what's described in your queries.

I think this would do what you are after.

I've done something wierd with the dates (formated them as yyyymmdd) I tend to do this to get rid of any ambiguity in the formating of the dates etc.

Have a look and see whether this helps you.

 

[shutzy]

The checkbox idea won't work.

hi vba. i beg to differ. not within the query but on the form. with having the 2 txt boxes i have macro's on the change event of checkbox all dates. i have basically said if check =yes then set value to the 2 txt boxes of #01-01-1900#, #31-12-2099# respectively.

and then if checkbox=no then set value of the 2 txt boxes of datefrom box and dateto box.

it works. theres alot more going on in the background but it does have the effect i want. thanks for all your help at least i am thinking a little differently as a result of this.

now ill see if it works with the employees, wish me luck

 

[nanscombe]

If you take a look at my demo database you'll find that all you need to do for Employee is use the criteria

Like "*" & [Forms]![rptReportsMainScreen]![Employee] & "*"

If Employee is empty it looks for "**" basically a wildcard which will pull back anything.

If Employee has a name it look looks for any string with the name in it ie "*James Cook*". No coding or checkboxes required.


For the dates I've something similar to yourself but used a pair of textboxes, which could be hidden, which will default to a low or high date automatically (I happened to pick 1/1/1970 and 31/12/2099) if no entry is made in either date. Again no coding (or macros) required.

 

[shutzy]

thats definately an alternative and something to bare in mind for the future. i already use the like "*" & criteria for searches via list boxes so i should of thought about that. thanks for the advise. good to know.

 

[vbaInet]

hi vba. i beg to differ. not within the query but on the form. with having the 2 txt boxes i have macro's on the change event of checkbox all dates. i have basically said if check =yes then set value to the 2 txt boxes of #01-01-1900#, #31-12-2099# respectively.

and then if checkbox=no then set value of the 2 txt boxes of datefrom box and dateto box.

Good effort shutzy! But what's the point having an extra checkbox when it can be done without it as I explained in post #5?

WHERE ((tblOrders.OrderDate BETWEEN [COLOR=Blue]Nz([/COLOR][Forms]![rptReportsMainScreen]![txtboxDateFrom], #01-01-1900#[COLOR=Blue])[/COLOR]
                                And [COLOR=Blue]Nz([/COLOR][Forms]![rptReportsMainScreen]![txtboxDateTo], #31-12-2099#[COLOR=Blue])[/COLOR]) AND 
    (tblOrders.Employee = [Forms]![rptReportsMainScreen]![Employee] Or [Forms]![rptReportsMainScreen]![Employee] Is Null))

I've substituted DMin() for #01-01-1900# and DMax() for #31-12-2099#. You could simply use that in place of DMin() and DMax(). Do you not know what the Nz() function does?