Criteria Issue with multiple Option Class sorting.

G

Guest

I have created a form with multiple [frames] or Option class buttions,
however you want to call them. This form is linked to a subquery in the form
providing the info based on what option buttons are selected.

This is the problem:
I have been working on this for about 30 hours straight and cannot get past
this part. I have the [Line] sort working but, the other options in the same
query are not responding and they are setup the same way as the [Line] code.

See code below: sitting in the criteria of a query.

four sorting colunms > [Line] [CH] [LT-DT] and [Deltas]



Line: "1" is the option number and "11" in the number sorted by.
IIf([Forms]![Main_out]![Frame60] Like "1","11")
IIf([Forms]![Main_out]![Frame60] Like "2","12")
IIf([Forms]![Main_out]![Frame60] Like "3","13")
IIf([Forms]![Main_out]![Frame60] Like "4","14")
IIf([Forms]![Main_out]![Frame60] Like "5","31")
IIf([Forms]![Main_out]![Frame60] Like "6","32")
IIf([Forms]![Main_out]![Frame60] Like "7","33")
IIf([Forms]![Main_out]![Frame60] Like "8","34")
IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")
IIf([Forms]![Main_out]![Frame60] Like "11","33")
IIf([Forms]![Main_out]![Frame60] Like "11","34")
IIf([Forms]![Main_out]![Frame60] Like "13","31")
IIf([Forms]![Main_out]![Frame60] Like "13","32")
IIf([Forms]![Main_out]![Frame60] Like "13","33")
IIf([Forms]![Main_out]![Frame60] Like "13","34")
IIf([Forms]![Main_out]![Frame60] Like "13","14")
IIf([Forms]![Main_out]![Frame60] Like "14","11")
IIf([Forms]![Main_out]![Frame60] Like "14","12")
IIf([Forms]![Main_out]![Frame60] Like "14","13")
IIf([Forms]![Main_out]![Frame60] Like "15","31")
IIf([Forms]![Main_out]![Frame60] Like "15","32")
IIf([Forms]![Main_out]![Frame60] Like "15","33")
IIf([Forms]![Main_out]![Frame60] Like "15","34")
IIf([Forms]![Main_out]![Frame60] Like "15","14")
IIf([Forms]![Main_out]![Frame60] Like "15","11")
IIf([Forms]![Main_out]![Frame60] Like "15","12")
IIf([Forms]![Main_out]![Frame60] Like "15","13")



CH:
IIf([Channel] Like "5","ALL",
IIf([Channel] Like "1","CTO",
IIf([Channel] Like "2","B2B",
IIf([Channel] Like "3","Retail",
IIf([Channel] Like "4","PVT","ALL")))))

LT-DT:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","LT",
IIf([Channel] Like "2","DT",
"ALL")))

Delta:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","Delta",
IIf([Channel] Like "2","",
"ALL")))
 
M

Michel Walsh

Hi,

IIf([Forms]![Main_out]![Frame60] Like "1","11")


is illegal. iif requires 3 arguments, you got only two.



IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")


sounds strange. If is it like "11", do you want 31 or 32 ?


IIf([Channel] Like "5","ALL",
IIf([Channel] Like "1","CTO",
IIf([Channel] Like "2","B2B",
IIf([Channel] Like "3","Retail",
IIf([Channel] Like "4","PVT","ALL")))))


sounds strange. There is no wildcard, why do you use LIKE? using = seems
do be more standard, when there is not wildcard. Also, instead of multiple
iif, try a single SWITCH, or CHOOSE. Finally, if the list is long, use a
table and a JOIN, easier to maintain.



Hoping it may help,
Vanderghast, Access MVP


Limo said:
I have created a form with multiple [frames] or Option class buttions,
however you want to call them. This form is linked to a subquery in the
form
providing the info based on what option buttons are selected.

