Dates To show blank FIRST then rest of dates in Descending order

Hi I an creating a database with expiry dates in one column which the record will be sorted by. Currently I have the dates in descending order an example is shown below :
31/09/2019
27/06/2016
21/06/2016
21/05/2014
00/00/0000
00/00/0000
(the last two will show as empty cells because they have no expiry dates)

I would like the order as above but with the empty/blank cells at the top

I have conditional formatted them according to dates (green if no expiry or still in date within x amount of days , amber if running out within x amount of days, red if out of date ETC)

I have looked online and played about with vba but I still have no clue

Hello welcome to the forum.
As you have the various cells conditionally formatted, you can sort the column by cell colour with Green on top if these are the values with zero. Look under the Home tab Custom Sort/Filter for the various options.

Hope this helps.
DBY

Hi DBY

if I can do it by conditional formatting that would be great but I am not sure where I can do this on the sort options


any ideas??

See the attached screenshots. If you're using Excel 2010 then you should find these options.

Sort Options.jpg


Sort Options 2.jpg

I'm using access 2010 not excel for my database.....

I'm so sorry. I didn't notice you were referring to Access. I'll have a look at that to see if I can find a solution. I'll get back if I find anything.

Thank you so much!!

this is an example to what they look like


Untitled.png

if it doesn't show they come up as null values

Hello
I think it's possible to create a Query with a calculated field which will sort by Null values first and then Descending for the Expiry Date. For example the Calculated field would contain:

Formula:

Please Login or Register  to view this content.

The 1000000 figure being an arbitary number greater than your maximum date.

Query design:

Query Design.jpg

I am trying this but it seems I am unable to, is this because the data I am dealing with is in a subform on another form?

Hi
It could be. Can you not run the query off the original Data Table rather than the sub form?

I have tried to complete the query and it has just made lots of errors to my subform from the query/original table is there any other way???

Sorry at the moment I can't think of another way to do this. Using your sample data it works for me in both Queries and Forms. Not sure why it messed up your Sub Form/Table, the query shouldn't have changed your original Table, just merely sorted it differently on the Query datasheet view.

hi! thank you for trying with this problem I have now changed they way I have set out my form and filtered to appropriate things to make it easier for everyone! Thank you again

I have a new problem (typical )

which if anyone cant help with can be found from the link below.

http://www.excelforum.com/access-tab...ml#post4220546