Hello everyone;
  I could use some help on an access search engine hit counter.  I have
the problem of getting an #Error value because of null inputs, and I
was wondering if there was a way, in code, to set the results to "" if
the initial target string is null.   It would be great if you had any
advice.

  Here is my code:

Public Function CountPhrase(ByVal Search As String, ByVal Target As
String, Optional ByVal Compare As VbCompareMethod = vbTextCompare) As
Long

    Dim strSearch As String
    Dim strTarget As String
    Dim lngCount As Long
    Dim lngPos As Long


    strSearch = Search
    strTarget = Target
    lngPos = InStr(1, strSearch, strTarget, Compare)
    Do While lngPos <> 0


        lngCount = lngCount + 1
        strSearch = Mid$(strSearch, lngPos + 1)
        lngPos = InStr(1, strSearch, strTarget, Compare)


    Loop

       CountPhrase = lngCount

End Function

If you're really dealing with Null values, there is the IsNull() function
which you could use to test for null or you could just ignore the fact that
it's null using code like:
    strSearch = "" & Search
    strTarget = "" & Target

Concatenating the blank string turns a null in to a 0 length string.

HTH
Steve


<kme***@indiana.edu> wrote in message
Show quoteHide quote

kme***@indiana.edu wrote in news:1129713736.919189.138180
@g14g2000cwa.googlegroups.com:

Show quoteHide quotejust a note.....

in your code, you don't need to Dim strSearch and strTarget, you can use
the variables passed to the function. Also, the Mid$ call in not required
either.

    Dim lngCount As Long
    Dim lngPos As Long

    lngPos = InStr(1, Search, Target, Compare)
    Do While lngPos <> 0
         lngCount = lngCount + 1
         lngPos = InStr(lngPos + 1, Search, Target, Compare)
    Loop

    CountPhrase = lngCount

granted, that doesn't save a lot in exe size or processing, but if you
have a lot of function calls or subs, a couple lines or dim's in half of
them could make a difference in keeping the code shorter and un-bloated.

regards,

DanS

<kme***@indiana.edu> wrote in message
Show quoteHide quoteWhile Steve's and DanS's advice is more appropriate for your situation, you
can also 'catch' Nulls using the Immediate If function. This is useful if
you need to substitute another value.
   sValue =  IIf( IsNull(!FieldValue), "Null", !FieldValue)
- the same as
   If IsNull(!FieldValue) Then
          sValue = "Null"
   Else
          sVaue = !FieldValue
   End If

Below is a wrapper function called vVal (is my pascal showing?), that
I've been using for years.  It gracefully handles nulls that might be
returned from recordsets...enjoy...


Public Sub LoadFields(ByRef rs As DAO.Recordset)

  txtUserIDAutoNumber = vVal(rs!UserIDAutoNumber)
  txtUserID = vVal(rs!userid)
  txtUserFirstName = vVal(rs!UserFirstName)
  txtUserLastName = vVal(rs!UserLastName)
  If rs!userActive Then
     cbxUserActive.Value = vbChecked
  Else
     cbxUserActive.Value = vbUnchecked
  End If

  Call SetComboToItemData(cboUserGroupID, rs!usergroupID)
  Call SetComboToItemData(cboUserRegionCD, rs!userregionCd)

  If bUsingEncryptedPasswords Then
     txtUserPassword = decodeString(vVal(rs!userPassword))
  Else
     txtUserPassword = vVal(rs!userPassword)
  End If

  updateGrayout

End Sub


Public Function vVal(ByVal v As Variant, Optional vDateFormat As
Variant, Optional bStripDollarAndComma As Variant) As String
Dim nType As Integer
nType = VarType(v)

If IsMissing(bStripDollarAndComma) Then bStripDollarAndComma = False

Select Case nType
   ' following line changed from format(v) since it is unnecessary and
because it caused a bug
   ' when converting a string like 2.0:  it became just '2' after going
thru "format"
   Case vbString: vVal = Trim(v)
   Case vbInteger: vVal = Format(v)
   Case vbDate:
        If IsMissing(vDateFormat) Then
           vVal = Format(v, "mm/dd/yyyy")
        Else
           vVal = Format(v, vDateFormat)  ' "short date", or "Long
Date", or a mask like "m/d/yy"
        End If
   Case vbLong: vVal = Format(v)
   Case vbNull: vVal = ""

   Case vbSingle: vVal = Format(v)
   Case vbEmpty: vVal = ""
   Case vbDouble: vVal = Format(v)
   Case vbCurrency:
        If bStripDollarAndComma Then
           vVal = Format(v, "fixed")
        Else
           vVal = Format(v, "currency")
        End If

   Case vbDecimal: vVal = Format(v) ' <<< new data type for Cobol
files, added 10/30/01

   'Case vbObject:
   'Case vbError:
   Case vbBoolean: vVal = v
   'Case vbVariant:
   'Case vbDataObject:
   'Case vbByte:
   'Case vbArray:
   Case Else
     Err.Raise vbObjectError + 0, "UTILS.BAS(vVal): ", _
     "vVal function cannot handle this Variant data-type number: " &
Str(VarType(v)) & ". [E200102251620]", _
     "", 0
End Select
End Function

Subclass Help WM_NOTIFY
Division by Zero Error - Help Urgent
Merge array in VB
Problem executing a DTS from a VB6 application after installing th
Controlling location of form within form
"_files" folders and related folders
How to monopolize Windows in vb6
Page designer in VB
task bar?
detect tab keypress on checkbox - how?