blank for criteria in query field

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

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

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.

 

[Guest]

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.

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

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

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.

 

[Guest]

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.

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.

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

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

:

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.

 

[Guest]

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])

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.

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.

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

:

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

:

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.

 

[Guest]

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.

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])

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.

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.


:

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

:

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

:

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.

 

[Guest]

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?

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.

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])

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.

:

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.


:

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

:

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

:

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.

 

[Guest]

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 "*"

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?

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.

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])


:

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.

:

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.


:

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

:

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

:

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.

 

[Guest]

It should work just fine. The final result if the control is null will be
Like *
If the control is not null, it will be Like <value in control>

Here is an exact copy/paste from one I did as an experiment to see if it
works, and it does:

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

It is essentially the same, except for the users' convenience i include
(All) in my combo, but the important part is the Like is first, then the Iif.
(please ignore the horrible naming, in production I always use Reddick
naming, but for experimenting, I am trying to save time)

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 "*"

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?

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.

:

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])


:

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.

:

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.


:

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

:

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

:

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.

 

[Guest]

Ok thanks you have been extremely helpful. I will see if this works on mine.
I have also discovered that a subquery has the same string in the criteria
of a field so I had to take out the isnull in that also. Thanks again this
is resolved as far as I can see for now.

It should work just fine. The final result if the control is null will be
Like *
If the control is not null, it will be Like <value in control>

Here is an exact copy/paste from one I did as an experiment to see if it
works, and it does:

Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

It is essentially the same, except for the users' convenience i include
(All) in my combo, but the important part is the Like is first, then the Iif.
(please ignore the horrible naming, in production I always use Reddick
naming, but for experimenting, I am trying to save time)

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 "*"

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?


:

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.

:

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])


:

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.

:

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.


:

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

:

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

:

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.