RE: blank for criteria in query field

Yes I did get it working. Thank you. but I am still wondering if the like
outside of the iif will cause problems when forms!main!program is not null
meaning that that value will be used instead of like "*"

"Klatuu" wrote:

The IsNull is not really Access. It is VBA. You have to keep in mind that
Access has severail components. Anything in a Query goes to Jet, the
database engine. Since it is not necessary to use an mdb as the actual data
source (could be SQL Server, Oracle, Informix, Sybase,etc), what goes to the
database engine has to be distinguishable by SQL SQL does not understand the
VBA function IsNull and VBA will not understand the SQL statements Is Null or
Is Not Null (The VBA equivilant being Not IsNull)

The reason the Like needs to go first is because that is what is doing the
filtering. It will always be
Like <some comparison value>
The IIf then provides the value to the Like

That is how it works.

You did not say whether you got it working or not. Can you let me know if
the probelm is resolved or not?


"bindurajeesh" wrote:

Thank you so much. Even though this is in access it appears that isnull was
not functioning. Also the same criteria string was in an additional query
that the report query was pulling from. A further question though. When the
forms!main!program is not null won't the like in front of the iif cause
problems? I tried putting like inside the iif and it did not like it. Again
thanks and if you could respond to the additional above question regarding
when forms!main!program is not null that would be great.

"Klatuu" wrote:

Okay, I rechecked mine and it is different, because I use (All) in the combo,
so it does it like this
Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

Since IsNull is an Access function, it may be you need to use SQL syntax. Try

Like iif([forms]![main]![programs] IS Null,"*",[forms]![main]![programs])


"bindurajeesh" wrote:

Thank you very much for such quick responses. I have put a breakpoint in
code where forms!main!program is populated and me!program is passing null to
the forms!main!program. Still can not get it to work. Thank you for further
advice.

"Klatuu" wrote:

Then it is possible the control programs is not really null. It could be a
zero length string ""
Try this as a test

Like iif(isnull([forms]![main]![programs]) OR Trim[forms]![main]![programs]) = "","*",[forms]![main]![programs])

I know what I originally posted works, I took it from one of my queries that
does work.


"bindurajeesh" wrote:

Like iif(isnull([forms]![main]![programs]),"*",[forms]![main]![programs])
still gives me an unpopulated report. If I put like "*" in the query it
pulls many records

"Klatuu" wrote:

Like iif(isnull([forms]![main]![programs]),"*",[forms]![main]![programs])

"bindurajeesh" wrote:

I have
iif(isnull([forms]![main]![programs]),like "*",[forms]![main]![programs]) as
criteria in a query field. If I place a value in the field on the form the
report that uses the query with the above criteria works but if I put nothing
in the filed on the form i.e. null the report pulls nothing. How do I make
the query show all programs when the field on the form is blank.
.

Relevant Pages

  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Using SQL statement to append a recordset to a table
    ... To insert a value into the field Priority: ... > all failed mean while each time my report was working fine. ... Used the query> wizard "Find Unmatched Query Wizard" to create a query between these two> tables, changed to an append query, appending to the ... > table (looking at the sql view along the way). ...
    (microsoft.public.access.formscoding)
  • Re: Creating Access DB
    ... database and in the SQL database that your report needs. ... click the query tab. ...
    (microsoft.public.excel.programming)