How to fix indicator flag for datatype mismatch 1

[sxschech]

Trying to build an indicator to filter rows and am running into datatype mismatch. I have tried nz, isnull, val, and so far it persists. Hopefully someone will see what I'm not.

This works fine without a where clause:

ExtraCredit: IIf(IIf(Nz([GT],0)>1,1,0)+IIf(Nz([AA],0)>1,1,0)+IIf(Nz([ET],0)>1,1,0)+IIf(Nz([YB],0)>1,1,0)+IIf(Nz([RR],0)>1,1,0)+IIf(Nz([FR],0)>1,1,0)>2 Or countoccurrences(Replace(Replace([Curriculum],[b04_Curriculum],""),";;",";"),";")>2,"Manual Edit Netforum","")

If I add where clause IS NOT NULL, I get the error.

Created a new field and when I take out the "OR" condition, it works with Where > 0

ExtraCreditIndicator: IIf(IIf(Nz([GT],0)>1,1,0)+IIf(Nz([AA],0)>1,1,0)+IIf(Nz([ET],0)>1,1,0)+IIf(Nz([YB],0)>1,1,0)+IIf(Nz([RR],0)>1,1,0)+IIf(Nz([FR],0)>1,1,0)>2,1,0)

I have narrowed down to this as the problem area:

 countoccurrences(Replace(Replace([Curriculum],[b04_Curriculum],""),";;",";"),";")","")

That piece is doing double duty in that it is comparing two fields and if the value of b04_Curriculum is contained within Curriculum, we don't need it and in turn remove the semicolon if there is an extra one, then we need to count how many semicolons are left as we can only use two curriculums for the automated process, due to current design, any additional ones we need to input to the other application have to be done manually. As a visual of the data for the curriculum expression:

[pre]
b04_Curriculum
Accounting and Financial Statements

curriculum
Accounting and Financial Statements;Financial Leadership Forum;Fraud;Business and Industry;Finance, Financial Management and Controllership
[/pre]
Italicized would need to be manually added

CountOccurrences came from:

http://www.cardaconsultants.com

 

[MajP]

That is kind of a mess. A whole lot simpler to write your own UDF

Public Function GetFlag(B04 As Variant, Curriculum As Variant, ParamArray SumFields() As Variant)
  Dim I As Integer
  Dim FieldSum
  For I = 0 To UBound(SumFields)
    FieldSum = FieldSum + Nz(SumFields(I), 0)
  Next I
  If FieldSum > 2 Or GetOccurences(B04, Curriculum) > 2 Then GetFlag = "Manual Edit NetForm"
End Function

Public Function GetOccurences(Field1 As Variant, Field2 As Variant) As Long
  If Not IsNull(Field1) And Not IsNull(Field2) Then
    GetOccurences = UBound(Split(Field2, ";")) + 1
    If Len(Field2) <> Len(Replace(Field2, Field1, "")) Then GetOccurences = GetOccurences - 1
  End If
End Function

Then from a query:
ExtraCredit: GetFlag([b04_Curriculum],[Curriculum],[GT],[AA],[ET],[YB],[RR],[FR]]

 

[sxschech]

Hi MajP, after making a minor change, it seems to be working. Modified GetFlag because I am not summing up the cols, instead, trying to count the number of cols that have a value greater than 0 since the external utility can only import 2 of the 6 cols automatically.

Thank you for getting me pretty close, so that I could fix the problem.

Public Function GetFlag(B04 As Variant, Curriculum As Variant, ParamArray SumFields() As Variant)
  Dim I As Integer
  Dim FieldSum
  For I = 0 To UBound(SumFields)
    [s]FieldSum = FieldSum + Nz(SumFields(I), 0)[/s]
    If Nz(SumFields(I), 0) = 0 Then
        FieldSum = FieldSum
    Else
        FieldSum = FieldSum + 1
    End If
  Next I
  If FieldSum > 2 Or GetOccurences(B04, Curriculum) > 2 Then GetFlag = "Manual Edit NetForm"
End Function

 

[MajP]

A little cleaner. "fieldSum = fieldsum" is some silly code.

Public Function GetFlag(B04 As Variant, Curriculum As Variant, ParamArray SumFields() As Variant)
  Dim I As Integer
  Dim FieldSum
  For I = 0 To UBound(SumFields)
   if not isnull(sumfields(i)) then fieldSum = FieldSum + 1 
  Next I
  If FieldSum > 2 Or GetOccurences(B04, Curriculum) > 2 Then GetFlag = "Manual Edit NetForm"
End Function

 

[sxschech]

I did feel silly writing that, however, when I try your revision, it flags every record as needing manual edit because all the cols are not null. My silly code is checking to see if the value = 0 and if so do not increment the fieldsum, only increment the fieldsum if it is > 0.

Example:
[pre]
EventID [GT] [AA] [ET] [YB] [RR] [FR] ExtraCredit
12345A 2 0 0 2 4 0 Manual Edit NetForm
23456A 0 8 0 0 0 0
[/pre]
Row 1 would be flagged because it has 3 cols that have a value > 0
Row 2 only has 1 col with a value > 0 so does not need manual intervention

The reason I have Extra Credit spelled out is because this is displayed on a multiselect listbox on the form and that is easier for users to understand then seeing a 1/0 Yes/No

Modifying your recommendation to this seems to work:

If Nz(SumFields(I), 0) > 0 Then FieldSum = FieldSum + 1

Is main difference then that code is one 1 line and implicitly defining the else rather than explicitly stating it?

Thanks again, I do appreciate you going the extra step in cleaning up my code.