Results 1 to 7 of 7

Simple Autofill Question

  1. #1
    kevins is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    26

    Simple Autofill Question

    I have a search form for my database that is great except for one thing...if one of my criteria on the table or query is null, my search will not retrieve that record. I can't seem to find a fix for the search query, BUT I thought of a way around it...the only thing is that I don't know how to do it, which is where you experts come in...there HAS to be a simple line of code that will autofill a field with any character...a period, dash, whatever...as long as there is something in the field, my search will work...can you help? THANK YOU!

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,082
    How are you constructing the search criteria? Does query reference input controls on form? Using VBA to construct a filter string by reference to input controls on form? Show query and/or code.

    You want all records where field equals the given criteria or is null?

    I expect that need to add Or Is Null to the criteria for each field.

    You can have an expression handle the null and return a value. The value can be a character or empty string.

    Nz(fieldname, "some character")
    Nz(fieldname, "")
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    kevins is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    26
    I have a form labeled "Search Form" that has text boxes for name, company, city, etc. My query below (labeled "Search Query" references a query labeled "Candidates Extended." If any one of these entries in Candidates extended (city, state, etc.) is null, my search query will not retrieve all records that meet the other criteria.

    I use a form called "Candidate Details" to input the data. It is on the Candidate Details form that I wish to be able to autofill blank or null fiels with a . or - or some kind of character that shows there is an entry in that field...that way my search will retrieve all of the records that meet specified criteria and aren't excluded because of a null field.

    I'm not very familiar with VBA but I figured there is probably a simple way to accomplish the autofill. Thanks!

    SELECT [Candidates Extended].[Contact Name], [Candidates Extended].Company, [Candidates Extended].[Job Title], [Candidates Extended].City, [Candidates Extended].State, [Candidates Extended].Status, [Candidates Extended].[Specialty 1], [Candidates Extended].[Specialty 2], [Candidates Extended].[Specialty 3], [Candidates Extended].[Specialty 4], [Candidates Extended].Category
    FROM [Candidates Extended]
    WHERE ((([Candidates Extended].[Contact Name]) Like "*" & [Forms]![Search Form]![Name] & "*") AND (([Candidates Extended].Company) Like "*" & [Forms]![Search Form]![Company] & "*") AND (([Candidates Extended].[Job Title]) Like "*" & [Forms]![Search Form]![Job Title] & "*") AND (([Candidates Extended].City) Like "*" & [Forms]![Search Form]![City] & "*") AND (([Candidates Extended].State) Like "*" & [Forms]![Search Form]![State] & "*") AND (([Candidates Extended].Status) Like "*" & [Forms]![Search Form]![Status] & "*") AND (([Candidates Extended].[Specialty 1]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].[Specialty 2]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].[Specialty 3]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].[Specialty 4]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].Category) Like "*" & [Forms]![Search Form]![Category] & "*"));

  4. #4
    kevins is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    26
    Oh and I've tried adding Or Is Null to the string and got nada.

  5. #5
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,082
    Okay, handle the null with an expression and apply the criteria to that constructed field.

    Nz([Specialty 1],"") AS Spec1

    At the core of this issue is that data does not appear normalized and that is allowing fields with null. Similar named fields (Specialty, Speciality1, Specialty2, Specialty3, Specialty4) with nulls is indicator of non-normalized data. Do you need to be able to search for multiple specialties at one time?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  6. #6
    kevins is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    26
    Sorry I don't totall follow. Where exactly am I inputting the expression? In the Candidate Details form where I input the data or on the Search Form?

    I don't necessarily need to search multiple criteria at the same time, but I do have four different columns in my table to put four different specialties. One specialty text box on my search form will suffice, as long as it will retrieve records where the specified criteria matches Specialty 1 OR Specialty 2, etc. I planned on just adding three more criteria lines in my query for that and just changing the specialty criteria. It's just the null fields that are throwing a wrench in my gears.

  7. #7
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,082
    The expression goes in the query to create a new field. This will assure no Null values in that field in the RecordSource. Then put the parameter references under these constructed fields.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

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

Similar Threads

  1. A simple question:
    By kosti in forum Queries
    Replies: 4
    Last Post: 10-12-2011, 10:46 AM
  2. Yet Another autofill question
    By srcacuser in forum Database Design
    Replies: 1
    Last Post: 01-29-2011, 09:05 AM
  3. Simple VBA Question
    By koper in forum Access
    Replies: 1
    Last Post: 01-20-2011, 10:07 AM
  4. Simple question?
    By roads.zx in forum Access
    Replies: 0
    Last Post: 10-15-2009, 03:56 PM
  5. Very simple question!
    By fiddley in forum Programming
    Replies: 2
    Last Post: 04-28-2009, 01:16 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
  •  
Tech Forums: Microsoft Office Forums