Results 1 to 13 of 13
  1. #1
    Mahdi86 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    6

    [code]Problem with query criterias?[/code]. Please do help, it quite important.

    Hi guys,
    I have recently made an access database for my company
    It contains a table and a query from the table which is connected to a form for searching for data.


    The query contains a first name, Last name, Class, Section, Busnumber and Phone number.
    I've connected the queries to the form using Like "*" & [forms]![Searchform]![firstname] ...... Same for all the other fields
    Now my problem is that when I search only for Bus number 1, It shows me a list of all the people using bus number 1 and also people in bus number 11
    I don't know what criteria to give in order to only to get people of bus number 1.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    using Like "*"
    This is a wildcard. It is being interpreted as Like *1.

    So it will return 1
    and
    11
    and
    ChickenNuggets1
    and
    Chicken Nuggets 1

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The easiest fix is to not use "like" for the bus number, just look for an exact match.

    [forms]![Searchform]![busnumber]

  4. #4
    Mahdi86 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    6
    So what exactly Do I do ?
    Cause I dont want to search for bus number 1 and its shows exactly what I want
    But when I search for First name It wont show any results?

  5. #5
    Mahdi86 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    6
    I've tried to do it and it works
    But then if I search for the first name or any other field I get a blank sheet.
    Its like only the bus number field works, rest of the fields don't

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You need to explain more on what you have and what you want to do. So you have 1 search field or do you have a search box for each field? And in your query, you have all the fields and in the critera for each field you have:
    Like "*" & [forms]![Searchform]![firstname] for the First name criteria
    Like "*" & [forms]![Searchform]![lastname] for the Last Name criteria
    Like "*" & [forms]![Searchform]![busnumber] for the BusNumber criteria, etc.?

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You have to deal with Null in the bus number field. Try this:

    [forms]![Searchform]![busnumber] or isNull([forms]![Searchform]![busnumber])

  8. #8
    Mahdi86 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    6
    Quote Originally Posted by Bulzie View Post
    You need to explain more on what you have and what you want to do. So you have 1 search field or do you have a search box for each field? And in your query, you have all the fields and in the critera for each field you have:
    Like "*" & [forms]![Searchform]![firstname] for the First name criteria
    Like "*" & [forms]![Searchform]![lastname] for the Last Name criteria
    Like "*" & [forms]![Searchform]![busnumber] for the BusNumber criteria, etc.?
    I have a search form which has text boxes for every field of the query table
    I used this form to search for the datas I want in the query
    Am I clear enough now

  9. #9
    Mahdi86 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    6
    Quote Originally Posted by John_G View Post
    You have to deal with Null in the bus number field. Try this:

    [forms]![Searchform]![busnumber] or isNull([forms]![Searchform]![busnumber])
    I have no null values in the bus field !

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Quote Originally Posted by Mahdi86 View Post
    Am I clear enough now
    Good luck!!

  11. #11
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I have no null values in the bus field !
    Ok, but you do have a null in the search form if you don't enter a value to search for. You have to account for that, which is what the second part of the criteria does.

    Is there is no bus number to search for, then isNull([forms]![Searchform]![busnumber]) is true, which means the entire expression is true. All records will pass the bus number check.

  12. #12
    Mahdi86 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    6
    Quote Originally Posted by Bulzie View Post
    Good luck!!
    What am I supposed to do ?

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I believe you need to dynamically build the where clause depending on what search field(s) they enter. This would be onClick on a "Search" Button on form.

    ' Create "Where: statement from all the criteria boxes that they enter data into on the form.
    ' DQ = Double Quote, SQ = Single Quote, HT = Hashtag

    'For Text fields syntax is = '" & Me!TextField & "'" The quotes around this are (SQDQ & Me!TextField & DQSQDQ)'
    'For Numeric fields syntax is = '" & Me!NumericField The quotes around this are (DQ & Me!NumericField)
    'For Date fields syntax is = #" & Me!DateFIeld & "#" The quotes around this are (HTDQ & Me!DateField & DQHTDQ)
    'For Like Searches syntax is Like '*" & Me!TextField & "*'" The quotes around this are (SQHTDQ & Me!TextField & DQHTSQDQ) This lets you search for any part of the value.

    'For your specific program. Assume all are text except BusNumber. If you have spaces in names, use brackets [] around names:

    ================================================== =======================================
    'Code Below, This would be onClick on a "Search" Button on form

    Dim strSQL, strWhere As String

    If Not IsNull(Me!SearchLastName) Then strWhere = strWhere & " AND LastName= '" & Me!SearchLastName & "'"
    If Not IsNull(Me!SearchClass) Then strWhere = strWhere & " AND Class = '" & Me!SearchClass & "'"
    If Not IsNull(Me!SearchSection) Then strWhere = strWhere & " AND Section= " & Me!SearchSection & "'"
    If Not IsNull(Me!SearchBusnumber) Then strWhere = strWhere & " AND Busnumber=" & Me!SearchBusnumber
    If Not IsNull(Me!SearchPhoneNumber) Then strWhere = strWhere & " AND SearchPhoneNumber= '" & Me!SearchPhoneNumber & "'" Assume this is a text field.

    'Create SQL statement
    strSQL = "SELECT * FROM qryYourQueryName"

    'Trim leading " AND " from the where clause. If no criteria select will add no Where clause and return all records.
    If Not IsNull(strWhere) And strWhere <> "" Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)
    End If

    'Me![SearchFormName].Form.RecordSource = strSQL
    'Me![SearchFormName].Requery
    Last edited by Bulzie; 08-24-2016 at 07:02 AM.

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

Similar Threads

  1. update query code problem
    By Jen0dorf in forum Access
    Replies: 2
    Last Post: 12-22-2015, 04:35 AM
  2. Problem with this code
    By Abacus1234 in forum Programming
    Replies: 3
    Last Post: 10-16-2015, 02:25 PM
  3. Replies: 1
    Last Post: 05-04-2013, 12:19 PM
  4. Problem with Code
    By jackiea in forum Programming
    Replies: 1
    Last Post: 10-07-2011, 05:59 PM
  5. Problem with INSERT INTO query code
    By rghollenbeck in forum Queries
    Replies: 8
    Last Post: 09-27-2011, 12:16 PM

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