My bad, Biggles. I didn't think it through.
OK, so you have a combo box where you pick a field, which populates a list
box with the data from one field. The data can/might change each time you run
the report. One time you might be searching for records for certain dates,
other times it might be for certain text in the field.
So the trick is determining the field type using the field name. This is
done using a function "TableInfo()" passing two arguments: the table name and
the field name.
Below is the code:
' Select Case VarType(List67.ItemData(varnumber))
' Evaluates as a string. The entire code is below. Is there some
other way I
' should format the list box?
Dim stDocName As String
Dim varnumber As Variant
Dim sfilt As String
Dim lngLen As Long
Dim sfld As Variant
Dim stlinkcriteria As String
Dim separ As String
Dim sepctr As Integer
Dim FldType As Long '*****
sfld = Me.GRPSELECT
FldType = Null
stDocName = "rptALLARCHIVED_BASE"
Debug.Print Me.Option69
'If users choose the all records option
If Me.Option69 = -1 Then
DoCmd.OpenReport stDocName, acPreview
Exit Sub
End If
On Error GoTo Err_Command72_Click
'determine the delimiter needed
FldType = TableInfo("ARCHIVED_ISSUES", sfld)
Select Case FldType
Case vbDate 'date fields
separ = "#"
Case vbString 'character fields
separ = "'"
Case vbInteger, vbCurrency, vbDecimal, vbSingle, vbDouble
separ = ""
Case Else
Debug.Print FldType
End Select
'if users select records to filter by in the list box
For Each varnumber In List67.ItemsSelected
sfilt = sfilt & separ & List67.ItemData(varnumber) & separ & ","
'Debug.Print sfilt
Next
'remove trailing comma, add field name, in operator and brackets
' lngLen = Len(sfilt) - 1
' If lngLen > 0 Then
If Len(sfilt) > 0 Then
sfilt = "[" & sfld & "] in (" & Left(sfilt, lngLen) & ")"
Debug.Print sfilt
End If
DoCmd.OpenReport stDocName, acPreview, , sfilt
Exit_Command72_Click:
Exit Sub
Err_Command72_Click:
Debug.Print Err.Number; Err.Description
Resume Exit_Command72_Click
' I think this is where you would have an
'END SUB
'========================================
'the following code (modified) was shamelessly stolen from
'Allen Browne at
http://allenbrowne.com/func-06.html
Function TableInfo(strTableName As String, strFieldName As String) As Long
On Error GoTo TableInfoErr
' Purpose: Determine the field type in a table
' Arguments: Name of a table in the current database, Name of a field in
the table
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
For Each fld In tdf.Fields
If fld.Name = strFieldName Then
TableInfo = CLng(fld.Type) 'fld.Type is Integer, but constants are
Long.
End If
Next
TableInfoExit:
Set db = Nothing
Exit Function
TableInfoErr:
Select Case Err
Case 3265& 'Table name invalid
MsgBox strTableName & " table doesn't exist"
Case Else
Debug.Print "TableInfo() Error " & Err & ": " & Error
End Select
Resume TableInfoExit
End Function
'===================================
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
Biggles said:
Steve
That is what I was looking for, but every time I run the code:
Select Case VarType(List67.ItemData(varnumber))
Evaluates as a string. The entire code is below. Is there some other way I
should format the list box?
Dim stDocName As String
Dim varnumber As Variant
Dim sfilt As String
Dim lngLen As Long
Dim sfld As Variant
Dim stlinkcriteria As String
Dim separ As String
Dim sepctr As Integer
sfld = Me.GRPSELECT
stDocName = "rptALLARCHIVED_BASE"
Debug.Print Me.Option69
'If users choose the all records option
If Me.Option69 = -1 Then
DoCmd.openreport stDocName, acPreview
Exit Sub
End If
On Error GoTo Err_Command72_Click
'if users select records to filter by in the list box
For Each varnumber In List67.ItemsSelected
Debug.Print VarType(List67.ItemData(varnumber))
Select Case VarType(List67.ItemData(varnumber))
Case vbDate 'date fields
separ = "#"
Case vbString 'character fields
separ = "'"
Case vbInteger, vbCurrency, vbDecimal
separ = ""
Case Else
Debug.Print VarType(List67.ItemData(varnumber))
End Select
sfilt = sfilt & separ & List67.ItemData(varnumber) & separ & ","
Debug.Print sfilt
Next
'remove trailing comma, add field name, in operator and brackets
lngLen = Len(sfilt) - 1
If lngLen > 0 Then
sfilt = "[" & sfld & "] in (" & Left$(sfilt, lngLen) & ")"
Debug.Print sfilt
End If
DoCmd.openreport stDocName, acPreview, , sfilt
Exit_Command72_Click:
Exit Sub
Err_Command72_Click:
Debug.Print Err.Number; Err.Description
Resume Exit_Command72_Click
--
Ficticiously Yours, Biggles
Steve Sanford said:
Check out the VarType() function.
This is air code, but it might help...
' I added an 's' here
ssepar = "'"
dsepar = "#"
'if users select records to filter by in the list box
sfilt = ""
For Each varnumber In List67.ItemsSelected
Select Case VarType(List67.ItemData(varnumber))
'for dates
Case vbDate ' constant = 7
sfilt = sfilt & dsepar & List67.ItemData(varnumber) & dsepar & ","
'for strings
Case vbString 'constant = 8
sfilt = sfilt & ssepar & List67.ItemData(varnumber) & ssepar & ","
'for numbers
Case vbInteger - vbCurrency, vbDecimal 'constants 2-6 & 14
sfilt = sfilt & List67.ItemData(varnumber) & ","
End Select
Next
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
But how do I identify the field type for sfld or me.grpselect?
--
Ficticiously Yours, Biggles
:
Put the logic in a function and pass the separator as a parameter.
Dorian
:
I populate a combo box with a field list, which, when selected will populate
a list box using this code:
Dim SSQL As String
SSQL = "SELECT ARCHIVED_ISSUES.[" & Me.GRPSELECT & "] FROM
ARCHIVED_ISSUES GROUP BY ARCHIVED_ISSUES.[" & Me.GRPSELECT & "] "
Me.List67.RowSource = SSQL
That part works fine, but I get errors when I try to build a list with the
different selections from the multi-select list box, since the values could
be strings, numbers, dates. So, how do I make this:
separ = "'"
createlist:
'if users select records to filter by in the list box
sfilt = ""
For Each varnumber In List67.ItemsSelected
sfilt = sfilt & separ & List67.ItemData(varnumber) & separ & ","
Next
'remove trailing comma, add field name, in operator and brackets
lngLen = Len(sfilt) - 1
If lngLen > 0 Then
sfilt = "[" & sfld & "] in (" & Left$(sfilt, lngLen) & ")"
Debug.Print sfilt
End If
DoCmd.openreport stDocName, acPreview, , sfilt
flexible enough to be able to change the separ constant to be "'", "", or "#".
Sean