This is the problem:
I have been working on this for about 30 hours straight and cannot get
past
this part. I have the [Line] sort working but, the other options in the
same
query are not responding and they are setup the same way as the [Line]
code.

See code below: sitting in the criteria of a query.

four sorting colunms > [Line] [CH] [LT-DT] and [Deltas]



Line: "1" is the option number and "11" in the number sorted
by.
IIf([Forms]![Main_out]![Frame60] Like "1","11")
IIf([Forms]![Main_out]![Frame60] Like "2","12")
IIf([Forms]![Main_out]![Frame60] Like "3","13")
IIf([Forms]![Main_out]![Frame60] Like "4","14")
IIf([Forms]![Main_out]![Frame60] Like "5","31")
IIf([Forms]![Main_out]![Frame60] Like "6","32")
IIf([Forms]![Main_out]![Frame60] Like "7","33")
IIf([Forms]![Main_out]![Frame60] Like "8","34")
IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")
IIf([Forms]![Main_out]![Frame60] Like "11","33")
IIf([Forms]![Main_out]![Frame60] Like "11","34")
IIf([Forms]![Main_out]![Frame60] Like "13","31")
IIf([Forms]![Main_out]![Frame60] Like "13","32")
IIf([Forms]![Main_out]![Frame60] Like "13","33")
IIf([Forms]![Main_out]![Frame60] Like "13","34")
IIf([Forms]![Main_out]![Frame60] Like "13","14")
IIf([Forms]![Main_out]![Frame60] Like "14","11")
IIf([Forms]![Main_out]![Frame60] Like "14","12")
IIf([Forms]![Main_out]![Frame60] Like "14","13")
IIf([Forms]![Main_out]![Frame60] Like "15","31")
IIf([Forms]![Main_out]![Frame60] Like "15","32")
IIf([Forms]![Main_out]![Frame60] Like "15","33")
IIf([Forms]![Main_out]![Frame60] Like "15","34")
IIf([Forms]![Main_out]![Frame60] Like "15","14")
IIf([Forms]![Main_out]![Frame60] Like "15","11")
IIf([Forms]![Main_out]![Frame60] Like "15","12")
IIf([Forms]![Main_out]![Frame60] Like "15","13")



CH:
IIf([Channel] Like "5","ALL",
IIf([Channel] Like "1","CTO",
IIf([Channel] Like "2","B2B",
IIf([Channel] Like "3","Retail",
IIf([Channel] Like "4","PVT","ALL")))))

LT-DT:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","LT",
IIf([Channel] Like "2","DT",
"ALL")))

Delta:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","Delta",
IIf([Channel] Like "2","",
"ALL")))
Click to expand...
 
G

Guest

Michel,
Thank-you so much for responding.

It seem to work with only 2 arguments, but your right it should be 3.

next>
IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")
**** This control on the form when clicked (optionbutton 11) select number
31 and 32.

iif, try a single SWITCH, or CHOOSE. Finally, if the list is long, use a
table and a JOIN, easier to maintain.
**** Do you have any examples to help me understand a swtich or choose?

table and a join??? how so?

Michel Walsh said:
Hi,

IIf([Forms]![Main_out]![Frame60] Like "1","11")


is illegal. iif requires 3 arguments, you got only two.



IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")


sounds strange. If is it like "11", do you want 31 or 32 ?


IIf([Channel] Like "5","ALL",
IIf([Channel] Like "1","CTO",
IIf([Channel] Like "2","B2B",
IIf([Channel] Like "3","Retail",
IIf([Channel] Like "4","PVT","ALL")))))


sounds strange. There is no wildcard, why do you use LIKE? using = seems
do be more standard, when there is not wildcard. Also, instead of multiple
iif, try a single SWITCH, or CHOOSE. Finally, if the list is long, use a
table and a JOIN, easier to maintain.



Hoping it may help,
Vanderghast, Access MVP


Limo said:
I have created a form with multiple [frames] or Option class buttions,
however you want to call them. This form is linked to a subquery in the
form
providing the info based on what option buttons are selected.

