Default value: date (1 Viewer)

[nick5196]

Hi,
I have a sub table within a form containing some dates. I would like to create a default value which will be 12weeks (84 days) after the LAST RECORDED DATE.

*I can make this work for 84 days after todays date

date()+84

but am unsure of how to specify the last record/date.

Thanks allot
Nick

 

[DCrake]

Use DMax() to find the last recorded date and then use DateAdd() to add the 84 days to it.

 

[nick5196]

Sorry I am still unsure of how to put these 3 together, could you specify a little more what i should type in the default value box.

Thank you very much
Nick

 

[Brianwarnock]

Be aware that DMAX will find the maximum value in the field tested , which may or may not be the LAST RECORDED DATE.

Brian

 

[DCrake]

So what you are saying is you want it in your default value for new records in tables? However not 84 days from today but from a date supplied by the user. Last recorded date?

How are you collecting the lat recorded date?

 

[nick5196]

Thats exactly correct DCrake, and DMax will be okay because the last recorded date will be the greatest...thanks for the concern BrianWarnock.

I am just unsure of how these should be combined within the box.

Thanks

 

[nick5196]

Sorry I missed the last line of your response, I am recording the date using a subtable within my main form. The dates are entered using a datepicker.

Hope that helps

 

[vbaInet]

What are the chances that the LAST RECORDED DATE will not be Today's Date?

Is the Last Recorded Date the field where Date()+84 will be Default? If this is the case, so the next Default Value would be Date()+84+84?

Following from Brian's comment, if you're going to test against a field, then you also need that field to have a Default Value of Date() so that you're sure the max of that field will be the last recorded date.

 

[nick5196]

There is a reasonably high chance. I have uploaded a copy of the DB to prevent confusion. Basically the client must go for a review every 12weeks and she want there to be a default value of 12 weeks after the last review date.

*you want to be looking in the tblReviewDates table and the field is reviewdate.

Thanks for all your help

 

[vbaInet]

Your table is not normalized and as a result you won't be able to get the Max Review Date for that client. There should be a ClientID in that table but that's just one part of normalization. You can read more about it:

http://support.microsoft.com/kb/283878
http://r937.com/relational.html

 

[nick5196]

I have now normalized my table, how should the defult value "code" specifically read. I have been trying

DMax(84)

but this is not recognised.

Thanks

 

[DCrake]

DMax usage

x = DMax("YourDateFieldHere","YourTableHere","YourConditionHere")