Results 1 to 8 of 8
  1. #1
    NewbieInCT is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    20

    Consolidated Screening List Search database

    Hello,

    I am trying to import a csv file from http://export.gov/ecr/eg_main_023148.asp Downloading the Consolidated Screening List and be able to create a search form.

    The idea is to tell me if a name, or partial name exists.



    I know how to import the CSV or Excel file into Access (into its own table), or have Access link to it, so that it does not have to be re-imported everytime.

    I am having problems creating a search box that will provide me with the full line of detail.

    Example: I want to find the word plenty. I could easily search in Excel and it will find no results. But I would like it to tell me no results found, a time and date stamp of the time of my search. However, if the word "lent" was searched, any row in which "lent" was found would show up below it, with a time and date stamp on the page.... this could even be a report instead of a query.

    Another example: If I am searching for the word "tent", I would like to see a report that brings up any row that has a partial of the "tent" showing.. so if a row had any word in it like reTENTion, or deTENTion, the entire row would appear.

    So, in my mind, and it may not be this simple, but I have a search box, where I enter a word... and either below, or in a separate report, any time the word, or partial word appears, I see the entire row... and if possible, say No results returned if none... and the date and time...

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If criteria met, record will display. If criteria not method record will not display and cannot return substitute line.

    Can show match results in a calculated field then use criteria under this field to sort or filter records.

    SearchResult: IIf(InStr(Forms!formname!textboxname,[fieldname])>0,"Found","NotFound")

    Or

    SearchResult: IIf([fieldname] Like "*" & Forms!formname!textboxname & "*","Found","NotFound")
    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.

  3. #3
    NewbieInCT is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    20
    June 7, I have no idea how to incorporate this into the search function or report? I hate to ask how, but I just do not know how... I'm sorry

  4. #4
    NewbieInCT is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    20
    Here is the file I downloaded from the internet. I have deleted out all but 20 records to be able to upload the database to this trail... the actual report has around 40,000 records.
    Attached Files Attached Files

  5. #5
    NewbieInCT is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    20
    I have attached the db, if you could help....

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What field should be searched for 'tent'? If you want to search all fields, that significantly complicates the issue.
    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
    NewbieInCT is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    20
    I wanted to search everything. Would it be easier to write a macro that concatenated everything into 1 field, so that it was like searching for 1 column, and a report that gave any possible matches... and take it offline to actually review the matches?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That would be one approach, although don't know why need to take it offiline - what do you mean by that?

    However, in the code that would do the concatenation could just as easily compare the search criteria with field value and return True/False for the record. Here is code for concatenating related records: http://allenbrowne.com/func-concat.html

    Non-code approach would be to put criteria under each field in query with OR operator, that means each criteria on a separate line: Like "*" & Forms!formname!controlname & "*". This would mean that records not having a match would not list.
    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. Search List Box with Text Box
    By dalton in forum Access
    Replies: 6
    Last Post: 01-30-2013, 09:58 AM
  2. Search From List and Key Search
    By netchie in forum Forms
    Replies: 14
    Last Post: 02-23-2012, 01:37 PM
  3. Foreign keys in a consolidated table
    By threepwoodjr in forum Database Design
    Replies: 3
    Last Post: 01-14-2011, 11:25 PM
  4. Search form with list boxes
    By scottay in forum Programming
    Replies: 15
    Last Post: 07-27-2010, 09:28 AM
  5. Simple list/combo search fails
    By Dega in forum Forms
    Replies: 4
    Last Post: 02-08-2010, 08:39 AM

Tags for this Thread

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