Not too sure what date functions are valid in Access, but you couldn't use an CF
functions within the expression if you expect it to be executed by the database.
How about just:
<cfquery name="getTenants" datasource="xyz">
SELECT ID, Since, Rate, Lease_length, sq_ft_leased, Name,
DateAdd(MONTH,lease_length,Since) AS sortable_date
FROM tenants
WHERE Property_ID=#TheRecord.ID#
ORDER BY sortable_date
</cfquery>
-----Original Message-----
From: Cyrill Vatomsky <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Monday, May 29, 2000 5:08 PM
Subject: RE: Tricky sorting riddle.
>
>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.
