Where Condition & Quotation Marks

M

Melissa

Please help! I'm very confused about using quotation marks in the where
condition of code. I'm trying to figure it out. Could somebody please let
me know what's wrong with the following line of code? All my database is
returning is when Status Is Null.

"Status < '5' & ""Or"" & Status Is Null"

What it should give me is all conditions where Status < 5 or where Status Is
Null.
 
J

Jim Burke in Novi

It depends on whether status is a numeric value or a text field. If it's
numeric you'd use

"Status < 5 Or Status Is Null"

if it's text it would be

"Status < ""5"" Or Status Is Null"

That's if you have the 5 hard-coded. If that value was coming from, say, a
recordset or a form textbox, etc, it would be

for numeric:

"Status < " & RecordsetOrTextboxValue & " Or Status Is Null"

for text value:

"Status < """ & RecordsetOrTextboxValue & """ Or Status Is Null"
 
R

ryguy7272

This is an excellent resource:
http://www.mvps.org/access/forms/frm0031.htm
http://www.mvps.org/access/queries/qry0001.htm

Also, IsNull is one word, not two words.

Finally, for a post a while back, "Me" is shorthand for the form or report
that the code is running on. If you have a form open called Form1 you can
refer to that form by the code Forms!Form1. However, if the code is on Form1,
you can shorten the previous to just Me. Me only works in VBA, it doesn't
work in the Control Source of calculated controls.

The "!" after the Me is just a separator between components making up the
path to the object being referred to.

HTH,
Ryan---
 
D

DStegon via AccessMonster.com

First of is '5' text??? You are turing the 5 into a text 5 and THEREFORE
cant eval the LESS THAN math. Also, you dont surround the OR with quotes
because you are essentially telling the where to LOOK for "or" in the field

open "Select Status, Status_ID from tbl_Status WHERE ((tbl_Status.Status<5)
OR (tbl_Status.Status Is Null));"

if you are trying to open and the where condition is a text then you would
want to double wrap the condition with quotes. YOu can also use single '

open "Select Status, Status_ID from tbl_Status WHERE ((tbl_Status.
Status=""Confirmed"") OR (tbl_Status.Status Is Null));"

or

open "Select Status, Status_ID from tbl_Status WHERE ((tbl_Status.
Status<>'Confirmed') OR (tbl_Status.Status Is Null));"
 
J

Jim Burke in Novi

In VBA code there is an IsNull function, e.g. IsNull(Status). If you code it
in SQL you use Is Null, e.g. Status Is Null.
 
Top