I have a database with over 80 000 records in a table with about 15 fields. I am creating a form so the user can easily query the table without having to create custom queries.
The query form looks something like:
First Name: [TEXTBOX]
Last Name: [TEXT BOX]
Billing Number: [TEXTBOX]
etc.
[SEARCH BUTTON]
The [SEARCH BUTTON] triggers the query that looks at the main table and each field of that table using a the following Like statement:
(Under the first name filed)Criteria: Like [Forms]![frmFullSearch]![txtFirstName] & "*"
(Under the lastname field)Cirteria: Like [Forms]![frmFullSearch]![txtLastName] & "*"
The issue that I just noticed is, it doesn't pull all the results. For example I might put "Rob" in the FirstName text box and get 4 results with first name "Robert"....however, if I remove the Like statement and just use an exact look up ([Forms]![frmFullSearch]![txtFirstName]) and type "Robert" I get 50 results.
Am I missing something with the Like statement? Should I be setting up my query in a different way? Basically the goal is to allow the user to query a database using multiple text fields on a form and only having to enter partial entries (example "Rob" should also include "Robert" "Robbeh" etc).
This has been killing me for hours.
Robb