Where Condition & Quotation Marks

  • Thread starter Thread starter Melissa
  • Start date Start date
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.
 
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"
 
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---
 
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.
 
Back
Top