Followup to my own message :-)
>
> Hi All,
>
> I am building a real-estate DB and I have stumbled over an
> interesting riddle:
>
> In this case I use the following fields in Access DB (tenants)
>
> Property_ID -integer (related to an ID in Properties table)
> Name -Text,
> sq_ft -integer
> rate -currency
> lease_start -date,
> term -integer (months)
>
> At some point I want to output a partial list of tenants:
>
> <cfquery name="gettenants" datasource="xyz">
> SELECT *
> FROM tenants
> WHERE Property_ID=#Form.ID#
> <!--- where form.id is a ID of a property I list tenants for --->
> </cfquery>
>
> Based on existing data, I want to sort those tenants so that
> those with closest expiration dates are listed first.
>
> I thought of using a structure (name, CalculatedExpirationDate),
> but then I will still need to output other fields from the query,
> like rate and sq_ft.
>
> Is there any other way? Can such sorting be done within <cfquery> tag?
>
> Cyrill
I tried to execute this: (instead of #CreateODBCDate(now())# I also used
getdate() which should have worked with SQL server, but I am working with
Access and it says that getdate() is undefined)
<cfquery name="getTenants" datasource="xyz">
SELECT ID, Since, Rate, Lease_length, sq_ft_leased, Name,
Datediff(dd,#CreateODBCDate(now())#, DateAdd(MONTH,lease_length,Since)) AS
sortable_date
FROM tenants
where Property_ID=#TheRecord.ID#
Order by sortable_date
</cfquery>,
But I get this:
---------------
ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.
Hint: The cause of this error is usually that your query contains a
reference to a field which does not exist. You should verify that the fields
included in your query exist and that you have specified their names
correctly.
---------------
All names check out OK.
Bummer!
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
