Select statement

[mana2]

Hi,

I have a simple query but it's not working:

#form.status#% is numeric and I'm wondering if that's it.
from tblEMP
<CFQUERY NAME=&quot;GetNumber&quot; DATASOURCE=&quot;Empdocs&quot;>
SELECT EmpStat

where EmpStat LIKE '%TP%'
AND EmpStat LIKE '%#form.status#%'
</CFQUERY>

<CFOUTPUT
query=&quot;GetNumber&quot;>
<p>The Status is: #EmpStat#
</cfoutput>

I tried hardcoding the value for #form.status#%' in the select statement and that worked. but I need to get the value from the form.

SO it would be:

<CFQUERY NAME=&quot;GetNumber&quot; DATASOURCE=&quot;Empdocs&quot;>
SELECT EmpStat
from tblEMP
where EmpStat LIKE '%TP%'
AND EmpStat LIKE '%55%'
</CFQUERY>

Thanks so much.

 

[DeZiner]

<CFQUERY NAME=&quot;GetNumber&quot; DATASOURCE=&quot;Empdocs&quot;>
SELECT EmpStat
from tblEMP
where EmpStat LIKE '%TP%'
AND EmpStat LIKE %55% <-- No quotes
</CFQUERY>
DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[MarcDePoe]

the problem is that &quot;like 55&quot; is impossible for the system to understand ... you must code this differently ...

Tell me exactly how you want the results to reflect &quot;like&quot; a particluar number. ie 10 more or 10 less than 55, numbers ending or starting with a 5, same double digit numbers, etc.

GL,
Marc

 

[mana2]

Hi,

I don't think I was very clear. All I'm really trying to do is find all records in the datbase that match a form field that is numeric. So for example, #form.status# = 55.

I want to find all records that contained 55 in them.
ABC-55-efg in them. It should work but nothing is found.

<CFQUERY NAME=&quot;GetNumber&quot; DATASOURCE=&quot;Empdocs&quot;>
SELECT EmpStat
where EmpStat LIKE '%TP%'
AND EmpStat LIKE '%#form.status#%'
</CFQUERY>

Thanks again.

 

[webmigit]

Change:

    where EmpStat LIKE '%TP%' 
    AND EmpStat LIKE '%#form.status#%'

    where EmpStat LIKE '%#form.status#%'

just see if that works...

If it pulls results, we'll go from there... Did I help?
Vote!

 

[SidianConsulting]

You are comparing the EmpStat field twice:

one with

LIKE'%TP%'

and the other with

LIKE '%#form.status#%'

but you are using AND where you should be using OR. Unless the EmpStat field must contain the characters &quot;TP&quot; and a specified numeric character or characters, us OR. Otherwise, something else is wrong. It doesn't matter if form.status is numeric or not, since form variables are not typed, whereas database fields are.

-Tek