Sorting on a calculated field

[Jon Harris]

Morning All,

I have a problem which i'm sure is easy to overcome but can't think how to do it for the life of me.

I have split from with a field using:

=DateDiff("d",[File Sent Date],Date())

which works perfectly in so far as telling me how many days have elapsed since the "File Sent Date" but now I try sort (A-Z) so I can look at the oldest job first etc and it won't allow me to. Googling it's because it's a calculated unbound field.

I tried making the field a calculated field but the expression builder doesn't accept DateDiff...

Any thoughts and easy fixes very much appreciated!

Many thanks

Jon.

 

[bob fitz]

You can sort by the calculated field in the query

Alternatively, you could apply a descending sort on [File Sent Date] which would give the same result as an ascending sort on the calculated field

 

[Jon Harris]

Hi Bob, thanks for the really quick response, much appreciated.

This is probably where I've made the schoolboy error, the DATEDIF is sitting in a Form not a query and being dim I can't see how to do that.

BUT your second solution is genius, lateral thinking at it's best!

Thanks again Bob.

 

[bob fitz]

the DATEDIF is sitting in a Form not a query and being dim I can't see how to do that.

Create a query based on the table you used for your form. You can use the wizard for this if you need to.
Open the query in design mode.
In a blank column, on the first line put:

DateCalc:DateDiff("d",[File Sent Date],Date())

In the third line down (sort) select "Ascending" from the dropdown list.

You could then use this as the "Record Source" of you form.

 

[Jon Harris]

That's a huge help, thanks again Bob, obvious really if you know these things but I've been thrown in at the deep end and trying to get my head around Access so apologies for the lack of basic understanding.

thanks again.

Jon.

 

[bob fitz]

That's a huge help, thanks again Bob, obvious really if you know these things but I've been thrown in at the deep end and trying to get my head around Access so apologies for the lack of basic understanding.

thanks again.

Jon.

Glad to be of some help.
No need for any apologies. We all start off knowing absolutely nothing.
Good luck with your project.