Using Like SQL Command with numerec field type in Crystal Report(Resolved)

Hi
I am using the following SQL command to retrieve all the items records start with item number # 6 for the field ITEM NUMBER, the result is OK:

Code:

---

SELECT * FROM LIST_ITEMS WHERE LIST_ITEMS.[ITEM NUMBER]
Like '*6*' AND LIST_ITEMS.[ITEM Description] Like '**' ORDER BY
LIST_ITEMS.[ITEM NUMBER];

---

But when I use the following SQL for retrieving the same result in Crystal report, It does not work, like command seems will not work with numerical field type, it only accepts the equal sign:

Code:

---

{LIST_ITEMS.ITEM NUMBER} Like '*6*' And {LIST_ITEMS.ITEM
Description} Like "*"

---

Am I missing any parameters in the above code, appreciate your help

This might help

VB Code:

cast {LIST_ITEMS.ITEM NUMBER} as char Like '*6*' And {LIST_ITEMS.ITEM
Description} Like "*"

Thanks for your lights, I have posted this question in [email protected] and get semilar answer, I liked to posted here for reference:
Appreciate your reply.
------------
Mark
Thank you very much, I tried what you had suggested
and it works exactly as I need.

CStr({LIST_ITEMS.ITEM NUMBER}) Like '*6*' And
{PUNCH_LIST_ITEMS.ITEM Description} Like "*"

I even used the * and stil it is working.

Many Many Thanks to you and to others who participated
in this question.

--- maqmaq> wrote:
Mark
Thank you very much, I tried what you had suggested
and it works exactly as I need.

CStr({LIST_ITEMS.ITEM NUMBER}) Like '*6*' And
{PUNCH_LIST_ITEMS.ITEM Description} Like "*"

I even used the * and stil it is working.

Many Many Thanks to you and to others who participated
in this question.




--- maqmaq> wrote:
eg: from fields
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17...
you want to return just records 6 and 16 ?

That is correct...exactly what I am loking for.
I keep trying and hopefully end with success.
Appreciate your continous help


--- mark_the_starwatcher
<[email protected]> wrote:

Whoa... this seems weird.

If I understand you, you've got a numeric field
and
want to select
records based on the number having a "6" digit
anywhere in the field?

eg: from fields
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17...

you want to return just records 6 and 16 ?

If that's the case, you'll need to convert the
numeric field to a
string, and then use LIKE on the coverted string.
I don't think LIKE
will work on numerics.

Checkout the convert function in SQL Books on
Line.

Must say, if my understanding is correct, seems
like a very strange requirement.

> > Cheers,
> >
> > Mark