microsoft office 2010 Home And Business key Put II

[panpan094i]

Readers Trey Smith and Brandon Kessler from HealthTrans, LLC suggested the following Power Tip for streamlining Access queries.
I ran into an issue at my job where I had so many IIF statements for a field in a query that the query wouldn’t run. We deal with many clients that all have different criteria for getting billed, but I wanted to create one query that dealt with all of them. After searching online for inspiration of how to get around this problem, I had just about given up when no results came back, until my friend and I came up with the idea of using the find and replace function in VB to evaluate text IIF statements in a table. Now, instead of writing out long IIF statements in a query to return the answer for a field, we point to the table that has the IIF statement and it runs on its own.
Here is an example:
Suppose we have a field in a table called "Client". We have another field in the table called "Employee Name". Suppose one of our clients is Contoso. We may have 10,000 records in the table for the "client" field that all say Contoso, but the "Employee Name" field would be a different employee each time,microsoft office pro 2010 64 bit, thereby giving you a table of all the employees that Contoso has.

Now, if we want to build a quick query to count how many employees Contoso has, we could just pull the "Client" field in and put Contoso in the criteria, do a group by on that field, then bring in the "Employee Name" field and do a count. That would give us a good count of how many employees Contoso has. Easy enough. Now, let’s assume that Contoso is the one entering the employees in this table and occasionally they put in the employee "Test" as a test employee just to make sure everything is working right. Well, now in our query we don’t want to count the employee named "Test", so in the WHERE clause we could put in the criteria – Not "Test" and that would filter out any employees named "Test" from being counted.

So far, all is well in the world of Access and everyone is happy.
Now, let’s assume we have another client, Fabrikam, that uses this same table to track their employees but they happen to have an employee named "Test". Because of that, they decide to set up their test employees with the word “TST”. Well now if we want our query to give us a count of both clients Contoso and Fabrikam, we have to remove the criteria – Not "Test" from the employee count, because if we don’t we will filter out the employee of Fabrikam that is actually named Test. We could add two different criteria, one under the client that says “Contoso” with Not "Test" under the employee count, and another criteria under client that says “Fabrikam” with the Not "TST" criteria next to it under the employee count.

Another way to do it would be to put this expression in the employee count field: Sum(IIf([CLIENT]="CONTOSO",IIf([EMPLOYEE NAME]="TEST",0,1),IIf([CLIENT]="FABRIKAM",IIf([EMPLOYEE NAME]="TST",0,1)))) that would do the same thing.
The problem is,discount microsoft office Enterprise 2007, Access can only hold so many criteria in a field and in a query. Once your IIF statement has gotten too long, or you’ve added too many criteria, the query will break. It takes quite a bit of criteria and IIF statements to make the query go crazy, so this isn’t a typical problem for most people, but when you have 1000’s of clients all with different criteria pulling from the same query, you will likely run into this.
So our solution: take the IIF statements and criteria out of the query and put them into a table that the query will use.
Keeping with our example, we create a new table just for our criteria and in that table you would have the fields "Client" and "Criteria":

I understand this looks different than your typical IIF statement and I will get into that a little later.
Now, going back to your query that counts the employees by client, make sure that you bring in the new “Criteria” table and link the old client table to the criteria table with a join on the fields "Client". The first field again is the field "Client" and is grouped. The employee count field will be different this time. Instead of an IIF statement, it will read like this:
Client Count: SUM(Eva([CRITERIA],"EMPLOYEE NAME",[EMPLOYEE NAME]))