This is the problem:
I have been working on this for about 30 hours straight and cannot get
past
this part. I have the [Line] sort working but, the other options in the
same
query are not responding and they are setup the same way as the [Line]
code.

See code below: sitting in the criteria of a query.

four sorting colunms > [Line] [CH] [LT-DT] and [Deltas]



Line: "1" is the option number and "11" in the number sorted
by.
IIf([Forms]![Main_out]![Frame60] Like "1","11")
IIf([Forms]![Main_out]![Frame60] Like "2","12")
IIf([Forms]![Main_out]![Frame60] Like "3","13")
IIf([Forms]![Main_out]![Frame60] Like "4","14")
IIf([Forms]![Main_out]![Frame60] Like "5","31")
IIf([Forms]![Main_out]![Frame60] Like "6","32")
IIf([Forms]![Main_out]![Frame60] Like "7","33")
IIf([Forms]![Main_out]![Frame60] Like "8","34")
IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")
IIf([Forms]![Main_out]![Frame60] Like "11","33")
IIf([Forms]![Main_out]![Frame60] Like "11","34")
IIf([Forms]![Main_out]![Frame60] Like "13","31")
IIf([Forms]![Main_out]![Frame60] Like "13","32")
IIf([Forms]![Main_out]![Frame60] Like "13","33")
IIf([Forms]![Main_out]![Frame60] Like "13","34")
IIf([Forms]![Main_out]![Frame60] Like "13","14")
IIf([Forms]![Main_out]![Frame60] Like "14","11")
IIf([Forms]![Main_out]![Frame60] Like "14","12")
IIf([Forms]![Main_out]![Frame60] Like "14","13")
IIf([Forms]![Main_out]![Frame60] Like "15","31")
IIf([Forms]![Main_out]![Frame60] Like "15","32")
IIf([Forms]![Main_out]![Frame60] Like "15","33")
IIf([Forms]![Main_out]![Frame60] Like "15","34")
IIf([Forms]![Main_out]![Frame60] Like "15","14")
IIf([Forms]![Main_out]![Frame60] Like "15","11")
IIf([Forms]![Main_out]![Frame60] Like "15","12")
IIf([Forms]![Main_out]![Frame60] Like "15","13")



CH:
IIf([Channel] Like "5","ALL",
IIf([Channel] Like "1","CTO",
IIf([Channel] Like "2","B2B",
IIf([Channel] Like "3","Retail",
IIf([Channel] Like "4","PVT","ALL")))))

LT-DT:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","LT",
IIf([Channel] Like "2","DT",
"ALL")))

Delta:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","Delta",
IIf([Channel] Like "2","",
"ALL")))
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,



Choose( param, "Öne", "Two", "Three", "Four")

