Results 1 to 3 of 3
  1. #1
    WimDC is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    12

    Use field-name as output (in query)

    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.

    WimDatabase4.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of putting all fields in 1 query, use a UNION query.
    1 query for each field:

    SELECT Tabel1.Id, Tabel1.Veld1, IIf([veld1] Like "*" & [forms]![formulier1]![search_field] & "*","fieldname veld1","no") AS [found]
    FROM Tabel1
    WHERE (((Tabel1.Veld1) Like "*" & [forms]![Formulier1]![search_field] & "*"));
    union
    SELECT Tabel1.Id, Tabel1.Veld2, IIf([veld2] Like "*" & [forms]![formulier1]![search_field] & "*","fieldname veld2","no") AS [found]
    FROM Tabel1
    WHERE (((Tabel1.Veld2) Like "*" & [forms]![Formulier1]![search_field] & "*"));
    union
    SELECT Tabel1.Id, Tabel1.Veld3, IIf([veld3] Like "*" & [forms]![formulier1]![search_field] & "*","fieldname veld3","no") AS [found]
    FROM Tabel1
    WHERE (((Tabel1.Veld3) Like "*" & [forms]![Formulier1]![search_field] & "*"));


    or make each its own query using the sqls above like:
    qnUnion=
    select * from qsFind1
    union
    select * from qsFind2
    union
    select * from qsFind3

  3. #3
    WimDC is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    12
    Thanks for the solution; works perfectly!

    Wim

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 02-19-2017, 03:19 PM
  2. Replies: 4
    Last Post: 04-23-2014, 04:35 PM
  3. Replies: 4
    Last Post: 12-30-2013, 05:51 PM
  4. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  5. Field output for height?
    By emccalment in forum Access
    Replies: 1
    Last Post: 01-29-2010, 12:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums