Using OR in a query from a text box

[pbennison]

Hi clever people.

I wonder if you can help me
I have a form with a text box and a query that has a criteria or [FORMS]![ThisForm]![ThistextBox]

Is i enter say "apples" in the text box then the query shows be all records with the criteria of apples. If i enter "oranges" it shows me all records with a criteria of Oranges, however if i enter "Apples" OR "Oranges" it return nothing

Can anyone help we as to why i can use multiple items in a criteria.
I have tried IN("Apples","Oranges") too and nothing works

Really appreciate your help.

Its driving me mad !!! :banghead:

 

[jdraw]

Show us the actual sql.

 

[plog]

You can't do what you want, like you want. Your query is set up to match everything typed into that form input. You can't escape out of the value to make the query realize you have placed SQL in it.

Let's first explain escaping. When you said

If i enter "oranges"..

You didn't mean you typed those quote marks, you typed in 6 characters between them. But you used quotes to show what you typed. That's called escaping and a form of it is working in your SQL.

For my example, I'm going to use ~ as an escape symbol when I am writing my explanation. So when I say:

type in ~oranges~

you will know I just mean those 6 letters. It also means when I say:

type in ~"oranges"~

it means I want to include 8 characters -those quote marks are part of it because they fall between the squiggles.

Your WHERE clause in SQL is this:

WHERE [FruitField]=[FORMS]![ThisForm]![ThistextBox]

You come along and type ~apples~ your text box. In SQL resolves to this:

WHERE [FruitField]="apples"

SQL uses quote marks as an escape symbol. It marks the beginning and end of the value you want to use. So it takes what you typed, ~apples~ and places it in your WHERE clause surrounded by quotes.

So, when you type ~"apples" or "oranges"~ in your text box it becomes this:

WHERE [FruitField]=""apples" or "oranges""

There are 3 sets of quotes there. The outer ones define what value to look for in your table. The inner ones become part of the value you are looking for. You want the ~OR~ to mark the end of an input value and the beginning of SQL, but you can't do it with the method you want, because even though you are using the correct escape character you are still inside another set and you can't break out of that set from where you are.

 

[pbennison]

Hi Plog,

thanks for the very clear and concise explanation.
Is there any way of achieving this as in my very very newbie thoughts my only solution is creating another query with additional columns that allocate a value. Such as...

Apples and Pears, Apples and Bananas and Apples and Oranges and then putting a yes in each column if it matches one of the criteria ??

Gotta be a better way of doing this ??

Thoughts people ?

 

[jdraw]

I suggest you tell us clearly in simple English what you are trying to accomplish. There may be some options. You have started with a form and text box --and that is HOW you did something in Access. We still need to know WHAT you are trying to accomplish.

Plog has given you good info regarding SQL. There are some tutorials by Martin Green that deal with vba and SQL.

Here are some other links dealing with vba and SQL.
http://www.baldyweb.com/buildsql.htm
https://msdn.microsoft.com/en-us/library/dd627355(v=office.12).aspx
http://www.utteraccess.com/wiki/index.php/Dynamic_SQL

Learning vba and SQL is not trivial, but opens up many more solution options.

Good luck.

 

[pbennison]

OK, so forgetting fruit so a second, what i am doing is this..

I have a table which containing lots of different jobs. They are a few different types such as planned works, reactive work, contract work, Adhoc work.
I have started a database with this tables and people can run reports on it.
For example people might want to see how many contract jobs we have in a month or how many reactive jobs we have in a day etc.
What I am doing now is a report where people can look for a road, town, date range and then select what types of jobs they want in that report so they could chose Adhoc and Planned or Planned and Contract etc
When the button is pressed to generate this report it brings up a form which they can fill in. Everything else works like dates and towns etc but i couldn't figure out how to select different type of jobs.

Hope that makes sense

 

[jdraw]

In my previous post I suggested links about vba and sql. After seeing you latest post, I don't think your issue is sql/vba oriented.
In my view you do not have requirements -at least not complete requirements - to design your database.
Get a description of your business - start with the 30000 foot overview and gradually add detail to ensure all parts of the business are represented. Use the business description to build a data model. Get some test data to vet your model.
Here are a couple of examples from RogersAcessLibrary tutorials.

Problem Narrative:
Class Information Database


Roger Carlson's CIS 253 class wants to create a database to 
store information about the students in the class.  Information 
will include student demographic information, contact 
information and course information and history.


Information about the students will include name, address, city, 
state, zip, phone, email, fax, college major, Social Security 
Number, and gender.  Each student can have more than one 
phone number, email address or fax number.


Course Information includes course name, course number, 
number of credits, and grade received.  Each student will take 
many courses in their college career.  And naturally, each course
 will have many students enrolled.

Class tutorial

Problem Narrative:
CaterIt Database


Sue Johnson is starting a catering business.  She is looking for a
 program to help her maintain her business.   She wants to start 
by tracking customers and orders.  She will eventually want to 
add accounting features like accounts payable, accounts 
receivable, and inventory control, but wants to start small.


Customers are the people to whom she sells her catering 
services.  Customer information includes: Name, Address, 
Phone, Alternate Phone, Fax, and Email.  A customer may place 
many orders.

An Order is a group of items delivered as a single unit to a 
single Customer.  An Order contains the customer information,
 the order date, delivery instructions and an itemized list of the 
Items delivered.  An order applies to only one customer at a
 time, but each order can have many items on it.


Items are the individual items that appear on an order.  Each 
item has an associated item number, item description, quantity,
 price and extended price.  Each item can appear on many
 orders, and can appear on one or more orders.

Cater Tutorial

I recommend you work through 1 (or a few) of these tutorials to get a better understanding of database and table design.

Good luck with your project.