Equals to and greater than

Life would be SOOOOO much simpler if you let people choose the height from a drop down list. The DISPLAY can say 5'11, but the VALUE would be 71. Then in the database you just store whole numbers.

Finally figured out why it wasnt working... it was missing a couple of concotanators and it was missing a from in one of the weights...'

thank you guys for all your help....
alot
perez

Turn on debugging and look at the sql being sent to access. Is it what you expected? Copy and paste it into an access query and see if you get the same answer.

What's the current problem?

You'll have to use the preservesinglequotes function if you create a variable like that to use in a cfquery tag.

I don't remember if access supports the BETWEEN sql command, but it might be a good candidate for this situation. The syntax is:

where value1 between min_value and max_value

explaination: this returns true when value1 lies between the value of min_value and max_value (inclusive).

Check out the following code as an example:

WHERE 1=1
<cfif Len(trim(Form.Height)) gt 0>
AND <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Form.Height#"> Between 502 AND 508
</cfif>

Explaination:

WHERE 1=1

this always returns true, basically just a place holder. If you have other SQL to put in the where statement, you would place it here.

AND <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Form.Height#"> BETWEEN 502 AND 508

Its always a good idea to use <cfqueryparam> when you are taking data entered by a user and using it in a query. It avoids possible SQL tampering and ensures that the data is going to be in the format that you expect.

perezn470 wrote:
> <input name="Height" type="text">


I am going to need you to provide some examples of how you expect users
to complete this form field; '506','504 and 510','5ft 2in' or 'Between 5
feet and 5 feet and 6 inches'?

etc.

In other words what do you expect users to input and how do you plan to
validate that they don't enter things like; '614', '2 meters', '187cm'
or 'moms apple pie'.

Thank you,
I did forget to add what i have on the search page itself... wasnt sure if that was correct... and i am doing it in Dreamweaver8. i also have a mathching input just on the other side of it...thank you, thank you, thank you, thank you.

i have all the other 8 search fields working fine...lol

perezn470 wrote:
> i almost have this finished and this is the last thing left... i am very new to
> CF8 and am coming along pretty good.
>
> i am stuck at one query.... i have a column in an access data base that is
> called Height that has it in like this 506 which means the person is 5ft 6in
> tall. i am looking to set up a query that will check for those that fall in
> between like Height =>: 502 and Height =<508 so that it gives me anyone that
> falls from 502 to 508. But i am not sure exactly where to place it yet, on the
> search page or the results page along with the WHERE statement?
>
> i am attaching the WHERE statement in case that helps. when you type in just
> on height option it passes it thru fine but i know i need to have that query
> that does the equal to and greater than and then the equal to and less than to
> make it select all those in between also...lol.
>
> Thank you in advance for any help you can give me...
>
>
> <cfset WhereClause = "0=0">
>
> <!--- Search by Height --->
> <cfif FORM.Height IS NOT "">
> <cfset WhereClause = WhereClause & " AND Height LIKE '%" & FORM.Height &
> "%'">
> </cfif>
>

This would be much easier if your data was a bit more normalized.
Mixing feet and inches into a single column like this is troublesome.

If this project is still open to design improvements I would just store
the height as inches i.e. 66 for 5ft 6inches. Then it is easy display
logic to show '66' inches as '5ft 6in' as well as a trivial where clause
of 'AND HEIGHT => 62 AND HEIGHT <= 68' OR 'AND HEIGHT BETWEEN 62 AND 68'
if your database management system supports the 'between' clause.

Working with what you have now you are going to have to build the type
of number that you have stored in the database. This will depend how
you are collecting the data from the user. Once the feet and inches
collected from the user are properly concatenated into a single number
the same as it is stored in the database then the above where clauses
should still work.

AND HEIGHT => '#ConcatHeight#' AND HEIGHT <= '#concatHeight#'

I believe that should still work even though you are mixing data types
and storing what should be an integer as strings. Luckily your data
should still return the way you desire.