auto update date() daily basis

[infinite2006]

I have a query that is used to calculate job order status in days. It subtracts 'due
date' from the 'current date', which is auto completed in table properties, date().
This query is simply used to display which jobs are more urgent than others for
production, and are then colour coded using a conditional format expression.

Order status: DateDiff("d",[Current Date], [Due Date])

However, order status (days) does not change from day to day because the 'current
date' does not update each day, its fixed on the day the record was added to the
Database, in table properties, date() as mentioned before.

How do I get 'current date' to update on a daily basis to reflect the order status
in days changing daily?

Thanks in advance

 

[SOS]

Instead of using it in the table just use it like this:

Order status: DateDiff("d",Date(), [Due Date])

 

[infinite2006]

Can you explain more in really dumb terms for me please? Remove current date completely from table and just run query like you suggest?

BTW,Thanks for the quick reply

 

[SOS]

Remove current date completely from table and just run query like you suggest?

Yes, that is correct.

 

[infinite2006]

Yes, that is correct.

Thanks SOS, thats it sorted! whilst you're in the prob solving mood, perhaps you can shed some light: I have a form with a conditional format which highlights in colour the one field that is active for the expression being 'true'. is there anyway of making the entire record highlight in colour? Im using Access 2007. The field is 'order status', if true, this field is highlighted in red, i'd prefer if it ws entire record, multiple fields. [order status]

 

[SOS]

Thanks SOS, thats it sorted! whilst you're in the prob solving mood, perhaps you can shed some light: I have a form with a conditional format which highlights in colour the one field that is active for the expression being 'true'. is there anyway of making the entire record highlight in colour? Im using Access 2007. The field is 'order status', if true, this field is highlighted in red, i'd prefer if it ws entire record, multiple fields. [order status]

You would need to add the conditional formatting to each of the controls. For the other controls you would need to change from FIELD IS to EXPRESSION IS and then use
[order status] = True

 

[infinite2006]

SOS, final question on this. Each record in the order form has a 'delivered' field, which is just y or n in table properties. For data maintenance I'd like the record to be automatically deleted from the database when delivered is set to y. Is this straightforward to achieve or does it involve coding?

Thanks in advance

 

[spikepl]

For data maintenance I'd like the record to be automatically deleted from the database when delivered is set to y.

No you don't. Any mistyping and your order is gone. Or when lost in the mail and the customer calls - "sorry, our system erased the data" ?!?! You have no idea how many data-mining statistics can be compiled on data, ir which ones will be desired in the future.

In db's you very seldom erase data - you mark it inactive and do not display it when unwanted. But wait - you already have a flag - it is "Delivered=yes". Simply do not display orders that have been delivered (but supply possibility of showing them anyway).

 

[infinite2006]

Thanks, justification for maintaining all data makes sense. But can you tell me how to hide records after they are set as "y" in the delivered field?

Thanks

 

[Pat Hartman]

Flags such as this have very little value. A date has more meaning and can be used for analysis and archiving. So as has already been mentioned, you don't need the flag at all. Use the date to determine whether something has been delivered.

I do delete transaction data from many of my applications. However, rather than losing it totally, I move it to an archive database. The archive isn't used for anything on a regular basis but if you wanted to research old data, you could do it. The length of time you keep old data in the current database depends on data volume and what you need it for. Three-four years is more than enough time for most transaction data. Running an archive procedure each month to take all data over four years old and transfer it to the archive and then delete it once the archive has been backed up will keep your active database lean and mean.

 

[infinite2006]

got it, subtracts date delivered from current date, >90 days, not displayed.