Upsizing Access 97 to SQL server 6.5 Part IV
I have come across another problem when upsizing my database to SQL from Access. This one pertains to a select statement. This is the code I used to extract the last record in a table.
Code:
<cfquery datasource="DataSourceName" name="QueryName"> SELECT LAST(TableName.FieldName) AS LastOfFieldName FROM TableName </cfquery>
As you can see I used the Last function and this works fine in Access, but SQL server doesn’t recognise this statement and returns an error. So I had to use a slightly more convoluted way to get the last record.
Code:
<cfquery datasource="DataSourceName" name="QueryName" maxrows="1"> Select Tablename.FieldName From TableName Order by TableName.FieldName DESC </cfquery>
This query selects all the fields and orders them in descending order. You then use the maxrows= statement to limit the output to the first field it retrieves.