Queries and Expressions Part Deux

[waholtmn]

I have a text field with values of either "2" or "M" and I'll call this [Field1]. Another field has numeric values including some with null values and I'll call this [Field2]. There are also three other text fields with some having empty strings and I'll call these [Field 3], [Field 4], and [Field 5]. I want to write an expression that checks on [Field1] to see if it is a "2" or an "M" and then once that is determined, then do one of two things.

If [Field1] = "2" then this expression...
IIf([Field3]<>"" Or [Field4]<>"","Answer A","Answer B")

So if [Field1] = "2" then if either [Field3] or [Field4] have a value, then write "Answer A". If neither one has a value, then "Answer B"



If [Field1] = "M" then this expression...
IIf([Field2]>0,IIf(IsNull([Field3])=False Or IsNull([Field5])=False,"Answer A","Answer B"),"Other")

So if [Field1]="M" then check if [Field2] is greater than zero;if [Field2] is greater than zero then if [Field3] or [Field5] has text then write "Answer A";if [Field2] is greater than zero and both [Field3] and [Field5]are empty strings, then write "Answer B";if [Field2], [Field3] and [Field5] is are either empty strings or null then write "Other".

I need my solution to be an expression that I can place in the Expression Builder...I promise, I'll pick up a VBA book at B&N!

 

[plog]

The easiest way to do this is to create a custom function inside a module. In your query you would put this for the field you want to create:

CustomFieldName: getCustomFieldName([Field1], [Field2], [Field3], [Field4], [Field5])

Then you would create a new module and create a new function like this:

Function getCustomFieldName(f1, f2, f3, f4, f5)
ret=""
 
If (f1="2") Then If IsNull(f3 & f4) Then ret = "B" else ret="A"
 
If (f1="M") Then
 
' Write the rest of your code here, there is no need to cram it all on one line
 
 
getCustomFieldName= "Answer " & ret
 
End Function

Save this Module and run your query.

 

[waholtmn]

PLOG -- Thanks so much...You are definitely helping this noob learn more and more. I come to forum only after exhausting efforts to find the solution myself. Your solution looks inviting and I'm going to look at it more closely but I do have a few questions.

1. f1, f2, f3....Those are generic labels which would need to be replaced with the actual field names, correct?

2. CustomFieldName: getCustomFieldName([Field1], [Field2], [Field3], [Field4], [Field5])...this would go on the Field line in Design View, correct? Can I use something like Source instead of CustomFieldName?

3. ' Write the rest of your code here, there is no need to cram it all on one line...Are you willing to help me with this code?

4. I'm going to look around on how to create a new module/function within a query and hopefully between what I find out of the web, etc and your intelligent insight, the database I am working on can finally be finished as this is the last piece of the puzzle that I, for the life of me, cannot figure out.

Best regards!


UPDATE: I now know how to create custom user defined functions and have a crudimentary one built. Now all I need is the code to solve for when [Field1] is "M".

 

[spikepl]

I love "crudimentary"

 

[plog]

1. Actually no they do not need to be changed. That function lives by itself, it doesn't need to know the name of the fields--all it needs is the data passed to it. Whatever value you put first goes into the variable called f1, the second value into f2, and so on. The function then references those values with the names of the variables it has assigned (f1, f2, f3, etc.). So while you can change them, you don't need to. If you do change them, you must change them throughout the function.

2. Yes, you can name it anything you want. The data before the colon represents what will appear in your query, feel free to name this anything. The function part -getCustomFieldName - can also be renamed, but it must match exactly the name of the function in your module.

3-4. Sure, but from the code you have written so far, I'm pretty sure you can do it. Give it shot--write the code in your function that you think will work--save it, then execute the query. Its a SELECT query so its not going to screw anything up, at worst its going to produce the wrong result. If it does, go back to your function and try and correct it and repeat the process. If you do get stuck, post back here and we are happy to help.

 

[waholtmn]

Plog -- You are awesome! I'll give it a shot.

 

[waholtmn]

Here is the public function I wrote:

Public Function getCustomFieldName(f1, f2, f3, f4)
ret = ""

If (f1 = "2") Then If IsNull(f3 & f4) Then ret = "Answer B" Else ret = "Answer A"

If (f1 = "M") Then If (f2 > 0) And IsNull(f3 & f4) Then ret = "Answer A" Else ret = "Answer B"
If (f1 = "M") Then If IsNull(f2) Then ret = "Other"
getCustomFieldName = "" & ret

End Function

It appears to be working just fine. Plog, please let me know if you see something that may be incorrect.

 

[plog]

Looks good. Just to be sure I would create a table of test data that when run through your fucntion should produce every result that the function should return. Then what you do is create a query based on that table, bring in all the fields of the test table and create your calculated field to make sure what it actually returns is what is supposed to be returning.