Solved: I think SQL?

[Eric58132]

Hi everyone,

new question, this one is likely very easy...I think it's called a "CASE STATEMENT" that I need? I just couldn't find an example of what it was I was truly looking for to work off from.

I attatched an excel file to this post; it mimics the portion of my the query that I hope to build my next query from. WHat I'm trying to do is build the "true success" column (in yellow) so I can make easy pivot tables from my data. The grey columns are all pre-populated fields.

How do I build my yellow column?

thanks for your help in advance.

[Eric58132]

OK, I decided to build a module (don't know how that escaped me before) but I think something might be wrong with my variable declarations: When I run my query while pulling in my "Action Log" Function, I get a #Error response for every record in the column but there isn't any sort of opportunity to debug. Can anyone spot the problem?


Function ActionLog(ByVal Form_Name As String, ByVal Success As String, ByVal Phone_Call_1_Success As String, ByVal Phone_Call_2_Success As String, ByVal Phone_Call_3_Success As String) As String
If [Form_Name] = "Back Office Entry Form" Then
ActionLog = [Success]
Else
If [Form_Name] = "Back Office Entry Form2" Then
ActionLog = [Success]
Else
If [Form_Name] = "frm_PHONECALL1QUE" Then
ActionLog = [Phone_Call_1_Success]
Else
If [Form_Name] = "frm_PHONECALL_2_3QUE" And [Phone_Call_3_Success] Is Not Null Then
ActionLog = [Phone_Call_3_Success]
Else
If [Form_Name] = "frm_PHONECALL_2_3QUE" And [Phone_Call_3_Success] Is Null Then
ActionLog = [Phone_Call_2_Success]
Else
If [Form_Name] = "New Special Termination Form" Then
ActionLog = "Yes"
Else
If [Form_Name] = "New Special Termination Form NH" Then
ActionLog = "Yes"
End If
End Function



All of the fields that my Function is using are simple Yes/No text strings

[CreganTur]

I haven't read all your code, but I can tell you that one big problem is that you're not using ElseIf correctly.

You should be using:
ElseIf

Not:
Else
If

ElseIf is a check that is part of the same conditional statement as the original If. Using Else If creates a new conditional If block inside the original conditional.

Make this correction, then test your code.

HTH:thumb

[Eric58132]

thanks Cregan, but no luck yet

[CreganTur]

thanks Cregan, but no luck yet

Post your corrected code, along with a description of what the code's supposed to do and the error you're seeing.

[Eric58132]

I did some book reading and changed my variables to read as variant instead of string. This got some of the code working. Now when I run my query I get an error with the frm_PHONECALL_2_3QUE lines. I suspect that my formatting of "IS NULL THEN" and "IS NOT NULL THEN" is incorrect.

Function ActionLog(ByVal Form_Name As Variant, ByVal Success As Variant, ByVal Phone_Call_1_Success As Variant, ByVal Phone_Call_2_Success As Variant, ByVal Phone_Call_3_Success As Variant) As Variant

If [Form_Name] = "Back Office Entry Form" Then
ActionLog = [Success]
ElseIf [Form_Name] = "Back Office Entry Form2" Then
ActionLog = [Success]
ElseIf [Form_Name] = "frm_PHONECALL1QUE" Then
ActionLog = [Phone_Call_1_Success]
ElseIf [Form_Name] = "frm_PHONECALL_2_3QUE" And [Phone_Call_3_Success] Is Null Then
ActionLog = [Phone_Call_2_Success]
ElseIf [Form_Name] = "frm_PHONECALL_2_3QUE" And [Phone_Call_3_Success] Is Not Null Then
ActionLog = [Phone_Call_3_Success]
ElseIf [Form_Name] = "New Special Termination Form" Then
ActionLog = "Yes"
ElseIf [Form_Name] = "New Special Termination Form NH" Then
ActionLog = "Yes"
End If
End Function

Take a look at the .xls attachment in my initial posting and you can get a good idea of what my data looks like. I'm trying to conditionally pull data from the queryset and line them all up in one column so that I can make pivot tables.


***EDIT*** I now see that this thread is widening beyond the screen. Sorry for the inconvenience, is there an easy way to fix this?

**Another EDIT** The Error I'm getting is 'Run-Time error '424'': Object required

[Eric58132]

I think I got it! I used a combonation of ISNULL() and Len()>0 to get both criteria to work. Took me 6 hours to figure out, but I'd say it was a good learning experience as this is the first module I've ever written by hand :)

[hansup]

***EDIT*** I now see that this thread is widening beyond the screen. Sorry for the inconvenience, is there an easy way to fix this? Split long lines into shorter lines, and use the VBA line continuation character ("_") to indicate those short lines are all part of one "logical" line. Here is an example using your function's declaration:Function ActionLog(ByVal Form_Name As String, _
ByVal Success As String, _
ByVal Phone_Call_1_Success As String, _
ByVal Phone_Call_2_Success As String, _
ByVal Phone_Call_3_Success As String) As String

[geekgirlau]

Sample using Select Case:


Function ActionLog(ByVal Form_Name As Variant, ByVal Success As Variant, _
ByVal Phone_Call_1_Success As Variant, _
ByVal Phone_Call_2_Success As Variant, _
ByVal Phone_Call_3_Success As Variant) As Variant

Select Case [Form_Name]
Case "Back Office Entry Form", "Back Office Entry Form2"
ActionLog = [Success]

Case "frm_PHONECALL1QUE"
ActionLog = [Phone_Call_1_Success]

Case "frm_PHONECALL_2_3QUE"
If [Phone_Call_3_Success] Is Null Then
ActionLog = [Phone_Call_2_Success]
Else
ActionLog = [Phone_Call_3_Success]
End If

Case "New Special Termination Form", "New Special Termination Form NH"
ActionLog = "Yes"
End Select
End Function

[hansup]

It looks a lot better with Select Case. However, I get an "object required" error message on this line:

If [Phone_Call_3_Success] Is Null Then

The IsNull function avoids that error:

If IsNull([Phone_Call_3_Success]) Then