Dmax problems

[dullster]

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.

DMax("TransDate","tblTrans","TransDate")

 

[CJ_London]

Try
Dmax("TransDate","tblTrans")

Yo don’t need a criteria

 

[The_Doc_Man]

the next line populates as 12/30/1899

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.

Field.DefaultValue property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

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...

Me.Transdate = DMax("TransDate","tblTrans") + 1

That might work if you just wanted numbering of consecutive days.

 

[Ken Sheridan]

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.

DMax("TransDate","tblTrans","TransDate")

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:

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:

Me.TransDate DefaultValue = """" & DMax("TransDate","tblTrans") & """"