If a field is Blank I want the default data to be taken from a field in a table

[Javelin]

All,

Can someone please help - it's driving me crackers !!!

Basically - I want to be able to track when a record was last modified - So that I can see if a record has not been reviewed within the last 30 days.

So far;

I have created a form, which adopting a great piece of code from this site, automatically creates an audit trail if any data on the form (individual record) is changed.

On this form is a field entitled 'Last_Modified' that automatically updates (with the 'Date ()') every time a change is made.

However - if the form is displaying a record that has not been amended/changed since it was created - this field is blank.

This means that if I run a query based upon the 'Last_Modified' date it would not recognise/identify the risk because there is no data entry - therefore a risk could that has not been modified since it's creation could potentially be unmonitored.

What I want to do is if the 'Last_Modified' field is Blank/empty I want the default data to be taken from a field in a table named 'Date_Raised'.

Do I do this through code? / using Before_Update?

Can someone please help?

Many thanks in advance.

Javelin

 

[noccy]

Maybe you could do somethink like this on Form load:

Dim MyNewValue As String

MyNewValue = Dlookup ( YOUR VALUES, SEE HELP FILE)


If Me.txtLastModified = "" Then
Me.txtLastModified = MyNewValue



good luck

noccy

 

[dcx693]

Your query should havd a calculated field that selects the Last_Modified date if it's not blank, else take the Date_Raised date. In the Field: line of your query enter something like this:
LastModDate:IIf(IsNull(Last_Modified), Date_Raised , Last_Modified)

 

[Pat Hartman]

Where Last_Modified Is Null or Last_Modified < Date() - 30;

 

[SforSoftware]

I should think to use the fields' DefaultValue in form or table. So the DateCreated is for each New Record set to the systemdate.

Bert