Have you tried:
...
ORDER BY DateAdd(MONTH,lease_length,Since)
???
Good luck,
Stephen
>
> 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_t
> alk or send a message to [EMAIL PROTECTED] with
> 'unsubscribe' in the body.
Stephen Hait <[EMAIL PROTECTED]>
------------------------------------------------------------------------------
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.
