473,322 Members | 1,523 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,322 software developers and data experts.

Multiple Variable Criteria

I'm trying to create a form that has several text boxes for input by the user. I want to use the input as criteria for a query that will then display in a listbox on that same form.
I can get it to work, but i need to have the query use the criteria entered, and if some is left out, ignore it instead of seeing it as a Null value.

For example, I have inputs for First Name, Last Name, City, State

User inputs Last Name and City
The query should return all results by Last Name AND City

I can get it to work only on an OR basis.

Help!
Thank you.
Aug 8 '07 #1
3 1409
Rabbit
12,516 Expert Mod 8TB
In the criteria of all the fields you need to use Like "*" & Forms!frmName!ctlName & "*". You also need to Nz the fields in case any of them have Nulls.
Aug 8 '07 #2
In the criteria of all the fields you need to use Like "*" & Forms!frmName!ctlName & "*". You also need to Nz the fields in case any of them have Nulls.

I'm not familiar with how to use Nz. Where would I put that?
Aug 8 '07 #3
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. SELECT Nz(LastName, "")
  2. FROM Table1
  3. WHERE Nz(LastName, "") Like "*" & Forms!Form1!txtLastName & "*";
  4.  
Aug 8 '07 #4

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

Similar topics

1
by: KLAU | last post by:
I have a field that retrieves information from an expression in a query. I have used a DLookup function to get the calculated field from the query. However, the relationship is 1-to-many so one...
12
by: (Pete Cresswell) | last post by:
I know I can open many instances of a given form, but I've never done it. Now I'm analyzing an application where that seems like just the ticket: Many investment funds, *lots* of data points for...
1
by: EricV | last post by:
I have been looking for a solution to the following problem: I have a databases with multiple searchable fields, and what I want to do is to design a query which will enable me to enter various...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
4
by: carl.barrett | last post by:
Hi, I have a list box that displays 2 columns. Behind it sits a query with five columns. These are Column1 (DOB), column2 (a concatenated string of Surname Forname, Title), Column3 (Surname),...
0
by: Greg Strong | last post by:
Hello All, In the past I've used a combo box with the 'row source' being an Access SQL union query to select "All" or 1 for only 1 criteria in a query. An example is as follows: SELECT 0 As...
7
by: Aaron | last post by:
Complete code follows. I am new to .NET programming (and programming in general) and I am having a difficult time understanding how to fill a variable in one sub, and then access it from...
3
by: hmiller | last post by:
Hey everyone, I am having a hell of a time trying to set this menu system up. Here's what I'm trying to do. Combo Box One; is populated by names under properties "row source" "Phase 1"...
20
by: talktozee | last post by:
Hey, everyone! Basically, I need to insert *multiple rows* into table A from table B based upon some criteria, and I need to insert some static values along with each row from table A. For...
7
by: Ceebaby via AccessMonster.com | last post by:
Hi All Here's hoping someone can help me with this. I have a report based on a query where the criteria for 4 of the fields is set from an unbound form. I want the user to be able to select any...
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...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
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
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.