SELECT DISTINCT returns Null to a rs, but SELECT returns values

[HoneyBee88]

My experience in VBA is intermediate, but my familiarity with MS Access (while growing every day!) is still lacking. I learned in excel, and have only been using access for a month and a half.

I am trying to create a function that creates a recordset when certain parameters are passed to it. The parameters are, generally, table and field names and boolean values used to determine whether or not to add things like "WHERE" clauses and "DISTINCT" keywords to the sql string.

It works! No problems-- unless I use the DISTINCT Keyword in the SQL string. (This is determined, in the code, by whether or not I pass "True" or "False" for the Distinct parameter in the function) Then all the values it would assign to a recordset (a recordset which is then passed to another subroutine) are null. Even before there is any passing between function and sub.

I am using Access 2003. Some kind of library/reference file issue may be at fault. I have a very vague idea of how that actually works, but I know that whoever installed Access at my company did not choose to include all the whistles and bells. I occassionally run into things (like "Office Links" under Tools) which are not supported/require further installation.

I have copied all the code relevent code below. I welcome any and all critique. I'd prefer critique relevent to the SELECT/ SELECT DISTINCT Descrepancy, but if there is something else I need to know then let me know!
________________________________________________
Sub BuildingFirstQuery()

Dim i As Integer
Dim RS2 As ADODB.Recordset


'Setting the created recordset to a new name. As a side issue,
'I'd eventually like to use this code repeatedly. Does anyone know
'how closing a recordset with one name ("CreateRecordset") affects
'using it by another name ("RS2")?

Set RS2 = CreateRecordset("Cust", "tBasic", False)

'Checking the recordset now that it has been passed from the function
'to the sub to see if the values were lost in the pass.
'It appears that they were not.

For i = 1 To RS2.RecordCount
Debug.Print "RS2: " & RS2![field1]
Next i
End Sub
________________________________________________________
Function CreateRecordset(ByRef rs1Field As String, rs1Table As String, _
Distinct As Boolean, Optional rs1WhereField As String, _
Optional rs1WhereVal As String, Optional Numeric As Boolean) As ADODB.Recordset
Dim i As Integer
Dim sql As String
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Set CreateRecordset = New ADODB.Recordset

'Creating the sql string that will open the recordset.

sql = "SELECT "
If Distinct = True Then
sql = sql & "DISTINCT "
End If
sql = sql & "[" & rs1Field & "] AS [fIeld1] FROM " & rs1Table & " "
If rs1WhereField <> "" And rs1WhereVal <> "" Then
Select Case Numeric
Case False
sql = sql & "WHERE [" & rs1WhereField & "] = '" & rs1WhereVal & "'"
Case True
sql = sql & "WHERE [" & rs1WhereField & "] = " & rs1WhereVal
End Select
End If
CreateRecordset.Open sql, cnn, adOpenKeyset, adLockReadOnly

'Giving myself a reminder and checking the recordset values.
Debug.Print "Don't forget to close the created recordset"
For i = 1 To CreateRecordset.RecordCount
Debug.Print " CR: " & CreateRecordset![field1]
Next i

End Function

 

[kjv1611]

My initial guess without looking in depth at your code is to use GROUP BY instead of DISTINCT.

Here's an example:

SELECT    rsField1
FROM      rs1Table
WHERE     [i]Condition[/i]
[HIGHLIGHT]GROUP BY  rsField1[/HIGHLIGHT]

--

"If to err is human, then I must be some kind of human!" -Me

 

[HoneyBee88]

Thanks!

 

[lameid]

I don't see anything wrong with your code. While certainly you could use Group By instead of distinct it should not be necessary.

You might try grabbing the SQL built by your code and pasting it inot the SQL view of a new query replacing everything and double checking how it runs. You can also post the SQL here...

My only thought is that maybe the problem is with the input to the function rather than the function itself.

As for the comments of your code, your function createrecordset returns a recordset. You need to set it to a variable to use it and each call will give you a new recordset.