How to do a search based on four values

[yogi564]

Hi all

Can somebody out there try to help me out. I have a form which needs to pull out the results based on four different ranges. For example the heading is called Easting and the first field name is called "e1" and second is called "e2" And the second heading of the field is called Northing and the field name is called "n1" and the next field is called "n2". What need to happen is when a user put a range from e1 and e2 and also between n1 and n2 all the records which fall within the ranges should be pull out.

I have got the sql quote for this but it doen't not like it at all. It comes up with "error converting numeric to float. I don't understand. here the code for it.


********
Code
*******
<CFQUERY NAME= &quot;GetSearch&quot; datasource=#MM_Connection1_DSN# username=#MM_Connection1_USERNAME# password=#MM_Connection1_PASSWORD#>
Select *
From T_SOIL_SITE
Where easting between '@e1' and '@e2'
and northing between '#n1#' and '#n2#'
Declare @e1 numeric (9),@e2 numeric (9),@n1 numeric (9),@n2 numeric (9)
set @e1 = '#e1#'
set @e2 = '#e2#'
set @n1 = '#n1#'
set @n2 = '#n2#'

</cfquery>

Thanks
from
yogi

 

[Guest_imported]

Simplify your query until it works, then add stuff back in gradually until it doesn't work. Debug each time that it doesn't work. Try this for starters using literal values:

Select *
From T_SOIL_SITE
Where easting between 'x' and 'y'
and northing between '#n1#' and '#n2#'

Are all the data types correct? Does it work?

I suspect that the data type conversion is being done where you write:
Declare @e1 numeric (9) ...
then you write:
set @e1 = '#e1#'

Problem: if @e1 is numeric, why are you trying to set it to a string?

 

[Guest_imported]

Explain the Declare clause. Is that database vendo-specific syntax?