Results 1 to 11 of 11
  1. #1
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36

    Search to ignore null fields


    Currently i have a query that is based on inputs from a search form. The query will not return any results if one of the fields in the table is empty. Currently, i have to fill them all with "N/A" for the search to work properly.

    This is the sql for my query as it stands -
    Code:
    SELECT [Job Form].[Invoice Number], [Job Form].Customer, [Job Form].[Booking Reference], [Job Form].[B/L Number], [Job Form].[Number of Containers], [Job Form].Line, [Job Form].Vessel, [Job Form].ETA, [Job Form].ETS, [Job Form].[Invoice Value (£)], [Job Form].[Cost (£)], [Job Form].[Internal invoice number], [Job Form].IntInvoice2, [Job Form].IntInvoce3
    FROM [Job Form]
    WHERE ((([Job Form].[Invoice Number]) Like forms!Search!qinvoice & "*") And (([Job Form].Customer) Like forms!search!qcustomer & "*") And (([Job Form].[Booking Reference]) Like forms!search!qbooking & "*") And (([Job Form].[B/L Number]) Like forms!search!qbl & "*") And (([Job Form].Line) Like forms!search!qline & "*") And (([Job Form].[Internal invoice number]) Like forms!Search!qInternal & "*")) Or ((([Job Form].[Invoice Number]) Like forms!Search!qinvoice & "*") And (([Job Form].Customer) Like forms!search!qcustomer & "*") And (([Job Form].[Booking Reference]) Like forms!search!qbooking & "*") And (([Job Form].[B/L Number]) Like forms!search!qbl & "*") And (([Job Form].Line) Like forms!search!qline & "*") And (([Job Form].IntInvoice2) Like forms!Search!qInternal & "*")) Or ((([Job Form].[Invoice Number]) Like forms!Search!qinvoice & "*") And (([Job Form].Customer) Like forms!search!qcustomer & "*") And (([Job Form].[Booking Reference]) Like forms!search!qbooking & "*") And (([Job Form].[B/L Number]) Like forms!search!qbl & "*") And (([Job Form].Line) Like forms!search!qline & "*") And (([Job Form].IntInvoce3) Like forms!Search!qInternal & "*"));

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    If you want the fields to appear as N/A then you could use the NZ function in each field that this would occur.

    Give each field a new name, ie. If the field name was Input, change it to qInput or similar. Then in the field name in your query, type qInput: NZ(Input,"N/A")

  3. #3
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Quote Originally Posted by alansidman View Post
    If you want the fields to appear as N/A then you could use the NZ function in each field that this would occur.

    Give each field a new name, ie. If the field name was Input, change it to qInput or similar. Then in the field name in your query, type qInput: NZ(Input,"N/A")
    I dont want them to appear as N/A, im just using that as a short term solution until i can find a fix for the query.

    I want the query to be able to find the record if one of the fields it searches by is empty. I used the & "*" wildcard thinking that would work but it didnt.

  4. #4
    kevins is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    26
    I am having the exact same issue. My search will not retrieve records that have null fields. Also using the "*" wildcard. Is the a way to add something like Or [Is Null]?

  5. #5
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Yeah, im not sure whats causing it exactly but the wildcard i thought would have worked. Obviously not.

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    What happens when you add Or IsNull to each of the criteria for each of the fields that you are querying?

  7. #7
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Quote Originally Posted by alansidman View Post
    What happens when you add Or IsNull to each of the criteria for each of the fields that you are querying?
    Seems to make no difference what so ever. Ive had a few issues with this query along the way, i think in tweaking with it to get other problems fixed it has somehow messed the whole thing up. the SQL is all over the place... im going to try and rebuild the search, see how that goes...

  8. #8
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    If you upload your database with sample data in it, perhaps, another set of eyes will spot your issue(s). Make sure to run a compact and repair and dummy up any confidential data. Additionally, if you make it an .mdb file (earlier Access version), you will have the possibility of more responses as not everyone has upgraded to 2007 or 2010.

    Alan

  9. #9
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Ok the database is in the zip folder.

    The "search" query will not find any records that have null fields in what it searches for. Also, if you type something into the parameter value box that pops up (or type it into the search form) then it gives you an error saying the query is too complicated?

    EDIT:
    Code:
    SELECT [Job Form].[Invoice Number], [Job Form].Customer, [Job Form].[Booking Reference], [Job Form].[B/L Number], [Job Form].Line, [Job Form].[Internal invoice number], [Job Form].IntInvoice2, [Job Form].IntInvoce3
    FROM [Job Form]
    WHERE ((([Job Form].[Invoice Number]) Like [forms]![Search]![qinvoice] & "*") AND (([Job Form].Customer) Like [forms]![search]![qcustomer] & "*") AND (([Job Form].[Booking Reference]) Like [forms]![search]![qbooking] & "*") AND (([Job Form].[B/L Number]) Like [forms]![search]![qbl] & "*") AND (([Job Form].Line) Like [forms]![search]![qline] & "*") AND (([Job Form].[Internal invoice number]) Like [forms]![Search]![qInternal] & "*")) OR ((([Job Form].[Invoice Number]) Like [forms]![Search]![qinvoice] & "*") AND (([Job Form].Customer) Like [forms]![search]![qcustomer] & "*") AND (([Job Form].[Booking Reference]) Like [forms]![search]![qbooking] & "*") AND (([Job Form].[B/L Number]) Like [forms]![search]![qbl] & "*") AND (([Job Form].Line) Like [forms]![search]![qline] & "*") AND (([Job Form].IntInvoice2) Like [forms]![Search]![qInternal] & "*")) OR ((([Job Form].[Invoice Number]) Like [forms]![Search]![qinvoice] & "*") AND (([Job Form].Customer) Like [forms]![search]![qcustomer] & "*") AND (([Job Form].[Booking Reference]) Like [forms]![search]![qbooking] & "*") AND (([Job Form].[B/L Number]) Like [forms]![search]![qbl] & "*") AND (([Job Form].Line) Like [forms]![search]![qline] & "*") AND (([Job Form].IntInvoce3) Like [forms]![Search]![qInternal] & "*"));
    That is the working SQl for the search, this however sill does not show records that have one of the fields empty...

  10. #10
    kevins is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    26
    I was thinking instead of the search query including null fields, is there a way to auto-populate the form or query you are searching with a random character? For example, I have a form that I use to input client information. Is there a line of code I can use to "auto fill" fields that I may otherwise leave blank? If I stick a [.] in a null field, they will come up in my search since all fields are not null. Is there a way to auto fill a [.] for null fields on my Client Details form? If we do that, the query will work.

  11. #11
    joelmeaders is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    33
    I am willing to bet the fields on your search form are not null. They could contain an empty string which would then disqualify all null fields in the table. Set them all to vbNullString.

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

Similar Threads

  1. How to Check If Two Fields are Null?
    By alpinegroove in forum Programming
    Replies: 5
    Last Post: 01-04-2012, 01:41 PM
  2. Query search wont display null fields
    By Coffee in forum Queries
    Replies: 2
    Last Post: 08-08-2011, 09:00 AM
  3. Combine two fields, Null fields involved
    By yes sir in forum Access
    Replies: 9
    Last Post: 10-03-2010, 09:20 AM
  4. Replies: 5
    Last Post: 03-20-2010, 08:30 AM
  5. On Click - Ignore Required Fields
    By amy in forum Forms
    Replies: 1
    Last Post: 08-18-2009, 07:42 AM

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