Query Help

[NewCoder2]

I'd like to know if it's possible to ave a query that looks for columns befor it actually selects? I might not be asking this correctly, so let me give an example.

Select (fname or first_name) as FirstN

Basically scan the dataset to see what the available columns are and then when I find a matching one, pull that data.

The data set is populated dynamically and may have fname or first_name or firstN. I'll know it'll have one of them. I just want to write 1 select to pull the data regardless of the col name. Thanks.

 

[Golom]

'fraid not.

SQL expects that any field name that you reference in your SQL statement actually exists in the source table(s).

To do this you would need to use VBA to flip through the fields in the table to find out which ones are there and then build the SQL within your program using those valid field names.

 

[NewCoder2]

darn, thought it was too easy to be true........

what about that vba idea you mentioned? how would I flip through that?

 

[Golom]

Here's a template to follow
[blue][tt]
Dim fd As DAO.Field
Dim TheField As String
Dim strSQL As String

For Each fd In CurrentDB.TableDefs("myTable").Fields
Select Case fd.Name
Case "fname", "First_Name", "FirstN"
TheField = fd.Name
Exit For
End Select
Next fd

strSQL = "Select " & TheField & " As [FirstN] " & _
"From myTable " & _
.... etc. ...

Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset ( strSQL )
[/tt][/blue]

 

[NewCoder2]

is there a way to have my case Case "fname", "First_Name", "FirstN"
set up in an external file, and use a variable there?

Case strWhatever

then in an external file
Firstname = "fname", "First_Name", "FirstN"

in the assumption that there will need to be another version of first name added to the list Like "F_Name", with out having to rewrite any code?

 

[Golom]

You would need some restructuring to do that. You can't just code ... "fname", "First_Name", "FirstN" ... in an external file and then plug it in as a variable.

If you were to create a table in Access containing your possible field names like this

tblFieldNames
fldname
fname
First_Name
FirstN

Then you could use code like
[blue][tt]
Dim fd As DAO.Field
Dim TheField As String
Dim strSQL As String
Dim rsn As DAO.Recordset

Set rsn = CurrentDB.Openrecordset ( "tblFieldNames", dbOpenTable)
TheField = ""

For Each fd In CurrentDB.TableDefs("myTable").Fields
rsn.FindFirst "fldname='" & fd.Name & "'"
If NOT rsn.NoMatch Then
TheField = fd.Name
Exit For
End If
Next fd

If Len(TheField) > 0 Then
strSQL = "Select " & TheField & " As [FirstN] " & _
"From myTable " & _
.... etc. ...

Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset ( strSQL )
End If
[/tt][/blue]
Another alternative is to have the list as a comma-separated string like "fname, First_Name, FirstN". Then the code looks like this.
[blue][tt]
Dim n As Integer
Dim fd As DAO.Field
Dim TheField As String
Dim strSQL As String
Dim myNames() As String
Const TheNames As String = "fname, First_Name, FirstN"

myNames = Split (TheNames, ",")
TheField = ""

For Each fd In CurrentDB.TableDefs("myTable").Fields
For n = LBound(myNames) To UBound(myNames)
If fd.Name = myNames Then
TheField = fd.Name
Exit For
End If
Next n
If Len(TheField) > 0 Then Exit For
Next fd

If Len(TheField) > 0 Then
strSQL = "Select " & TheField & " As [FirstN] " & _
"From myTable " & _
.... etc. ...

Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset ( strSQL )
End If
[/tt][/blue]

 

[NewCoder2]

darn, my only choice is the external file. I can't put "fname, First_Name, FirstN" = strnames in to a variable?

Const TheNames As String = strnames?