Switch( param=1, "One", param=2, "Two", param=3, "Three", true, "None Of
There")


For a join, define a temp table:

Temp ' table name
This Become ' Fields name
1 One
2 Two
3 Three


Then, assuming the data to "lookup" is from myTable.This:

SELECT myTable.*, Nz( temp.Become, "None Of There")
FROM myTable LEFT JOIN temp
ON myTable.this = temp.this





Hoping it may help,
Vanderghast, Access MVP

Limo said:
Michel,
Thank-you so much for responding.

It seem to work with only 2 arguments, but your right it should be 3.

next>
IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")
**** This control on the form when clicked (optionbutton 11) select number
31 and 32.

iif, try a single SWITCH, or CHOOSE. Finally, if the list is long, use a
table and a JOIN, easier to maintain.
**** Do you have any examples to help me understand a swtich or choose?

table and a join??? how so?

Michel Walsh said:
Hi,

IIf([Forms]![Main_out]![Frame60] Like "1","11")


is illegal. iif requires 3 arguments, you got only two.



IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")


sounds strange. If is it like "11", do you want 31 or 32 ?


IIf([Channel] Like "5","ALL",
IIf([Channel] Like "1","CTO",
IIf([Channel] Like "2","B2B",
IIf([Channel] Like "3","Retail",
IIf([Channel] Like "4","PVT","ALL")))))


sounds strange. There is no wildcard, why do you use LIKE? using =
seems
do be more standard, when there is not wildcard. Also, instead of
multiple
iif, try a single SWITCH, or CHOOSE. Finally, if the list is long, use a
table and a JOIN, easier to maintain.



Hoping it may help,
Vanderghast, Access MVP


Limo said:
I have created a form with multiple [frames] or Option class buttions,
however you want to call them. This form is linked to a subquery in the
form
providing the info based on what option buttons are selected.

This is the problem:
I have been working on this for about 30 hours straight and cannot get
past
this part. I have the [Line] sort working but, the other options in the
same
query are not responding and they are setup the same way as the [Line]
code.

See code below: sitting in the criteria of a query.

four sorting colunms > [Line] [CH] [LT-DT] and [Deltas]



Line: "1" is the option number and "11" in the number
sorted
by.
IIf([Forms]![Main_out]![Frame60] Like "1","11")
IIf([Forms]![Main_out]![Frame60] Like "2","12")
IIf([Forms]![Main_out]![Frame60] Like "3","13")
IIf([Forms]![Main_out]![Frame60] Like "4","14")
IIf([Forms]![Main_out]![Frame60] Like "5","31")
IIf([Forms]![Main_out]![Frame60] Like "6","32")
IIf([Forms]![Main_out]![Frame60] Like "7","33")
IIf([Forms]![Main_out]![Frame60] Like "8","34")
IIf([Forms]![Main_out]![Frame60] Like "11","31")
IIf([Forms]![Main_out]![Frame60] Like "11","32")
IIf([Forms]![Main_out]![Frame60] Like "11","33")
IIf([Forms]![Main_out]![Frame60] Like "11","34")
IIf([Forms]![Main_out]![Frame60] Like "13","31")
IIf([Forms]![Main_out]![Frame60] Like "13","32")
IIf([Forms]![Main_out]![Frame60] Like "13","33")
IIf([Forms]![Main_out]![Frame60] Like "13","34")
IIf([Forms]![Main_out]![Frame60] Like "13","14")
IIf([Forms]![Main_out]![Frame60] Like "14","11")
IIf([Forms]![Main_out]![Frame60] Like "14","12")
IIf([Forms]![Main_out]![Frame60] Like "14","13")
IIf([Forms]![Main_out]![Frame60] Like "15","31")
IIf([Forms]![Main_out]![Frame60] Like "15","32")
IIf([Forms]![Main_out]![Frame60] Like "15","33")
IIf([Forms]![Main_out]![Frame60] Like "15","34")
IIf([Forms]![Main_out]![Frame60] Like "15","14")
IIf([Forms]![Main_out]![Frame60] Like "15","11")
IIf([Forms]![Main_out]![Frame60] Like "15","12")
IIf([Forms]![Main_out]![Frame60] Like "15","13")



CH:
IIf([Channel] Like "5","ALL",
IIf([Channel] Like "1","CTO",
IIf([Channel] Like "2","B2B",
IIf([Channel] Like "3","Retail",
IIf([Channel] Like "4","PVT","ALL")))))

LT-DT:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","LT",
IIf([Channel] Like "2","DT",
"ALL")))

Delta:
IIf([Channel] Like "3","ALL",
IIf([Channel] Like "1","Delta",
IIf([Channel] Like "2","",
"ALL")))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple IIf Statements Not Working 5
First value below zero in a column 1
Framed Option Group / Nested iif Question 3
Problem with Update Query 5
Query running sum - help again 2
Iif statments 4
you tried to assign the null value to a variable that is not avariant data type - MsAccess 2007 4
Trying to find Nulls in an IIF statement 12

Top