INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to fix indicator flag for datatype mismatch

How to fix indicator flag for datatype mismatch

(OP)
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:

CODE -->

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

CODE -->

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:

CODE -->

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:

b04_Curriculum	
Accounting and Financial Statements

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

CountOccurrences came from: http://www.cardaconsultants.com

RE: How to fix indicator flag for datatype mismatch

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

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)
    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]]

RE: How to fix indicator flag for datatype mismatch

(OP)
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.

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)
    FieldSum = FieldSum + Nz(SumFields(I), 0)
    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 

RE: How to fix indicator flag for datatype mismatch

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

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 

RE: How to fix indicator flag for datatype mismatch

(OP)
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:
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
 
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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close