Dinclause

[dbahooker]

today, I had to rewrite a function that I've used a couple places

It basically is designed to be similiar to dlookup; but designed to
return an IN clause. This is for a bunch of reasons-- value lists-- a
whole ton of reasons

I was hopign to get your guys' feedback because I think that this is a
great function and I wish that it was included in the next version of
Access or something.

---------------------------------------------



Public Function DinClause(strField As String, strTable As String,
strWhere As String) As String
On Error GoTo errhandler

Dim rst As New ADODB.Recordset
Dim strSql As String
Dim strOut As String
Dim I As Integer

If IsEmpty(strWhere) = True Then
strSql = "Select 'DinClause' as Why, [" & strField & "] FROM [" &
strTable & "] ORDER BY 1"
Else
strSql = "Select 'DinClause' as Why, [" & strField & "] FROM [" &
strTable & "] WHERE " & strWhere & " ORDER BY 1"
End If

With rst
..CursorLocation = adUseClient
..LockType = adLockReadOnly
..Open strSql, CurrentProject.Connection

If .EOF = True Then
DinClause = "*"
GoTo cleanexit
Else
Do Until I = rst.RecordCount
If I <> 0 Then strOut = ", " & strOut
strOut = .Fields(1).Value & strOut
rst.MoveNext
I = I + 1
Loop
End If

End With

DinClause = strOut


cleanexit:
Exit Function
errhandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Function

 

[salah morad]

today, I had to rewrite a function that I've used a couple places

It basically is designed to be similiar to dlookup; but designed to
return an IN clause. This is for a bunch of reasons-- value lists-- a
whole ton of reasons

I was hopign to get your guys' feedback because I think that this is a
great function and I wish that it was included in the next version of
Access or something.

---------------------------------------------



Public Function DinClause(strField As String, strTable As String,
strWhere As String) As String
On Error GoTo errhandler

Dim rst As New ADODB.Recordset
Dim strSql As String
Dim strOut As String
Dim I As Integer

If IsEmpty(strWhere) = True Then
strSql = "Select 'DinClause' as Why, [" & strField & "] FROM [" &
strTable & "] ORDER BY 1"
Else
strSql = "Select 'DinClause' as Why, [" & strField & "] FROM [" &
strTable & "] WHERE " & strWhere & " ORDER BY 1"
End If

With rst
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open strSql, CurrentProject.Connection

If .EOF = True Then
DinClause = "*"
GoTo cleanexit
Else
Do Until I = rst.RecordCount
If I <> 0 Then strOut = ", " & strOut
strOut = .Fields(1).Value & strOut
rst.MoveNext
I = I + 1
Loop
End If

End With

DinClause = strOut


cleanexit:
Exit Function
errhandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Function

 

[dbahooker]

Public Function DinClause(strField As String, strTable As String,
Optional strWhere As String = "*", Optional strOrderBy As String = "1",
Optional bracketize As Boolean = False) As String
On Error GoTo errhandler

Dim rst As New ADODB.Recordset
Dim strSql As String
Dim strOut As String
Dim I As Integer

If IsEmpty(strWhere) = True Then
strSql = "Select [" & strField & "] FROM [" & strTable & "] "
Else
strSql = "Select [" & strField & "] FROM [" & strTable & "] WHERE "
& strWhere & " "
End If

If IsEmpty(strOrderBy) = True Then
strSql = strSql & " ORDER BY 1"
Else
strSql = strSql & " ORDER BY " & strOrderBy
End If

'MsgBox strSql, vbOKOnly

With rst
..CursorLocation = adUseClient
..LockType = adLockReadOnly
..Open strSql, CurrentProject.Connection

If .EOF = True Then
DinClause = "*"
GoTo cleanexit
Else
Select Case bracketize
Case False
Do Until I = rst.RecordCount
If I <> 0 Then strOut = ", " & strOut
strOut = .Fields(0).Value & strOut
rst.MoveNext
I = I + 1
Loop
Case True
strOut = "]"
Do Until I = rst.RecordCount
If I <> 0 Then strOut = "], [" & strOut
strOut = .Fields(0).Value & strOut
rst.MoveNext
I = I + 1
Loop
strOut = "[" & strOut
End Select
End If

End With

DinClause = strOut


cleanexit:
Exit Function
errhandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
Resume
End Function