If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
My criteria doesn't work.
Hi,
What is wrong with both of following criteria: This one, Access tells me "The expression is to complex..." iif(forms!frmProduct!cmbPL="Totals", Is Null , like forms!frmProduct!cmbPL & "*") This one doesn't return any value, iif(forms!frmProduct!cmbPL="Totals", Null, like forms!frmProduct!cmbPL & "*") Thanks Lydia -- Access/VB Programmer |
#2
|
|||
|
|||
My criteria doesn't work.
Just to clarify, you want logic setup like: if cmbPL = "Totals", then
no criteria at all, else cmbPL & "*" if thats the case, try iif(cmbPL = "Totals", Like "*", Like cmbPL & "*") you will of course need your Forms!... prefix too |
#3
|
|||
|
|||
My criteria doesn't work.
Lydia wrote:
What is wrong with both of following criteria: This one, Access tells me "The expression is to complex..." iif(forms!frmProduct!cmbPL="Totals", Is Null , like forms!frmProduct!cmbPL & "*") This one doesn't return any value, iif(forms!frmProduct!cmbPL="Totals", Null, like forms!frmProduct!cmbPL & "*") The result of a function (IIf) can only be a value, not a partial criteria expression. Where are you using that kind of thing? I suspect you are trying to use them as criteria in the query design grid. If so try something more like: Like Forms!frmProduct!cmbPL & "*" OR Forms!frmProduct!cmbPL = "Totals" (all on one line) -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
My criteria doesn't work.
Thanks for you reply.
When the cmbPL="Totals", I want all records whose "PL" field value Is Null to show up, not instead of all records. Thanks. Lydia -- Access/VB Programmer " wrote: Just to clarify, you want logic setup like: if cmbPL = "Totals", then no criteria at all, else cmbPL & "*" if thats the case, try iif(cmbPL = "Totals", Like "*", Like cmbPL & "*") you will of course need your Forms!... prefix too |
#5
|
|||
|
|||
My criteria doesn't work.
maybe this?
iif(cmbPL = "Totals", "", Like cmbPL & "*") |
#6
|
|||
|
|||
My criteria doesn't work.
The criteria is in a query and for the field named PL. User select from a
drop down box on a form called frmProduct the value PL. If a user selects "Totals" from this drop down box list, I want it to return all values whose PL value is null. This is kind of unusual, however it is what I need to accomplish right now. Thanks. Lydia -- Access/VB Programmer "Marshall Barton" wrote: Lydia wrote: What is wrong with both of following criteria: This one, Access tells me "The expression is to complex..." iif(forms!frmProduct!cmbPL="Totals", Is Null , like forms!frmProduct!cmbPL & "*") This one doesn't return any value, iif(forms!frmProduct!cmbPL="Totals", Null, like forms!frmProduct!cmbPL & "*") The result of a function (IIf) can only be a value, not a partial criteria expression. Where are you using that kind of thing? I suspect you are trying to use them as criteria in the query design grid. If so try something more like: Like Forms!frmProduct!cmbPL & "*" OR Forms!frmProduct!cmbPL = "Totals" (all on one line) -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
My criteria doesn't work.
The where statement in the query would look something like the following.
WHERE PL like forms!frmProduct!cmbPL OR (forms!frmProduct!cmbPL="Total" AND PL is Null) In Query Design View, I think that would be entered as Field: PL Criteria: Like forms!frmProduct!cmbPL OR (forms!frmProduct!cmbPL="Total" and Is Null) Access will restructure that when you save the query. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Lydia wrote: The criteria is in a query and for the field named PL. User select from a drop down box on a form called frmProduct the value PL. If a user selects "Totals" from this drop down box list, I want it to return all values whose PL value is null. This is kind of unusual, however it is what I need to accomplish right now. Thanks. Lydia |
#8
|
|||
|
|||
My criteria doesn't work.
Yes! It worked.
Thanks. Lydia -- Access/VB Programmer "John Spencer" wrote: The where statement in the query would look something like the following. WHERE PL like forms!frmProduct!cmbPL OR (forms!frmProduct!cmbPL="Total" AND PL is Null) In Query Design View, I think that would be entered as Field: PL Criteria: Like forms!frmProduct!cmbPL OR (forms!frmProduct!cmbPL="Total" and Is Null) Access will restructure that when you save the query. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Lydia wrote: The criteria is in a query and for the field named PL. User select from a drop down box on a form called frmProduct the value PL. If a user selects "Totals" from this drop down box list, I want it to return all values whose PL value is null. This is kind of unusual, however it is what I need to accomplish right now. Thanks. Lydia |
Thread Tools | |
Display Modes | |
|
|