SQL and 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,([qrytblPricingIngredientsPre].
[PricingIngredTextCode]) Like '*',
([qrytblPricingIngredientsPre].[PricingIngredTextCode])
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?
 
Are you saying that you have that pasted into the Criteria cell of the query
grid, or that it's in your SQL?

AFAIK, it won't work in the former case.

If it's in your SQL, though, what are you using to run the query: DAO or
ADO? The ADO wildcard character is %, not *.
 
Thanks for your reply Doug.

I had it on the criteria line in the query design view.

What I've done to get around the problem is used the same
iff statement and pasted it into the code. I assigned it
to the string criteria when opening the report. The only
mod I did to the statement was put quotes around the true
and false portions. (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

-----Original Message-----
Are you saying that you have that pasted into the Criteria cell of the query
grid, or that it's in your SQL?

AFAIK, it won't work in the former case.

If it's in your SQL, though, what are you using to run the query: DAO or
ADO? The ADO wildcard character is %, not *.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Thanks for taking the time to read my question.

I have a query that has the following criteria

IIf([Forms]![frmReportGenerator]![lstSelectedList].
[listcount]=0,([qrytblPricingIngredientsPre].
[PricingIngredTextCode]) Like '*',
([qrytblPricingIngredientsPre].[PricingIngredTextCode])
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...


.
Click to expand...
 
Just curious -- what does AFAIK mean?

Victor

Douglas J. Steele said:
Are you saying that you have that pasted into the Criteria cell of the query
grid, or that it's in your SQL?

AFAIK, it won't work in the former case.

If it's in your SQL, though, what are you using to run the query: DAO or
ADO? The ADO wildcard character is %, not *.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Brad said:
Thanks for taking the time to read my question.

I have a query that has the following criteria

IIf([Forms]![frmReportGenerator]![lstSelectedList].
[listcount]=0,([qrytblPricingIngredientsPre].
[PricingIngredTextCode]) Like '*',
([qrytblPricingIngredientsPre].[PricingIngredTextCode])
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...
Click to expand...
 
Back
Top