Here is what is happening. We have created a function called Eva in VB and this field is running that code for every line in the table. The first field [CRITERIA] is pulling back the value in the table. In the case of Contoso, the value being pulled back is IIF(|EMPLOYEE NAME| = 'TEST',0,1) and that is coming back as text. The second item being pushed to VB is the string "EMPLOYEE NAME"; we will get into this once we discuss what the VB code is doing. The third value being pushed to VB is the value that is in the [Employee Name] field, which is someone’s name such as BOB.
Now, getting to the VB. The gist of what we are going to do in VB is to take the string that was sent in quotes, in this case "EMPLOYEE NAME", replace the quotes with brackets so that VB knows which field you are looking to use. What happens is, VB sees 3 things when we pass these values. The first is the text phrase IIF(|EMPLOYEE NAME| = ‘TEST’,0,1), the second is the string "EMPLOYEE NAME", and the third is the value for [EMPLOYEE NAME], BOB (being the employee’s name). Two of these are values that are pulling from the table, the other is just a string. Now what you do is have VB do a find and replace on the IIF string, looking for any set of || and replacing it with []. In this case, it will find the word |EMPLOYEE NAME| in the expression, and replace it with the value for [EMPLOYEE NAME]. Now VB has the following expression IIF('Bob' ='TEST',0,1). There is still a problem though. This is not seen as a function or expression, it just looks like text. That’s where the function Eval comes in. Eval is a built-in function that takes a text expression and evaluates it for a value. In this case, it takes the text value IIF('Bob' = 'TEST',0,1) and converts it into an actual expression, returning the value 1 to the query. Once it gets to the employee name TEST, it will send back a 0.
The beauty is that in the query, on the field that is counting the employees you have this: name",[employee name])) of this: NAME]="TEST",0,1),IIf([CLIENT]="FABRIKAM",IIf([EMPLOYEE NAME]="TST",0,1)))) is very handy for me because certain fields that I am evaluating look at several different fields and might look more like this:
SUM(Eva([CRITERIA],"employee name",[employee name],"children",[children],"income",[income]))
So now say that you not only want to filter out employees with the name TEST but also any employees with an income over a million for Contoso, and filter out employees named TST for Fabrikam and employees that have more than 3 children.
The following is what that would normally look like:
SUM(IIF([CLIENT]="CONTOSO", IIF([employee name] <> "TEST" AND [income] < 1000000,1,0),IIF([CLIENT] = "FABRIKAM", IIF([employee name] <>"TST" and [children] < 3,1,0))))
This is getting large already, instead of this:
SUM(Eva([CRITERIA],"employee name",[employee name],"children",[children],"income",[income]))
That is about half the space as the other and the IIF statement resides in a table. It can even be a memo field and still work.
The following is the actual VB code:
Function Eva(ParamArray Evs() As Variant) Dim expression,win 7 starter x64 key, functionvariable, answer, comparestring As String Dim repeat, startposition, endposition, ArrayCnt, I As Integer 'Below is calling the first expression,windows 7 home premium update key, in the case of Contoso,microsoft office 2010 Home And Business key, 'expression would equal "IIF([EMPLOYEE NAME] = 'TEST',0,1)" expression = Evs(0) 'The next code is for clients that have no expression, 'so that the vb code doesn’t error out If IsNull(expression) Or IsEmpty(expression) Then Eva = Evs(1) Else 'This tells you how many characters there are until you get to 'a "|", basically meaning keep doing it until you run out of "|" repeat = InStr(1, expression, "|", 1) Do While repeat >= 1 'The below code is finding the first "|" startposition = InStr(1, expression, "|", 1) 'The below code is finding the 2nd "|" endposition = InStr(startposition + 1, expression, "|", 1) 'The below code is getting the letters that are in between 'the first and 2nd "|" previously found, in the case of Contoso, 'it would bring back the text "Employee Name" functionvariable = Mid(expression, startposition + 1, _ endposition - startposition - 1) 'The below code is saying compare the word you just found with 'the word I sent through, if it matches, give me the value of 'the next field I gave you, which is the person’s name (such as Bob) ArrayCnt = UBound(Evs) answer = 0 For I = 1 To ArrayCnt Step 2 comparestring = Evs(I) If comparestring = functionvariable Then answer = Evs(I + 1) End If Next I 'Replace the word with the value, so it replaces '"Employee name" with the value "Bob" expression = Replace(expression, "|" & functionvariable & _ "|", "'" & answer & "'", 1, 1) repeat = InStr(endposition + 1, expression, "|", 1) Loop 'Evaluate the text you now have, this is what the evaluation would be: '"IIF('Bob'='Test',0,1)", which would return 1 back to the query Eva = Eval(expression) End If End Function
Download the sample database here. I hope this helps others because I haven’t seen anything like it on the Internet and I absolutely needed it to help out with efficiency (not having to make 500 different queries for all of our different clients).

Trey Smith Send your Power Tips to Mike and Chris at accpower@microsoft.com.