I'm trying to use a DMax in a Continuous Subform to autopopulate the Date by the last entered Date in the Table. It works for the first entry but if I change the Date to a different day the next line populates as 12/30/1899. I have this code in my Default Value. Any help is appreciated.
That date is actually Day 0 of the Access date computation algorithms. Which means that you get a 0 for whatever computation you are using. I don't believe you can include domain aggregate functions (e.g. DMax) in a default value declaration.
Part of the problem is that only very simple expressions - usually, constant-value expressions - can occur for a default value. You can define a bit of code in the subform BeforeUpdate event (that would write the new record) to include a test for a new record or an empty date and use a variant of this...
I'm trying to use a DMax in a Continuous Subform to autopopulate the Date by the last entered Date in the Table. It works for the first entry but if I change the Date to a different day the next line populates as 12/30/1899. I have this code in my Default Value. Any help is appreciated.
The reason for the weird date is that the date is being interpreted as an arithmetical expression rather than a date. The result will be a small fractional number of course. As Access implements the date time data type as a 64 bit floating point number, this will be interpreted as sometime on 30 December 1899 because midnight at the start of that date is represented as zero in the implementation of the date/time data type.
The fact that this happens when setting the DefaultValue property is because that property is always a string expression, regardless of the data type in question. The following should work therefore in the control's properties sheet:
Code:
Cstr(DMax("TransDate","tblTrans"))
In cases like this where the value will change as data is inserted or edited, however, I always set the property in code in the form's Current event procedure, in which context the value should be wrapped in literal quotes characters:
The problem with setting a control's DefaultValue property by means of an expression in its properties sheet is that, if an existing date in a record is edited, or a date is inserted in a new record, so that it is later than the default, the control's default value will not become this new later date, but will remain that when the form was opened or last requeried.
The way to avoid this is to assign the latest date to the control's DefaultValue property in the form's Current event procedure, being sure to wrap the value in literal quotes characters to avoid its being misinterpreted as an arithmetical expression, which would not raise an error, but give an incorrect date.
...being sure to wrap the value in literal quotes characters to avoid its being misinterpreted as an arithmetical expression, which would not raise an error, but give an incorrect date.
PS: Don't be tempted to wrap the value in the # date delimiter characters. This would change 4th July to 7th April for those of us not using US short date format.
The reason for the weird date is that the date is being interpreted as an arithmetical expression rather than a date. The result will be a small fractional number of course. As Access implements the date time data type as a 64 bit floating point number, this will be interpreted as sometime on 30 December 1899 because midnight at the start of that date is represented as zero in the implementation of the date/time data type.
The fact that this happens when setting the DefaultValue property is because that property is always a string expression, regardless of the data type in question. The following should work therefore in the control's properties sheet:
Code:
Cstr(DMax("TransDate","tblTrans"))
In cases like this where the value will change as data is inserted or edited, however, I always set the property in code in the form's Current event procedure, in which context the value should be wrapped in literal quotes characters:
