list all items in a range...

[Leakyradiator]

I am building a database of classic car parts and service providers. Some of these providers will only handle cars within a certain range of dates. For example, they sell gaskets for Fords, but only those made from 1948 to 1953. If a user enters 1950 in his search, I want these items to come up, since 1950 is within the range.

When initially entering the data into the database I want to use two form fields..one for starting date of the date range and one for ending date of date range. Nulls allowed if there's no date range requirement.

Any ideas how to go about setting this up?

Thanks.

 

[josel]

It sounds to me that you have 70% of the puzzle figured out and your question is based on how to apply a query that retrieves the needed information when applicable.

Say you have two fields in your table StartDate - EndDate.

Your query would look as follows:

<cfparam name=&quot;form.EndDate&quot; default=&quot;2020&quot;>

<cfquery name=&quot;GetParts&quot; .....>
select *
from AutoParts
<cfif isdefined(&quot;form.StartDate&quot>
<cfif #form.StartDate# IS NOT &quot;&quot;>
where StartDate GTE #form.StartDate# AND
EndDate LTE #form.EndDate#
</cfif>
</cfif>
</cfquery>

Of course, you will need to enhance above to suit your need but I hope it points you in the right direction.


Jose Lerebours KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[simmerdown]

But you'll need to use SQL operators for GTE and LTE. And your code must be able to handle a specified start date only, or a specified end date only, or both. Also you can simplify the <cfif>ing by using <cfparam>...

<cfparam name=&quot;form.StartDate&quot; default=&quot;&quot;>
<cfparam name=&quot;form.StartDate&quot; default=&quot;&quot;>

<cfquery name=&quot;GetParts&quot; datasource=&quot;#datasource#&quot;>
select *
from AutoParts
<cfif Len(Trim(form.StartDate)) and Len(Trim(form.EndDate))>
where StartDate >= #form.StartDate#
and EndDate <= #form.EndDate#
<cfelseif Len(Trim(form.StartDate))>
where StartDate >= #form.StartDate#
<cfelseif Len(Trim(form.StartDate))>
where EndDate <= #form.EndDate#
</cfif>
</cfquery>

 

[simmerdown]

oops, that should be

<cfparam name=&quot;form.StartDate&quot; default=&quot;&quot;>
<cfparam name=&quot;form.EndDate&quot; default=&quot;&quot;>

But you probably knew that.