Dear Michael Red & Friends, help please :-)

[dieselBREATH]

Hi guys...........again.

Same project, new challenge.

I have serveral fields in my data base that contain either a 0 or positive number. What I want to do is have the first field in my query display a preset result based on wether there is a positve # in several different fields in the data base. Here is an example in plain english.

criteria for field "A"
if field g is equal to 1 or above, then result = 10 bond
or if field h is equal to 1 or above, then result = 10 Mylr
or if field i is equal to 1 or above, then result = 5 color
or if field j is equal to 1 or above, then result = 4 rcplot

I hope this is a good explaination of what I want to do.

Michael: Tell me the name of at least two national chain restaurants in your area. I am sending you gift certificates.

With a grateful heart,

DieselBreath

"I love the smell of Diesel in the morning"

 

[THoey]

DB,

I think I understand. Try putting the following in your query:

IIF(g > 0, "10 bond",
IIF(h > 0,"10 Mylr",
IIF(i > 0,"5 color",
IIF(j > 0, "4 rcplot", "No Value&quot)))

I didn't know what value you wanted if all four were less than 1.
Terry M. Hoey

 

[dieselBREATH]

Well, that didn't work. I substituted my field, but you expression doesn't work. I can do one at a time, but Access stops me from trying to add another. something about the parenthesis and comas etc.

DB

 

[Databaseguy]

Did you enclose the name of your field in brackets ?? Also, it's wise to fully qualify the fieldname. I.E.

IIF([tblName].[fieldg] > 0, "10 bond",
IIF([tblName].[fieldh] > 0,"10 Mylr",
IIF([tblName].[fieldi] > 0,"5 color",
IIF([tblName].[fieldj] > 0, "4 rcplot", "No Value&quot)))

 

[dieselBREATH]

yes, because I used the expression builder and matched it with your expression. It looks like this when I am done:

IFF([SetAccounting]![LinealFeetBond]>0,"3-Bonds",
IFF([SetAccounting]![LinealFeetVellum]>0,"8-Vellum",
IFF(SetAccounting]![LinealFeetMylar]>0,"12-Mylar","No Value&quot))

When I run it, I get an error message that states:

"You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks."

I appreciate you help with this greatly!

DB

P.S. Did I mention that I am using Access 2000?

 

[Databaseguy]

IFF([SetAccounting]![LinealFeetBond]>0,"3-Bonds",
IFF([SetAccounting]![LinealFeetVellum]>0,"8-Vellum",
IFF([SetAccounting]![LinealFeetMylar]>0,"12-Mylar","No Value&quot))

 

[tlbroadbent]

How about using IIF instead of IFF. It should work. ;-) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[dieselBREATH]

Ok, I fixed that bracket thing and ran this:

IIF([SetAccounting]![LinealFeetBond]>0,"3-Bonds",
IIF([SetAccounting]![LinealFeetVellum]>0,"8-Vellum",
IIF([SetAccounting]![LinealFeetMylar]>0,"12-Mylar","No Value&quot))

Notice also that I chaned the IFF to IIF (Duhhh on my part).
This time the query ran without errors, however, it didn't work. It returned "3-Bonds" for every record regardless if the value in [LinealFeetBond] was 0.

DB

 

[CosmoKramer]

DB,

I tested the syntax you posted above, and it did "work" for me. By that I mean it returned the corresponding value for the first field that was > 0.

If you're looking for the last field in the order you specified that is > 0 (LinealFeetBond then LinealFeetVellum then LinealFeetMylar) simply reverse the order of the fields in your IIF statement and it will return the last one.

Bond Vellum Mylar returns
0 0 0 No Value
1 1 0 8-Vellum
0 1 1 12-Mylar

HTH

 

[MichaelRed]

Well, all (apparently) said and done w/ this. In a general sense, I try to avoid nesting 'stttuuuuuuuffffffffff' in the query grid. There are numerous alternatives. My 'history' usually inclines me toward a 'procedure' -(that means CODE). I this instance, a select case process in a procedure would be easy to 'read' and understand. It would also translate fairly easily to other SQL dialects.

dieselBREATH,

Red Lobster, Chi Chi's and Bennigans' are local.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over