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
|
|||
|
|||
Query criteria
I would like set criteria based on a value from a form, such that if the
value from the form is "1" a specific recordset is returned and if the value is not "1" all records are returned. Can this be accomplished in a single criteria cell? e.g. I tried the following without success =IIf(Forms!Form1!Option=1,"Ohio", Like "*") |
#2
|
|||
|
|||
Query criteria
Assuming your field ALWAYS has data you can use
LIKE IIf(Forms!Form1!Option=1,"Ohio","*") Or if your field can contain nulls, you can force a value in it by appending a zero length string and using the first option Field: State: [TableName].[State] & "" Criteria: LIKE IIf(Forms!Form1!Option=1,"Ohio","*") If this is too slow, then there are other options available. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Jim Pockmire" wrote in message ... I would like set criteria based on a value from a form, such that if the value from the form is "1" a specific recordset is returned and if the value is not "1" all records are returned. Can this be accomplished in a single criteria cell? e.g. I tried the following without success =IIf(Forms!Form1!Option=1,"Ohio", Like "*") |
#3
|
|||
|
|||
Query criteria
I know the LIKE operator is slow - what are your thoughts regarding other
options? "John Spencer" wrote in message ... Assuming your field ALWAYS has data you can use LIKE IIf(Forms!Form1!Option=1,"Ohio","*") Or if your field can contain nulls, you can force a value in it by appending a zero length string and using the first option Field: State: [TableName].[State] & "" Criteria: LIKE IIf(Forms!Form1!Option=1,"Ohio","*") If this is too slow, then there are other options available. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . "Jim Pockmire" wrote in message ... I would like set criteria based on a value from a form, such that if the value from the form is "1" a specific recordset is returned and if the value is not "1" all records are returned. Can this be accomplished in a single criteria cell? e.g. I tried the following without success =IIf(Forms!Form1!Option=1,"Ohio", Like "*") |
#4
|
|||
|
|||
Query criteria
I've not found LIKE to be slow if no wild cards are involved. In that case,
it appears to have the same performance as equal. And I think the with just the "*" the performance would be just as fast as any other method. And I've tested with leading characters folllowed by just the asterisk and found it pretty quick there also. Like seems to use any indexes that exist when it can do so such as when searching for "ABC*" LIKE becomes slow when you give the SQL engine many options to check, such as LIKE "*ABC*" LIKE "*[A-F]" LIKE "[A-z][0-8]*32" If you want to avoid using likecompletely then try the following Field: [State] Criteria: = IIf(Forms!Form1!Option=1,"Ohio",Null) OR Forms!Form1!Option1 WARNING: When you save this Access will restructure the WHERE clause of the query. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Jim Pockmire" wrote in message ... I know the LIKE operator is slow - what are your thoughts regarding other options? "John Spencer" wrote in message ... Assuming your field ALWAYS has data you can use LIKE IIf(Forms!Form1!Option=1,"Ohio","*") Or if your field can contain nulls, you can force a value in it by appending a zero length string and using the first option Field: State: [TableName].[State] & "" Criteria: LIKE IIf(Forms!Form1!Option=1,"Ohio","*") If this is too slow, then there are other options available. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . "Jim Pockmire" wrote in message ... I would like set criteria based on a value from a form, such that if the value from the form is "1" a specific recordset is returned and if the value is not "1" all records are returned. Can this be accomplished in a single criteria cell? e.g. I tried the following without success =IIf(Forms!Form1!Option=1,"Ohio", Like "*") |
Thread Tools | |
Display Modes | |
|
|