Results 1 to 8 of 8
  1. #1
    sgray is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    7

    Search Query

    Hi all,

    I've built a basic search function in my database where users can enter information into a form called SearchF. A query then uses this informaiton and builds a report. The query uses this criteria:

    Like "*" & [Forms]![SearchF]![Field Name] & "*"



    There are multiple fields which use this criteria.

    PROBLEM! If the data in one of the fields is blank then that item will not show.

    Can anyone help me with this.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This comes up time and time again, I don't know how people solve it. In the meantime, until someone else answers, try this in the criteria line:

    Like "*" & [Forms]![SearchF]![Field Name] & "*" Or IsNull([Forms]![SearchF]![Field Name])

  3. #3
    sgray is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    7
    no good. I'll keep trying but I dont think im going to get it by myself

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Do not know VBA, but perhaps in VBA something like the pseudo-code below


    strsql = "SELECT myTable.field1, myTable.field2, myTable.field3, myTable.field4, myTable.field5 FROM myTable WHERE 1 = 1"

    if [Forms]![Form1]![Field1] Is Not Null Then
    strsql = strsql & "AND Field1 Like '*' & [Forms]![Form1]![Field1] & '*' "
    End if

    if [Forms]![Form1]![Field2] Is Not Null Then
    strsql = strsql & "AND Field2 Like '*' & [Forms]![Form1]![Field2] & '*' "
    End if

    Just check out if below query gives some guidelines :

    Code:
    SELECT 
    myTable.field1, myTable.field2, myTable.field3, myTable.field4, myTable.field5
    FROM 
    myTable
    WHERE 
    (
    	(
    		(1)=1
    	) 
    	AND 
    	(
    		(IIf(IsNull([Forms]![Form1]![Field1]),"1 = 1",[Field1] Like "*" & [Forms]![Form1]![Field1] & "*"))<>False
    	)
    	AND
    	(
    		(IIf(IsNull([Forms]![Form1]![Field2]),"1 = 1",[Field2] Like "*" & [Forms]![Form1]![Field2] & "*"))<>False
    	)
    	AND
    	(
    		(IIf(IsNull([Forms]![Form1]![Field3]),"1 = 1",[Field3] Like "*" & [Forms]![Form1]![Field3] & "*"))<>False
    	)
    );
    Thanks

  5. #5
    sgray is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    7
    Sorry but Im not use to using code like this in access. could you please help explain how im ment to use this.

  6. #6
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    If it is the VBA pseudo code that you are indicating, then let us wait for some one to help.

    If it is the SQL Query Code, you are indicating, then

    In the code, Replace

    myTable with Your Table Name,
    myTable.field1, myTable.field2, etc with Your Table Field Names,
    Form1 with Form Name,
    and the names along with Form1
    i.e Field1, etc with the name of the Search Form Field
    eg.

    [Forms]![Form1]![Field1] should be [Forms]![SearchF]![Form Field Name 1],
    [Forms]![Form1]![Field2] should be [Forms]![SearchF]![Form Field Name 2],
    etc.

    Also,
    In All
    "[Field1] Like"
    replace Field1 with your table field name.

    Do let us know if I am misunderstanding what you are asking for

    Thanks

  7. #7
    sgray is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    7
    Seems to work however it uses two tables linked via a field called OrderNumber and it says the specified field [ordernumber] could refer to more than one table. This is what i have:

    SELECT Items.Description, Items.Customer,Orders.OrderNumber,Items.PartNumber ,Items.InvoiceNumber,Orders.Supplier,Orders.DateOr dered,Orders.DateArrived
    FROM Orders INNER JOIN Items ON Orders.[OrderNumber] = Items.[OrderNumber]

    How do i fix this so it gets OrderNumber from the table Orders.

    THANKS x 1 million for all the help

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Is above a different question from the original one?
    If yes would suggest starting a new thread.

    Thanks

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

Similar Threads

  1. Help with search query
    By Sirius in forum Queries
    Replies: 0
    Last Post: 08-08-2011, 03:33 PM
  2. ID search Query
    By clarkej in forum Queries
    Replies: 3
    Last Post: 01-25-2011, 08:03 AM
  3. Search Query
    By convey2web in forum Queries
    Replies: 3
    Last Post: 11-08-2010, 02:27 PM
  4. Search with in a Query
    By AccessCodeMonkey in forum Queries
    Replies: 4
    Last Post: 03-09-2010, 03:16 PM
  5. yes/no search query
    By islandboy in forum Queries
    Replies: 7
    Last Post: 06-30-2009, 09:01 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