Like "*"

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Thanks for taking the time to read my question.

I have a query that has the following criteria

IIf([Forms]![frmReportGenerator]![lstSelectedList].
[listcount]=0,Like "*",In (SELECT DISTINCTROW
tblSelectedProductCodes.SelectedProductCode FROM
tblSelectedProductCodes))

Each portion of this code works on it's own, the
criteria, the true and the false.

Together they don't.

I have substituted a value for the Like "*" portion and
the code works.

I have a feeling it is the Like "*" portion.

Any suggestions?
 
Thanks for taking the time to read my question.

I have a query that has the following criteria

IIf([Forms]![frmReportGenerator]![lstSelectedList].
[listcount]=0,Like "*",In (SELECT DISTINCTROW
tblSelectedProductCodes.SelectedProductCode FROM
tblSelectedProductCodes))

Each portion of this code works on it's own, the
criteria, the true and the false.

Together they don't.

I have substituted a value for the Like "*" portion and
the code works.

I have a feeling it is the Like "*" portion.

Any suggestions?
Click to expand...

You cannot pass *operators* such as IN or LIKE using an IIF() function
- only the values to be searched. Try instead:

IN In (SELECT DISTINCTROW tblSelectedProductCodes.SelectedProductCode
FROM tblSelectedProductCodes)
OR
[Forms]![frmReportGenerator]![lstSelectedList].[listcount]=0

This may not work since the listcount property is a VBA property which
may not be accessible to JET. How is tblSelectedPropertyCodes being
filled? If the listbox being empty implies that the table will also be
empty, there is no need for the OR; the IN clause simply won't contain
any values.
 
-----Original Message-----
Thanks for taking the time to read my question.

I have a query that has the following criteria

IIf([Forms]![frmReportGenerator]![lstSelectedList].
[listcount]=0,Like "*",In (SELECT DISTINCTROW
tblSelectedProductCodes.SelectedProductCode FROM
tblSelectedProductCodes))

Each portion of this code works on it's own, the
criteria, the true and the false.

Together they don't.

I have substituted a value for the Like "*" portion and
the code works.

I have a feeling it is the Like "*" portion.

Any suggestions?
Click to expand...

You cannot pass *operators* such as IN or LIKE using an IIF() function
- only the values to be searched. Try instead:

IN In (SELECT DISTINCTROW tblSelectedProductCodes.SelectedProductCode
FROM tblSelectedProductCodes)
OR
[Forms]![frmReportGenerator]![lstSelectedList]. [listcount]=0

This may not work since the listcount property is a VBA property which
may not be accessible to JET. How is tblSelectedPropertyCodes being
filled? If the listbox being empty implies that the table will also be
empty, there is no need for the OR; the IN clause simply won't contain
any values.


.
Thanks for the reply John,
Click to expand...

What I did was put the statement in code and made it the
string criteria for a report. It works there. The only
mod I did was put double quotes around the true and false
statements. (See below)

Thanks again,

Brad

strCriteria = IIf([Forms]![frmReportGenerator]!
[lstSelectedList].[ListCount] =
0, "([qrytblPricingIngredientsPre].
[PricingIngredTextCode])
Like '*'", "([qrytblPricingIngredientsPre].
[PricingIngredTextCode]) In (SELECT DISTINCTROW
tblSelectedProductCodes.SelectedProductCode FROM
tblSelectedProductCodes)")

DoCmd.OpenReport "rptqrytblPricingIngredientsRpt",
acViewPreview, , strCriteria
 
Back
Top