Like within IIf

[Guest]

I have areport based on a query. The rpeort is rquired by Product or for all
products. My pproblem is with the query criteria, in the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other criteria in
its place. - used a ProductCode and get the report for this product when I
select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA",[Forms]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

 

[Bob Quintal]

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

I have areport based on a query. The rpeort is rquired by Product
or for all products. My pproblem is with the query criteria, in
the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other
criteria in its place. - used a ProductCode and get the report
for this product when I select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA", [Form
s]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

2 options. 1) change the criteria to put the like outside the iif:
LIKE (iif([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","*",
[Forms]![frmPrintSelection]![FilterField])

2) remove the criteria from the query. Add it to the VB Code that
opens the report as the WhereClause parameter
stDocname = "myReport"
if [Forms]![frmPrintSelection]![FilterField]<>"<<ALL>>"
stWhereClause ="filtercolumn = """ & me.filterfield & """
end if

Docmd.OpenReport, stDocname,,,stWhereClause

 

[Guest]

Where/how are you using this code?

 

[Douglas J. Steele]

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

I have areport based on a query. The rpeort is rquired by Product
or for all products. My pproblem is with the query criteria, in
the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other
criteria in its place. - used a ProductCode and get the report
for this product when I select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA", [Form
s]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

2 options. 1) change the criteria to put the like outside the iif:
LIKE (iif([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","*",
[Forms]![frmPrintSelection]![FilterField])

2) remove the criteria from the query. Add it to the VB Code that
opens the report as the WhereClause parameter
stDocname = "myReport"
if [Forms]![frmPrintSelection]![FilterField]<>"<<ALL>>"
stWhereClause ="filtercolumn = """ & me.filterfield & """
end if

Docmd.OpenReport, stDocname,,,stWhereClause

A third option:

Change the criteria to
= [Forms]![frmPrintSelection]![FilterField]) OR
[Forms]![frmPrintSelection]![FilterField]="<<ALL>>"

 

[Bob Quintal]

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

Thank you. Both options work

Good, But you should use one or the other, not both at the same
time.

I personally prefer the second, because it does not tie the report
to the form. You can then use the same report from other forms, and
even the same query in other reports or forms.

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

I have areport based on a query. The rpeort is rquired by Product
or for all products. My pproblem is with the query criteria, in
the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other
criteria in its place. - used a ProductCode and get the report
for this product when I select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA", [Form
s]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

2 options. 1) change the criteria to put the like outside the iif:
LIKE (iif([Forms]![frmPrintSelection]![FilterField]

[Forms]![frmPrintSelection]![FilterField])

2) remove the criteria from the query. Add it to the VB Code that
opens the report as the WhereClause parameter
stDocname = "myReport"
if [Forms]![frmPrintSelection]![FilterField]<>"<<ALL>>"
stWhereClause ="filtercolumn = """ & me.filterfield & """
end if

Docmd.OpenReport, stDocname,,,stWhereClause

 

[Guest]

Thank you. Both options work

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

I have areport based on a query. The rpeort is rquired by Product
or for all products. My pproblem is with the query criteria, in
the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other
criteria in its place. - used a ProductCode and get the report
for this product when I select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA", [Form
s]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

2 options. 1) change the criteria to put the like outside the iif:
LIKE (iif([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","*",
[Forms]![frmPrintSelection]![FilterField])

2) remove the criteria from the query. Add it to the VB Code that
opens the report as the WhereClause parameter
stDocname = "myReport"
if [Forms]![frmPrintSelection]![FilterField]<>"<<ALL>>"
stWhereClause ="filtercolumn = """ & me.filterfield & """
end if

Docmd.OpenReport, stDocname,,,stWhereClause

 

[Guest]

A third option:

Change the criteria to
= [Forms]![frmPrintSelection]![FilterField]) OR
[Forms]![frmPrintSelection]![FilterField]="<<ALL>>"

Thank you, but this option asks for
[Forms]![frmPrintSelection]![FilterField] when I run the query.

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

I have areport based on a query. The rpeort is rquired by Product
or for all products. My pproblem is with the query criteria, in
the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other
criteria in its place. - used a ProductCode and get the report
for this product when I select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA", [Form
s]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

2 options. 1) change the criteria to put the like outside the iif:
LIKE (iif([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","*",
[Forms]![frmPrintSelection]![FilterField])

2) remove the criteria from the query. Add it to the VB Code that
opens the report as the WhereClause parameter
stDocname = "myReport"
if [Forms]![frmPrintSelection]![FilterField]<>"<<ALL>>"
stWhereClause ="filtercolumn = """ & me.filterfield & """
end if

Docmd.OpenReport, stDocname,,,stWhereClause

A third option:

Change the criteria to
= [Forms]![frmPrintSelection]![FilterField]) OR
[Forms]![frmPrintSelection]![FilterField]="<<ALL>>"

 

[Al Campagna]

Tom,
Try this criteria... against your ProductCode.
Like [Forms]![frmPrintSelection]![FilterField] & "*"
or
Like "*" & [Forms]![frmPrintSelection]![FilterField] & "*"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Guest]

Thnak you.

Both options retunr the right data when I select a product, but no data when
I select <<ALL>>.
The solution provided by Bob Quintall did the job.

Bob,
Just to make the most of your good advice, I am using both, but in sperate
queries,

Tom,
Try this criteria... against your ProductCode.
Like [Forms]![frmPrintSelection]![FilterField] & "*"
or
Like "*" & [Forms]![frmPrintSelection]![FilterField] & "*"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have areport based on a query. The rpeort is rquired by Product or for
all
products. My pproblem is with the query criteria, in the ProductCode
Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other criteria
in
its place. - used a ProductCode and get the report for this product when
I
select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA",[Forms]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

 

[Douglas J. Steele]

In that case, you either mistyped the name of the control on
frmPrintSelection in one or both places or else the form wasn't already
open.

An improvement on that would be

= [Forms]![frmPrintSelection]![FilterField]) OR
Nz([Forms]![frmPrintSelection]![FilterField], "<<ALL>>") ="<<ALL>>"

By doing that, you'll also get all rows if the user forgets to select
anything from the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A third option:

Change the criteria to
= [Forms]![frmPrintSelection]![FilterField]) OR
[Forms]![frmPrintSelection]![FilterField]="<<ALL>>"

Thank you, but this option asks for
[Forms]![frmPrintSelection]![FilterField] when I run the query.

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

I have areport based on a query. The rpeort is rquired by Product
or for all products. My pproblem is with the query criteria, in
the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other
criteria in its place. - used a ProductCode and get the report
for this product when I select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA",
[Form
s]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

2 options. 1) change the criteria to put the like outside the iif:
LIKE (iif([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","*",
[Forms]![frmPrintSelection]![FilterField])

2) remove the criteria from the query. Add it to the VB Code that
opens the report as the WhereClause parameter
stDocname = "myReport"
if [Forms]![frmPrintSelection]![FilterField]<>"<<ALL>>"
stWhereClause ="filtercolumn = """ & me.filterfield & """
end if

Docmd.OpenReport, stDocname,,,stWhereClause

A third option:

Change the criteria to
= [Forms]![frmPrintSelection]![FilterField]) OR
[Forms]![frmPrintSelection]![FilterField]="<<ALL>>"

 

[Guest]

Indeed, after a long battle with this one I mistyped the name of the form.
Thanks for your help and follow up.

In that case, you either mistyped the name of the control on
frmPrintSelection in one or both places or else the form wasn't already
open.

An improvement on that would be

= [Forms]![frmPrintSelection]![FilterField]) OR
Nz([Forms]![frmPrintSelection]![FilterField], "<<ALL>>") ="<<ALL>>"

By doing that, you'll also get all rows if the user forgets to select
anything from the combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A third option:

Change the criteria to
= [Forms]![frmPrintSelection]![FilterField]) OR
[Forms]![frmPrintSelection]![FilterField]="<<ALL>>"

Thank you, but this option asks for
[Forms]![frmPrintSelection]![FilterField] when I run the query.

=?Utf-8?B?VG9tIFZlbnRvdXJpcw==?=

I have areport based on a query. The rpeort is rquired by Product
or for all products. My pproblem is with the query criteria, in
the ProductCode Field:

IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>",Like
"*",[Forms]![frmPrintSelection]![FilterField])

FilterField is a ComboBox on a Union query.

No error message, just a blank report.

I have isolated the problem to my use of Like "*" by using other
criteria in its place. - used a ProductCode and get the report
for this product when I select <<ALL>>.

Example:
IIf([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","DZA",
[Form
s]![frmPrintSelection]![FilterField])

In this example I get the report for the DZA Product.

Any suggestions?
Thanks

2 options. 1) change the criteria to put the like outside the iif:
LIKE (iif([Forms]![frmPrintSelection]![FilterField]="<<ALL>>","*",
[Forms]![frmPrintSelection]![FilterField])

2) remove the criteria from the query. Add it to the VB Code that
opens the report as the WhereClause parameter
stDocname = "myReport"
if [Forms]![frmPrintSelection]![FilterField]<>"<<ALL>>"
stWhereClause ="filtercolumn = """ & me.filterfield & """
end if

Docmd.OpenReport, stDocname,,,stWhereClause

A third option:

Change the criteria to
= [Forms]![frmPrintSelection]![FilterField]) OR
[Forms]![frmPrintSelection]![FilterField]="<<ALL>>"