another fieldname problem

[richaluft]

Hi:
Another Fieldname question:
Is there any way to construct an If--Then-Else clstatement as a query
Fieldname in order to set the value of the queryfield when opening the
query;
As an example, I'd like to use something like "Field10:If fldname(9)
=27, then Val(Field10) = 1G, else if Fldname(9) = 36 then Val(field10)
=1P, elseif--------"
Hope the question is clear, and I further hope that this is possible
to do.
(No, there are too many choices to use only the IIF function)
Thanks, Richard

 

[KARL DEWEY]

In the query design view grid use this ---
Field10: IIF([fldname(9)] =27, "1G", IIF([Fldname(9)] = 36, "1P", "4R"))

Your use of '1G' and '1P' seems like Excell references. I made them into
text.

 

[Conan Kelly]

richaluft:

Field10: iff(fldname(9)=27,"1G,iif(Fldname(9)=36,"1P","---------"))

HTH,

Conan

 

[John W. Vinson]

Hi:
Another Fieldname question:
Is there any way to construct an If--Then-Else clstatement as a query
Fieldname in order to set the value of the queryfield when opening the
query;
As an example, I'd like to use something like "Field10:If fldname(9)
=27, then Val(Field10) = 1G, else if Fldname(9) = 36 then Val(field10)
=1P, elseif--------"
Hope the question is clear, and I further hope that this is possible
to do.
(No, there are too many choices to use only the IIF function)
Thanks, Richard

No, it isn't clear at all. What's the structure of your table? Are you trying
to have a single criterion apply to multiple different fields conditionally
upon some other field? If so you will need to construct a SQL query in VBA
code - or (probably much better) normalize your data structure so that there's
only one place to look for data.

John W. Vinson [MVP]

 

[richaluft]

No, it isn't clear at all. What's the structure of your table? Are you trying
to have a single criterion apply to multiple different fields conditionally
upon some other field? If so you will need to construct a SQL query in VBA
code - or (probably much better) normalize your data structure so that there's
only one place to look for data.

John W. Vinson [MVP]

Conan & Karl's answers look like they're on the money. I didn't
realize that IIf's can be strung together like that.
No, 1G & 1P are not excel fields, but actial ID types in the
requirements for this Db.
As far as normalizing data, Unfortuneately the Db was initially
designed for a very specific order entry form. Now the demands are
for computerized orderentry, with all sorts of previously non-existent
requirements.
Before redesigning all tables and adding necessary fields so that
things function properly, I'm doing a "quick-fix" to get all necessary
data into a printout file. Thus the confusion!
Richard

 

[KARL DEWEY]

If the nesting of the IIFs get too deep then you are better off using a
translation table.