Calculated Fields - Need to update form & Table

[Bh89]

Hi there,

I have a table; a query and a form.
I need to work out how long since an order was placed using the following formula Weeks Since Order : DateDiff("ww",[Ref Date],Now())


Now I have managed to put this formula on my form but I cant sort (from largest to smallest etc) when looking at the form in datasheet view.
I then put this calculation on the query (select query) but it does not update the table or the form (meaning when a new record is entered, the 'weeks since order' figure is not updated).

any help would be appreciated. I am loosing patience with this; I am not very good at access so would appreciated simple instructions!

Thank you,

[Micron]

Do the sorting in the query if it will be static. If not, provide a means (e.g. option buttons in a frame, command button, etc.) to cycle the sort. This you would handle in code by altering the form rowsource by changing the sort only, then requerying the form. If that sounds like a possibility, you should be able to get help here.

As for not updating, [Ref Date] is a field in the query? If so, try modifying dates in the query itself. If the calculation changes, you've done that part correctly. Not exactly sure what process you're referring to. You are not trying to save the calculation in a table through a select query, are you? Even if you could, it is very rare that you should be storing calculated values anyway, so I suspect that how to achieve this is kind of a moot point.

[Bh89]

Hi there,

Thank you for your speedy reply.

I would like to be able to sort using the filter at the top of the column of my form (in datasheet view) and I would like to store calculated figures in the table as I have to export this data to analyse.
Yes i am using a select query at the moment; when I try to use an update query the formula DateDiff("ww",[Ref Date],Now()) - i get a syntax error and I am unsure I am using the correct query type.
In terms of 'cycle the sort', I am a little bit lots as to what you are referring to. Yes Ref Date is a field in the query.

If you let me know what else you need to know to help me, I will do my best.

Thanks

[Micron]

I would like to be able to sort using the filter at the top of the column of my form (in datasheet view)

Helps, but you didn't answer whether or not the sort will be static, or you want to change it from time to time. If no change, just sort via the query. A simple datasheet view works for me, so I don't see why it would not for you. By cycling the sort, I meant would it be A > Z, possibly changing to Z > A during the same view, and maybe back again as you cycle through records. BTW, you don't say what field you want to sort on, although I suspect it should work even if it's on the calculated field.

Can you post a zipped copy of your db with brief instructions on how to replicate your issue? It may be that what you need is a 2 step process; create record, then update based on some other/older data. Hard to say without knowing more of what you're trying to do and what you have.