IsEmpty(TipoKort) #Error

[matrixindicator]

I convert a short description of a street to a long with a function eg. CL -> CALLE
However if there is not a short description he writes #Error, how just to place a emmpty string "" if there is no short description for the street ?

What do I need to adapt to my code ?

Public Function TIPODESC(TipoKort As String) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SELECT STREETDESC FROM TblStreet WHERE StreetCode =""" & TipoKort & """", dbOpenDynaset)

If IsEmpty(TipoKort) Then
    TIPODESC = ""
Else
    TIPODESC = rst!STREETDESC
End If

End Function

 

[jrbarnett]

Replace the lot with

DLookup("StreetDesc", "tblStreet", "StreetCode='" & TipoKort & "'")

And that will work

John

 

[JerryKlmns]

Public Function TIPODESC(TipoKort As String) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SELECT STREETDESC FROM TblStreet WHERE StreetCode ='" & TipoKort & "'", dbOpenDynaset)

If rst.EOF AND rst.BOF Then 
   TIPODESC = ""
Else
   TIPODESC = rst.Fields(0)
End If
rst.close

End Function

DLookup("StreetDesc", "tblStreet", "StreetCode='" & TipoKort & "'") & ""

 

[TheAceMan1]

How are ya matrixindicator . . .

Try this:

[blue]Public Function TIPODESC(TipoKort As String) As String

   Dim dbs As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set dbs = CurrentDb
   SQL = "SELECT STREETDESC " & _
         "FROM TblStreet " & _
         "WHERE StreetCode ='" & TipoKort & "';"
   Set rst = dbs.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      If Trim(TipoKort & "") <> "" Then TIPODESC = rst!STREETDESC
   End If
   
   Set rst = Nothing
   Set dbs = Nothing

End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886