UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Use Field-name As Output (in Query), Access 2010    
 
   
WimDC
post Dec 1 2017, 05:05 AM
Post#1



Posts: 31
Joined: 26-December 12



Hi,

maybe I'm asking something ridiculous, but is it possible to set a fieldname as RESULT in a query.

In the attached sample-database, I'm using a query by form to search for a certain string in a table.
When the string is found, I want to see in what field the string is found.
So I would be happy if there's a way to get the field-name as output. You see what I want in the query-field "found".

Can someone help me?

Thanks.

Wim
Attached File(s)
Attached File  Database4.zip ( 27.07K )Number of downloads: 3
 
Go to the top of the page
 
Minty
post Dec 1 2017, 05:36 AM
Post#2



Posts: 70
Joined: 5-July 16



Cross posted here http://www.accessforums.net/showthread.php?t=69366

Wim please have a read of this https://www.excelguru.ca/content.php?184
Go to the top of the page
 
moke123
post Dec 1 2017, 06:19 AM
Post#3



Posts: 1,152
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



you could do it with a function:

CODE
Public Function fFindField(strIn As String, varID As Long) As Variant

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim i As Integer
    Dim rslt As String


    strSql = "select * from  Tabel1 where ID = " & varID

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    For i = 0 To rs.Fields.Count - 1

        If InStr(1, rs.Fields(i), strIn) Then
            rslt = rslt & rs.Fields(i).Name & ","
       End If

    Next i

    If rslt <> "" Then
        rslt = "Yes- " & Left(rslt, Len(rslt) - 1)
       Else
       rslt = "NO"
    End If

    fFindField = rslt

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Function


Change your query to:

CODE
SELECT Tabel1.Id, Tabel1.Veld1, Tabel1.Veld2, Tabel1.Veld3, Tabel1.Veld4, fFindField([Forms]![Formulier1]![search_field],[Id]) AS [found]
FROM Tabel1;


HTH
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 07:51 AM