Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    I am sooo close to getting this now.



    I have redone the form from scratch and it works for two criteria but not the rest...

    The form I am now using is the "New Client Search" form in the database attached.

    If you only use the search by status or search by name boxes then it works perfectly. I think you can even use both of them in conjunction. However if you try to search by Email, then it returns all results, or in the case of by nationality no results.

    If you search "Jo" in client name then you get 2 results. Correct.

    If you search by status and put in "cold" then results are also filtered.

    However, if you search by email "hotmail" for example then it returns all results even though I have used the same criteria for both in the query

    Like [Forms]![New Client Search].[QClientName] & "*"

    Like [Forms]![New Client Search].[QEmail] & "*"

    Any ideas? Also is it possible to change any of the search input boxes to combo boxes or would this be too complicated?
    Attached Files Attached Files

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is because 'hotmail' is in the middle of the email address, not at the beginning of the string. I got this to work: Like "*" & [Forms]![New Client Search].[QEMail] & "*"

    If you want to do a new search, need to remove the filter, otherwise subsequent search will only search the already filtered set. Include Me.Requery at end of the clear procedure.
    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. #18
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Thanks June but for some reason that is not working for me at all. I did not realise but the searches were only working if the inputted words started with those same letters. If I search "Smith" now for example it does not come up, even the way you have shown with the wildcard first.

    Surely I must be missing something really simple...?!

    EDIT: I WAS MISSING SOMETHING REALLY SIMPLE!!

    I hadn't saved the query! Thanks June!

  4. #19
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Ah, it seems I jumped the gun. I have used that code for the 6 textboxes but the other 3 on the right hand side still will not work. Nationality for example is now Like "*" & [Forms]![New Client Search].[QNationality] & "*" but returns no results...

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't know why you allow the user so much lattitude in search. Nationality, Source, Status, for instance, should have limited choices given by a combobox RowSource.

    Name parts should be in separate fields to control consistency then can be concatenated to display as desired. A combobox RowSource could be:
    SELECT LastName & ", " FirstName As ClientName FROM Clients ORDER BY LastName, FirstName;
    Then with AutoExpand Yes, as user types name (starting with last) the combobox will display matching values. Same for 1stContactPerson.

    That leaves only the email address needing the two wildcard characters.

    If you want to allow free search of name because all you might have is possibly the first name, I just tried the two wildcard search and it worked. What isn't working is search on Nationality, Source, Status, 1stContact. Lookup set at table level strikes again. I finally realized that the Clients table is displaying the alias values for Nationality, Source, Status, 1stContact but that the numeric primary key is the true value. I was also misled because these fields are set as text. They must be number or will get a type mismatch error when you try to join the tables in query. So either save the actual text description or change the field type. If you stay with the autonumber as PK, then the query will have to include these three tables so that the text descriptors will be available to search on. Set jointype as 'show all records from Clients ...".

    Review http://dbageek.blogspot.com/2004/10/...fields-in.html
    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.

  6. #21
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Hi June,

    Sorry, I am a little confused by the combo box part of your post. I think I have solved the rest and have taken away some of the client search options. However, I am now on the sales in progress section and would like to be able to search by status (as I would on clients ideally also).

    How can I achieve this? If I need to start from scratch on this part it is not a problem. I can restructure the design so that it is correctly done, but please explain to me exactly the steps I should take to achieve the correct design.

    Having the fields as number did not seem to work either although I suspect I have done something incorrectly here again.

    Please help me. Once I crack this combobox search problem then I think I will finally have this thing finished!!
    Attached Files Attached Files

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What confuses you about the combobox suggestion? You are using comboboxes elsewhere so you have familiarity with them. The Status search box should be a combobox. There are only 3 status values, the combobox will ensure user makes valid entry.

    The status values are so small, I would just save the actual descriptive value, not the record ID, and not have to deal with lookup. Oh, I see you are already doing that. Then the autonumber field in ClientStatus is not even necessary, it certainly shouldn't be the primary key field.

    The Client search is working just fine.

    For the SIP search, again, you are saving the status description, not ID, so ID is unnecessary. Don't need the Sale Status table in the SIP Query. The ID is interferring with the status search. The combobox RowSource includes ID as first column so it is the value being used. Remove the ID field from the RowSource sql and fix other properties or change the bound column or change the order of the fields in the sql.

    Could have one table for all status values.
    StatusCat (sales, client)
    StatusVal (hot, warm, cold, pending, etc.)

    What you should not do is use names as pk/fk. People tend to change their names. Also should not have full name in one field. There is inconsistency in entries, some are lastname first and others are firstname first. How will you do alphabetic sort on lastname? This is where the autonumber should be used as pk/fk. Call it CustomerNumber.

    BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as name. Instead of Mortgage? Other Notes, better would be MortgageOrOtherNotes.
    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.

  8. #23
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Hi June, the combobox suggestion didn't confuse me in itself. It was just that I didn't understand how to do it!

    Everything you have said makes sense. I will have a play with it and see if I can implement it all correctly. Muchas gracias!

  9. #24
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    It all seems to be working perfectly. Thank you!!

    Just one question, is there any reason why my table shows 397 records but in the form view (with query as recordset) I only see 395 in the box at the bottom?

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you referring to the Vendors table? The version I have shows 400 records and the last ID number is 407. IDs 1 thru 7 have been deleted. Could you be seeing something similar?
    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.

  11. #26
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Similar but I have created a query that shows 395 records and the table shows 397. I deleted 3 blank records from the table so 397 is right.

    I don't seem to be missing any though if that makes sense!

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If records have been deleted then the total number of records will not be equal to the ID number of the last record. Autonumber field automatically generates the ID when an entry is started in any field and if record is deleted or abandoned the number is lost and leaves gap in sequence. Unless there is some filtering in the query to exclude records, or an INNER join of tables might not show all records, this is the only reason I can see for the seeming discrepancy.
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search form criteria
    By atom in forum Forms
    Replies: 3
    Last Post: 04-03-2012, 06:42 AM
  2. search criteria help
    By putadokta in forum Access
    Replies: 2
    Last Post: 01-27-2012, 10:45 PM
  3. Search Criteria Error
    By hitesh_asrani_j in forum Forms
    Replies: 25
    Last Post: 09-29-2011, 10:35 AM
  4. Search Criteria
    By Meccer in forum Forms
    Replies: 1
    Last Post: 04-01-2011, 12:53 PM
  5. Search By Criteria - Flexible Criteria Fields
    By lilanngel in forum Access
    Replies: 0
    Last Post: 03-16-2011, 06:25 PM

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