Results 1 to 12 of 12
  1. #1
    ParkerM is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    3

    Question How to Return a Row From Input Value

    I am trying to create a form, basically the Vlookup function from Excel, but in Access. All our records for hardware are stored in an Access database and I receive just the FBT codes that are scanned daily.

    I would want to be able to enter a list of the FBT codes and return the whole line as a table, is this possible to do in Access?

    Thank you,



    -PM

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    enter a list of the FBT codes and return the whole line
    Not sure what you mean. A list isn't a line? It seems like the view you want ("table") is a select query datasheet view, but lists are records, a "whole line" would be one record comprised of table fields (not usually called 'columns' in Access unless referring to a form in datasheet view).

    I have a suspicion that your table is designed like a spreadsheet. That would not be good.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    SELECT * FROM your_table WHERE fbt_code In(FBT codes go here separated by commas)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You want to use the daily list of FBT codes as criteria to filter a table? In what format do you receive this daily list - Excel worksheet? As Micron indicated, would help to know your data strucure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ParkerM is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    3
    Yes I get the list as an excel file and the records are stored in access

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Still don't know your data structure. Still don't know what you mean by "return the whole line as a table".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    If you attach an example file it's easier to show you how to do it by being able to directly use the database structure.

  8. #8
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Usually, when you use a vlookup in excel, you do this in access with a relation between two tables. So, import or link the FBT data you receive as a table in your access database and then relate it to the table that contains the data you want to filter. How to build this relation, depends on your data structure which we don't know.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    The equivalent was Dlookup() I thought?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    A Vlookup is searcing matching values in another table, the primary way to do this in a database is to join the two tables. IMO Domain lookup functions can be, in some cases, a good alternative but never as fast and efficient as a join.

  11. #11
    ParkerM is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    3
    Apologies friends, I am not a programmer and have limited knowledge of such things.
    It seems based on your replies that I should import the entries into an excel table and search that way...

    However, I have attached a sample of the table (the actual one is far larger) and a sample of the list of FBT entries for your consideration

    FBT Table.accdb

    FBT Return All Related Fields
    *FBT44463*
    *FBT44464*
    *FBT44465*
    *FBT44466*
    *FBT44467*
    *FBT44468*
    *FBT44469*
    *FBT44470*
    *FBT44471*
    *FBT44472*
    *FBT44473*
    *FBT44474*
    *FBT44475*
    *FBT44476*
    *FBT44477*
    *FBT44479*
    *FBT44481*
    *FBT44482*
    *FBT44483*
    *FBT44484*
    *FBT44485*
    *FBT44486*
    *FBT44487*
    *FBT44488*
    *FBT44489*
    *FBT44490*
    *FBT44491*
    *FBT44492*
    *FBT44493*
    *FBT44494*
    *FBT44495*
    *FBT44496*
    *FBT44497*
    *FBT44498*

    Thank you,

    PM

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't think anyone is suggesting you export to Excel to accomplish, they were just comparing functions.

    If you set an External Data link to the Excel list of FBT codes then use that link as a table in query, maybe that will get what you want.

    SELECT [FBT Table].* FROM [FBT Table] INNER JOIN Sheet1 ON [FBT Table].FBT = Sheet1.FBT;

    Can even include the Excel worksheet in Access query without setting an External Data link. Example:

    SELECT [FBT Table].* FROM [FBT Table] WHERE FBT IN(SELECT xlData.FBT FROM [Sheet1$] AS xlData IN 'C:\Users\Owner\June\Forums\FBTs.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes]);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 6
    Last Post: 03-25-2022, 11:45 AM
  2. Replies: 1
    Last Post: 07-13-2021, 11:42 PM
  3. Input date and return value
    By rayted in forum Queries
    Replies: 2
    Last Post: 12-11-2018, 07:47 AM
  4. Replies: 6
    Last Post: 04-04-2017, 02:50 AM
  5. Replies: 1
    Last Post: 11-04-2014, 12:07 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