Null like "*" [Perameter] "*"

[Ryan Tisserand]

Like "*" & [Forms]![ICD9 Codes Lookup]![Keyword] & "*" Works great, but
shows every record untill a Keyword is entered and the search button is
clicked wich runs a me.requery. I cant get this to work, can anyone help.
IIf([Forms]![ICD9 Codes Lookup]![Keyword] is null, null, Like "*" &
[Forms]![ICD9 Codes Lookup]![Keyword] & "*"). Basicly I want no results
untill a value is in the unbound keyword textbox and the search button is
clicked.

 

[Amy Blankenship]

Like "*" & [Forms]![ICD9 Codes Lookup]![Keyword] & "*" Works great, but
shows every record untill a Keyword is entered and the search button is
clicked wich runs a me.requery. I cant get this to work, can anyone help.
IIf([Forms]![ICD9 Codes Lookup]![Keyword] is null, null, Like "*" &
[Forms]![ICD9 Codes Lookup]![Keyword] & "*"). Basicly I want no results
untill a value is in the unbound keyword textbox and the search button is
clicked.

LIKE "*" & IIF(([Forms]![ICD9 Codes Lookup]![Keyword] is null, "some value
that won't ever appear in your table", ([Forms]![ICD9 Codes
Lookup]![Keyword]) & "*"

Or possibly

LIKE IIF(([Forms]![ICD9 Codes Lookup]![Keyword] is null, Null, "*" &
([Forms]![ICD9 Codes Lookup]![Keyword] & "*")

I don't think you can concatenate null into a string.

 

[John Spencer]

Try the following where you enter a string that will never be entered into
the field (or at least the string is highly unlikely to ever be in the
field).

Like "*" & NZ([Forms]![ICD9 Codes Lookup]![Keyword], "234XXX::::")& "*")

Or you could also use

Not(IsNull([Forms]![ICD9 Codes Lookup]![Keyword])) AND Like "*" &
[Forms]![ICD9 Codes Lookup]![Keyword] & "*"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ryan Tisserand]

Thank you very much Jon
Like "*" & NZ([Forms]![ICD9 Codes Lookup]![Keyword], "234XXX::::")& "*"
worked like a charm. One last question though. I would like to count the
results which is easy, but if there are no results then I cant get anything
to show. I have tried is not null, >0, >"", but if there are no records I
cant get a value of zero to show. My reason for this is to give a count of
the results for the user to see, and if there are no results the answer to be
0, not just a blank space. Any advice?

Try the following where you enter a string that will never be entered into
the field (or at least the string is highly unlikely to ever be in the
field).

Like "*" & NZ([Forms]![ICD9 Codes Lookup]![Keyword], "234XXX::::")& "*")

Or you could also use

Not(IsNull([Forms]![ICD9 Codes Lookup]![Keyword])) AND Like "*" &
[Forms]![ICD9 Codes Lookup]![Keyword] & "*"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Like "*" & [Forms]![ICD9 Codes Lookup]![Keyword] & "*" Works great, but
shows every record untill a Keyword is entered and the search button is
clicked wich runs a me.requery. I cant get this to work, can anyone help.
IIf([Forms]![ICD9 Codes Lookup]![Keyword] is null, null, Like "*" &
[Forms]![ICD9 Codes Lookup]![Keyword] & "*"). Basicly I want no results
untill a value is in the unbound keyword textbox and the search button is
clicked.

 

[Amy Blankenship]

Thank you very much Jon
Like "*" & NZ([Forms]![ICD9 Codes Lookup]![Keyword], "234XXX::::")& "*"
worked like a charm. One last question though. I would like to count the
results which is easy, but if there are no results then I cant get
anything
to show. I have tried is not null, >0, >"", but if there are no records I
cant get a value of zero to show. My reason for this is to give a count
of
the results for the user to see, and if there are no results the answer to
be
0, not just a blank space. Any advice?

Use a UNION statement to add a 0 record if there are no records.