I use order by in the query like this

ORDER BY IIF(ISNULL(SHOPDWGDETAILS.DATE_SUBMITTED),SHOPDWGD ETAILS.DATE_RETURNED,SHOPDWGDETAILS.DATE_SUBMITTED )


when query run it retreive/display the record like this

date_submitted..date_return...status
------------------------------------
.................12-12-2005.....AAN
.................27-08-2005.....AAN
23-08-2005.........................
10-12-2005.........................
...

it should be display like this.

date_submitted..date_return...status
------------------------------------
23-08-2005.........................
.................27-08-2005.....AAN
10-12-2005.........................
.................12-12-2005.....AAN
...

this is shopdrawings in and out query order by. ie
first drawing submitt and after that necessary action
drawing return to contractor. I have to see the status.
when drawing submit status nill and after return status will
AAN (APPROVED AS NOTED) ect.

how use the date submitted and date return in order by
IIF clause with ISNULL ?

I also save the requery, some other properties will change?

Mateen

You may wish to rethink your approach. It looks like you are trying to change the sort order each record which I doubt is possible. How about creating a new field with your IIF and sort on that?

HTH RuralGuy (RG for short) acXP WinXP Pro

You cannot use ORDER BY in that manner. What you do is create a calculated field in your query called, say

dtmSortDate: Nz(SHOPDWGDETAILS.DATE_SUBMITTED, SHOPDWGDETAILS.DATE_RETURNED)

Then use ORDER BY dtmSortDate in your SQL statement.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

thanks for response.

problem in access report.
when report run it should retrieve records like this

date_submitted..date_return...status
------------------------------------
23-08-2005.........................
.................27-08-2005.....AAN
10-12-2005.........................
.................12-12-2005.....AAN
.......
...


when I run same query it retrieve the records as
I want. but same query run in report it not properly
display the records as above why ?
some other report properties will chnage?


order by clause working in query, but order by clause
don't work in report why ?

regards

Mateen