473,323 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Search Form

I have a search form with 4 search boxes on it. it searches a query that I have set up. The query has a criteria set on it of: Like "*" & Forms!SearchForm!LastName & "*" and three other search criteria. The problem is that some search fields are not pulling every record. Can anybody help or suggest another way to do this. Thanks.
May 7 '16 #1
7 1049
zmbd
5,501 Expert Mod 4TB
We have to at least see the SQL behind things and perhaps any code.

For the Query's SQL
Open the query in design view, then in the bottom right-hand corner of the status bar (you may see "NUM LOCK" there) you should see three icons, one will say "SQL" - click on that icon.
If you do not see it there, then in the blank area next to your tables, right-click, choose "SQL View" in the quick-menu

The SQL will then be shown and you can cut and paste it here

This could be something as simple as missing quotes around a text-type-cast field value or a malformed string.

>>> SQL, as with all scripts, MUST be formatted using the [CODE/] format tool found in the post toolbar.

-Z
May 7 '16 #2
Here is the SQL

Expand|Select|Wrap|Line Numbers
  1. SELECT tblEmployees.LastName, tblEmployees.Billet
  2.    , tblEmployees.Program, tblEmployees.[Parent Company]
  3. FROM tblEmployees
  4. WHERE ((
  5.    (tblEmployees.LastName) 
  6.       Like "*" & [Forms]![frmSearch]![LastName] & "*") 
  7.    AND ((tblEmployees.Billet) 
  8.       Like "*" & [Forms]![frmSearch]![Billet] & "*")
  9.    AND ((tblEmployees.Program) 
  10.       Like "*" & [Forms]![frmSearch]![Program] & "*")
  11.    AND ((tblEmployees.[Parent Company]) 
  12.       Like "*" & [Forms]![frmSearch]![ParentCompany] & "*"));
May 7 '16 #3
zmbd
5,501 Expert Mod 4TB
Nothing really pops out in the SQL itself; however, using the AND criteria, all four form fields must have either a Null/Empty-String or matching values in the record in order for the record to be returned to the record-set.

1) Have you checked that the "missing" records meet all of the established criteria?
For example using my Name "ZMBD" then :
[lastname] must have a "Z" in it somewhere
AND
[Billet] must have an "M" in it somewhere
AND
[Program] must have a "B" in it somewhere
AND
[Company] must have a "D" in it somewhere

IF only one, or two, or three, of those conditions are met within a given record then the record is excluded. Of course, if any of the related controls are Null/Empty then the "*" in the Like will basically ignore the field as a condition.

2) With that in mind, have you tried not setting any values in the form to ensure that all of the records are returned (if that's reasonable, the ideal situation is that one should work with a small subset of the data in a development situation )? Then try setting only one criteria, two, etc... checking that the records returned are reasonable at each stage.

3) Just to clarify, the query is "stored", that is to say, in the Access Object Navigation pane where you can click on it to run and not being ran from within VBA? I had assumed this in my last post that this was a stored query. :)
May 8 '16 #4
jforbes
1,107 Expert 1GB
You will not get results when you leave one of your criteria fields blank. ZMBD, hits on this in point one. There are ANDs in between each of the conditions meaning all conditions need to be met to return the record. So if one of your fields is not filled in, then that criteria will resolve to false.

If you are expecting to fill in only what you have, like just one of the four possible fields and expect the results to be returned you'll need to do one of two things.

Either change the ANDs to ORs.

Or replace the criteria with something like this, so when the criteria is null, it will match all the records (I didn't syntax check this code, I just typed it in, so there could be errors):
Expand|Select|Wrap|Line Numbers
  1. WHERE
  2.    tblEmployees.LastName 
  3.       Like IIF(IsNull(forms!frmSearch!LastName ), "*", "*" & forms!frmSearch!LastName & "*") 
  4.     AND tblEmployees.Billet
  5.       Like IIF(IsNull(forms!frmSearch!Billet), "*", "*" & forms!frmSearch!Billet& "*")
  6.     AND ((tblEmployees.Program) 
  7.       Like IIF(IsNull(forms!frmSearch!Program), "*", "*" & forms!frmSearch!Program & "*")
  8.     AND ((tblEmployees.[Parent Company]) 
  9.       Like IIF(IsNull(forms!frmSearch!ParentCompany), "*", "*" & forms!frmSearch!ParentCompany & "*")
May 9 '16 #5
zmbd
5,501 Expert Mod 4TB
J, the blank field in the code OP has provide should default to the Like "*" equivalent - or at least it did when I double checked it in my test DB. :)
May 9 '16 #6
jforbes
1,107 Expert 1GB
I wouldn't trust throwing two wildcards together like that.

But then again, if it works then great.
May 9 '16 #7
zmbd
5,501 Expert Mod 4TB
I agree, two "*" wildcards back-to-back certainly isn't the ideal situation.
I think OP could look at the propagation of null discussion located here: What Is Null Post#2

Actually, OP should read that article given Op's form is relying on the null "value" to function.
May 9 '16 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: misschristalee | last post by:
I'm having a brain blockage day.... Scenario: Search Form with 6 text boxes Query has same six fields Each has this IIF: IIf(IsNull(!!),"",!!) with each dictating the correct text box of...
7
by: jim Bob | last post by:
Hi, This is probably very simple to do so if anyone can point me to the right place for reading, it would be much appreciated. I just want to build a very basic search form where i can enter a...
1
by: Eddie Holder | last post by:
Hi Guys, I hope that someone will be able to help I have a table containing data, lets say products. I have a form with a text box which serves as the criteria for a query to search the product...
31
by: DWolff | last post by:
I'm somewhat new to Access, and have no VB experience at all (except for modifying existing code where obvious). I built a data entry form that works fine. Typically, a client will call in and...
9
by: lightning | last post by:
Hi all, I'm not very conversant with the vocabulary of Access, so please ask for clarification if necessary... I am trying to build a search form that allows combinations of search terms. For...
6
by: KiwiGenie | last post by:
Hi..I am trying to make a search form. I am fairly new to access and could well be looking at it completely wrong. I have an unbound form with textboxes in the header for entering different search...
2
by: Mark | last post by:
Hi All, I am creating a music database for a friend and have run into a problem. Within the ALBUM table, I wanted to store the ARTIST_ID rather than the ARTIST_NAME. To do this, I intended to have...
8
by: munkee | last post by:
Hi everyone, I am using the following code adapted from Allen Browne: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few...
9
by: ittechguy | last post by:
I have a unbound form which contains no data. I need to populate the form and also 3 listboxes with data using a search form which contains several search boxes in the header of my form. My idea...
7
by: ittechguy | last post by:
I'm not sure if I have a SQL problem or a vba problem so if I've posted in the wrong section, I'm sorry. I have a search form which uses a union query as its RecordSource. I need to use a union...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.