473,324 Members | 2,400 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,324 software developers and data experts.

Access Search Form

Hi Everyone, I hope you can help. I currently have an Access Database that manages contacts for sales. I have a form (split view) set up which basically calls a query to search function based on criteria in four different columns. Below is the SQL code. What i need it to do is this. All of the sales people are listed in a combo box. If none are selected, the query needs to return all values in the tables (unless other searches are used) If only the sales person is selected, it needs to bring up all records only for that person. The other boxes need to filter based on the value entered, but also include items that have a null value. What am I doing wrong?

Expand|Select|Wrap|Line Numbers
  1. WHERE (((Contacts.SalesRep) Like IIf([Forms]![ContactsForm].[cmbosalesrep]="","*",[Forms]![ContactsForm].[cmbosalesrep])))
  2.   AND  (((Contacts.LastName) Is Null)) OR (((Contacts.LastName) Like IIf([Forms]![ContactsForm].[txtlnsearch]="","*","*" & [Forms]![ContactsForm].[txtlnsearch] & "*"))
  3.   AND  ((Contacts.CompanyName) Is Null)) OR (((Contacts.CompanyName) Like IIf([Forms]![ContactsForm].[txtcsearch]="","*","*" & [Forms]![ContactsForm].[txtcsearch] & "*"))
  4.   AND  ((Contacts.FollowupDate) Is Null)) OR (((Contacts.FollowupDate) Like IIf([Forms]![ContactsForm].[txtfupsearch]="","*","*" & [Forms]![ContactsForm].[txtfupsearch] & "*")))
Oct 6 '11 #1

✓ answered by NeoPa

In that case we have a clear description of the logic. Try out this code and extend it if it works :

Expand|Select|Wrap|Line Numbers
  1. WHERE (Contacts.SalesRep Like Nz([Forms]![ContactsForm].[cmbosalesrep], '*'))
  2.   AND ((Contacts.LastName Is Null) OR (Contacts.LastName Like '*' & Nz([Forms]![ContactsForm].[txtlnsearch], '') & '*'))

7 1312
NeoPa
32,556 Expert Mod 16PB
Jason:
What am I doing wrong?
Good question. Apart from the messiness of the SQL of course, but that doesn't stop the code working. It just makes getting it to work so much harder.

Shall we start by your telling us what is the current problem with the existing code. Does it work? If it fails, how do you recognise that?

As some general advice, it is a good approach to start simple then build on this base when you have got the concepts understood. For now though, the requirement is pretty clear (Good for a first-time poster), but what's wrong isn't so much so.
Oct 6 '11 #2
Sorry about not posting more. With the current code in place, it brings up all records in the table, including records that do not match the criteria selected. Prior to the addition of the Is Null criteria, the search would only bring up records that had something in each cell which was being searched. I suppose I could just make the columns a default value, but I feel that isn't the best way to handle this. If you have a better way to do so, I am open to any suggestions.
Oct 10 '11 #3
NeoPa
32,556 Expert Mod 16PB
I'd go back to my earlier advice. Break it down into composite parts and test each one individually before putting them together.

Some things to understand about fields in Access tables and how to work at building up a WHERE clause :
  1. Fields that have nothing in are generally (by default) Null rather than empty strings ("").
  2. Field properties can be set to allow empty strings, but unless you have those changes in place, you should be checking for Nulls rather than empty strings.
  3. Form controls, on the other hand, always have a value of Null when empty.
  4. Nz() is a very useful function that converts a Null value to whatever you choose. This can reduce the number of tests required.

Expand|Select|Wrap|Line Numbers
  1. WHERE (Contacts.SalesRep Like Nz([Forms]![ContactsForm].[cmbosalesrep], '*'))
I was going to show an example of some of the other lines (individually) too, but I realise even now I'm not sure exactly what you want from them. I suspect you may not even have it clear in your head either. This is very important (as so many trip up over exactly what is required). I suggest you post it in precise English first in such a way that the logic is absolutely clear and stated. This will mean I probably won't have to help any further, as generally once that stage's been reached it becomes a lot simpler (but I'll be here in case of course).
Oct 10 '11 #4
Let me see if I can lay it out as clear as possible.

I have a form with three text boxes and a combo box. The combo box lists the names of our sales reps. If a name is selected and the search button is pressed, it should bring up just that persons sales (this is filled as entered, no null values). If no choice is selected, all table entries should be brought up into the split view unless restricted by one of the text boxes. The text boxes allow the user to enter last name, company name, or a follow up date. If the combo box is selected and a value is entered into one or more of the text boxes, anything that matches(or is close, ie *value*) the entered values should be brought up in the split view. At the same time, any null values should be brought up as well. Obviously if one of the boxes is left empty, the function should bring up anything that fits the criteria of the other search boxes.

So as simply as possible, the user enters data into one or all search options, mashes the button, and anything that matches the criteria selected comes up in my split view.
Oct 10 '11 #5
NeoPa
32,556 Expert Mod 16PB
In that case we have a clear description of the logic. Try out this code and extend it if it works :

Expand|Select|Wrap|Line Numbers
  1. WHERE (Contacts.SalesRep Like Nz([Forms]![ContactsForm].[cmbosalesrep], '*'))
  2.   AND ((Contacts.LastName Is Null) OR (Contacts.LastName Like '*' & Nz([Forms]![ContactsForm].[txtlnsearch], '') & '*'))
Oct 10 '11 #6
Thanks a lot! That works perfectly!
Oct 10 '11 #7
NeoPa
32,556 Expert Mod 16PB
Pleased to hear it Jason, and you're welcome of course.

Using functions in a WHERE clause may not be the most efficient approach for SQL, but it can be easier to develop, and the difference is negligible in most Access scenarios. Filtering queries are only noticeably delayed if of gargantuan proportions.
Oct 10 '11 #8

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

Similar topics

6
by: Jules | last post by:
Hi: I have an Access 97 Search form set up - a couple of combo boxes, a couple of text fields and a command button. I want the command button to run an SQL script and then open the results form....
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...
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...
1
by: scgamecock | last post by:
I am trying to build a search form that will display the data if the data exist and if the disposal check box is checked. I would like for an error message to display if the data exist, but the...
1
by: tamoochin | last post by:
I have a form that registers the user with my website, the form is in farsi language and must use utf-8 standard. I can store data in MS Access and also read it back with any problems. the...
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...
6
by: mercout | last post by:
Hey, I've been trying to create a search form in access for a while now, searching through books and emails. I have the search form set up with 11 combo box's, 3 text box's, a view button, and a...
9
f430
by: f430 | last post by:
i have been trying to write a search code for a similar database, and i followed all the steps that were provided above, and my code was close to what lightning had but i have added date range in my...
0
f430
by: f430 | last post by:
hi, i have a master table with all my part information, and it has columns with part number, date, defects,... and i am trying to write a code for my search form. i have created a search form on...
1
by: lorax | last post by:
I am new to this. I am recreating a form from ACCESS 2000 in Access 2010. The problem is that ACCESS no longer allow one to close a form without saving it. Form: a search form that allows people...
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: 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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.