Array? Question

[foxhound]

I need to have the value of records from my table “Employees” in field!EmpNo put into, I assume, an array. There are 750+ records. The format needed would be: ('10001','10002','10003','10004','10005','10006','10022') etc…

Is this possible? My end result is that I will use them a sql statement when running a query. I know I could use “select *” but there may be many, many records in the future that I will need to exclude.

Any suggestions would be greatly appreciated.

--Foxhound

 

[giacomo]

The following will create the array for you, I don't quite understand why you need an array rather than just accessing the table directly. In any case I hope this works for you...

Private Sub makeEmpArray()
Dim arrEmpId() As String
Dim con As Object
Dim rs As Object
Dim stSql As String
Dim i as integer

Set con = Application.CurrentProject.Connection
stSql = "SELECT EmpNo FROM Employees;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1

i = 0
ReDim arrEmpId(i)
If (rs.EOF) Then
arrEmpId(i) = "No Employees"
Else
Do While (Not (rs.EOF))
arrEmpId(i) = rs![EmpNo]
i = i + 1
ReDim Preserve arrEmpId(i)
rs.MoveNext
Loop
End If

' This is just to verify that it worked
For i = 0 To UBound(arrEmpId) - 1
Debug.Print arrEmpId(i)
Next i

rs.Close
Set rs = Nothing
Set con = Nothing
End Sub

 

[mdmilner]

I think what he wants to do is use the field values in a selection query to return a list of records, possibly in a different table. Seems to be asking how to dynamically build PARAMETERS within VBA (whether it's even possible).

Yes, of course it's possible.

What you're going to end up doing, probably, is this:

1) Walk thru the recordset as explained in the post above and capture all those values.

2) Sort them

3) Remove duplicates

4) Determine if there are any ranges (consecutive numbers) and save them as discrete items.

5) Create SQL within VBA and attach as part of the SQL a WHERE parameter specifying an arbitrary number of conditions (or all if you're patient). Rerun the SQL as many times as needed to exhaust the list.

I'd recommend an append query and dumping the results into a temporary table and display that table once you're done.

Very cumbersome but it'd work. I don't remember for certain, but I believe there is a limit to the number of parameters that can be used in a query. This may be an Access limit, not a SQL limit. If I were guessing, I'd say 32 or 255 is the limit.

Mike

 

[foxhound]

Thank you both for your replies!

Mike, that is exactly what I want to do! Just one further question if you don't mind...if I have ('00001','000001','000002','000003') where there could be multiple sets of duplicate numbers, how could I select the unique ones?

-Foxhound

 

[mdmilner]

The easiest SQL method is to use the DISTINCT qualifier (might be wrong term)

SELECT DISTINCT fieldname FROM tblName;

This creates a single column query that only lists unique records (think it's the first listed). If you select multiple fields, the DISTINCT applies to all fields at the same time so only records where all two/three/four fields are identical would show up as a single line on the output.

In fact, this really encompases steps 1-3 in a single shot. The function call to build the SQL would need to parse the list, which will start as a comma delimited String. I arbitrarily picked (4) parameters as the maximum to send over but you can use a different number.

Sub ShowMeDistinct()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strVal as String
Dim lnCnt As Long

Set dbs = CurrentDB()

strSQL = "SELECT DISTINCT fieldname FROM tblName ORDER BY fieldname"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

lnCnt = 0
With rs
  Do Until rs.EOF
    lnCnt = lnCnt + 1
    strVal = strVal & .Fields(0).Value & ","
    If lnCnt >= 4 Then
      strVal = Left(strVal,len(strVal)-1)   ' Removes extra comma
      lnCnt = 0
      ' Functional Call to build the parameter
    End If
    .MoveNext
  Loop
End With

If lnCnt > 0 Then
    strVal = Left(strVal,len(strVal)-1)   ' Removes extra comma
     ' Functional Call to build the parameter
     Call BuildSQL(strVal)
End If

Set rs = Nothing
Set dbs = Nothing
End Sub

 

[foxhound]

Thanks Mike. I'll give it a whirl.

-Foxhound