IIF in Query, more output then I want

[Thomas Hirschmann]

I'm trying to compile a list of purchases done between 0906 and 0911

M1: IIf([Purch]>"0906" And [Purch]<"0911",[ Purch],"")

The query works but instead of ignoring purchases that that don't fall into the above range it juts outputs a blank field.

What am I doing wrong?

Many thanks. Appreciate your help.
Tom




EggHeadCafe - Software Developer Portal of Choice
A Wrapper for the Dispatcher class of Threading Namespace to manage thread items
http://www.eggheadcafe.com/tutorial...f-2f01a383cff6/a-wrapper-for-the-dispatc.aspx

 

[kc-mass]

Add crtiteria as: <> ""

 

[Tom van Stiphout]

On Thu, 05 Nov 2009 23:11:28 -0800, Thomas Hirschmann wrote:

I am assuming "0906" Is shorthand for Jun-2009
You need to use a where-clause like this:
select * from myTable
where Purch > #06/01/2009# and Purch < #09/01/2009#

My query assumes that Purch is a date field, and date values need to
be wrapped in #-signs. Also use full dates; Access does not know what
kind of date "0906" is.
If Purch is a text field, you better have a REALLY GOOD reason why
you're not using a date field. In that case you should use
single-quotes around the text values.

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

I'm trying to compile a list of purchases done between 0906 and 0911

M1: IIf([Purch]>"0906" And [Purch]<"0911",[ Purch],"")

The query works but instead of ignoring purchases that that don't fall into the above range it juts outputs a blank field.

What am I doing wrong?

Using IIF.

It's doing exactly what you're asking it to: if the (text string??) Purch is
between "0906" and "0911", it's displaying it; if it isn't, it's displaying
"".

What's the context? What's the rest of the query? I suspect you don't need an
IIF at all, but instead a criterion on the Purch field.

I also share Tom's concerns about this field. If it's a date, or meant to be
a date, you're digging yourself a hole that will be hard to get out of!

 

[Thomas Hirschmann]

I really appreciate your time to help.

Basically the query works like a charm. The problem is that when I look at the "Data Sheet View" it

a) outputs the data I'm looking for BUT

b) I get tons of blank lines.

I'm not sure what I'm overlooking in the formula here. Maybe it?s the ,"") at the end? Is there some command to say "ignore" other records?

Have a great Sunday
Tom




kc-mass wrote:

Add crtiteria as: <> ""<Thomas Hirschmann> wrote in message
06-Nov-09

Add crtiteria as: <> ""
<Thomas Hirschmann> wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET AJAX on Microsoft CDN
http://www.eggheadcafe.com/tutorial...ca-b9d5d1348f25/aspnet-ajax-on-microsoft.aspx

 

[John Spencer]

Try entering criteria under your field.

Field: Purch
Table: [Your table name]
Criteria: >"0906" And <"0911"

That should return the records where the calculation is not blank.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County