Vlookup & Match on a table in microsoft access

[forestgump]

Hi all,

Is there a way you can do a vlookup and match type query in access? I need to know how to reference the values in the first column but then all the table field names so the query can find the correct number by looking down and across the table. Is this possible? and if so how would you do it?

Any help would be much appreciated. If more detail is required please let me know.

Regards,

Forestgump

[ranman256]

you build a query. In the criteria , put what your are searching for and show the fields you wish to see.

[forestgump]

Hi,

Thanks for your help, but i don't quite understand your response. I know how to build a query and return the values in fields but in this scenario I want to return the actual field names. i.e. If i had a table called 'Employees' and the fields were 'EmpID', 'FirstName', 'Surname' i would want the query to return 'EmpID', 'FirstName', 'Surname'.

Regards

Forestgump

[ssanfu]

Code is like a box of chocolates....


Here is a function to return field names from a table. Maybe this will help you:

Sub test()
    Dim fldlst As String
    fldlst = listTableFields("YourTableName")
    MsgBox fldlst

   ' Debug.Print fldlst
End Sub



Function listTableFields(sTblName As String) As String

    Dim db As DAO.Database
    Dim tblfld As DAO.TableDef
    Dim fld As Field
    Dim sListFields As String
    
    Set db = CurrentDb()
    Set tblfld = db.TableDefs(sTblName)
    For Each fld In tblfld.Fields    'loop through all the fields of the table
        sListFields = sListFields & fld.Name & ", "
    Next

    If Len(Trim(sListFields)) > 1 Then
        sListFields = Left(sListFields, Len(Trim(sListFields)) - 1)
    End If
    
'Debug.Print sListFields


    listTableFields = sListFields

Error_Handler_Exit:
    Set tblfld = Nothing
    Set db = Nothing
    Exit Function
End Function

[June7]

Maybe if you explained why you need this we can better advise on an approach to handle.

Why would you want a query that displays field names in every record as opposed to the data that is in those fields?