Expression As Default Value in Control Properties

[shane]

I want a field to update to the 12am date rollover for shift changes. I
typed the expression:

=iif([shift]="3",date()-1,iif([shift]="B",date()-1,iif([shift]="D",date()-1,date())))

Into the the default value of the date field from the form and it did not
work. Typing the same expression in the default value in the table for the
same field brought up an error.

I'm not sure what I'm doing wrong nor how to get the automated changes I
want. Please help.

 

[Ken Sheridan]

As the DefaultValue property applies to a new record, i.e. one which has not
only not yet been saved but has not even begun to have data inserted, then
there is no way of knowing the value of [shift] at this stage. You need to
assign a value to the date field's control when the Shift control is updated,
so in its AfterUpdate event procedure put code along these lines:

Select Case Nz(Me.shift, "~")
Case "~"
Me.datefield = Null
Case "3", "B", "D"
Me.datefield = DateAdd("d", -1, VBA.Date)
Case Else
Me.datefield = VBA.Date
End Select

The tilde character is used here as the return value of the Nz function if
the shift control is Null on the assumption that the shift control's value
will never legitimately be a tilde character.

While this will work its not ideal as data is being hard coded into the
procedure, i.e. the fact that shift values of "3", "B" or "D" determine the
relative value of the date field. As a fundamental principle (the
'information principle') of the database relational model is that data is
stored only as values at column positions in rows in tables, these values
would be better stored in a Shifts table so that the rows with values "3",
"B" or "D" in a shift column would contain a value of -1 in an adjustment
column. The adjustment could then be looked up in the shift control's
AfterUpdate event procedure :

Dim varAdjustment as Variant
Dim strCriteria as String

strCriteria = "Shift = """ & Me.Shift & """"

varAdjustment = DLookup("adjustment", "shifts", strCriteria)

If Not isNull(varAdjustment) Then
Me.datefield = DateAdd("d", intAdjustment , VBA.Date)
Else
me.datefield = Null
End If

To protect data integrity the relationship between the Shifts table and your
current table on the shift columns should be enforced. Cascade updates
should also be enforced so that should a shift value in Shifts be changed the
values in matching rows in your current table will change automatically.
Cascade deletes should NOT be enforced, however!

Ken Sheridan
Stafford, England

 

[Scott McDaniel]

I want a field to update to the 12am date rollover for shift changes. I
typed the expression:

=iif([shift]="3",date()-1,iif([shift]="B",date()-1,iif([shift]="D",date()-1,date())))

Into the the default value of the date field from the form and it did not
work. Typing the same expression in the default value in the table for the
same field brought up an error.

I'm not sure what I'm doing wrong nor how to get the automated changes I
want. Please help.

A Default Value is a value that is used when you add a NEW record ... when you add a New record, the [Shift] field is
Null. You'll probably be better off using something like this in the AfterUpdate event of whatever field you want to key
this off of. For example, if you have a ShiftDate field, and you want that updated when the user selects a Shift:

Sub Shift_AfterUpdate()
Select Case Me.Shift
Case "3","B", "D"
Me.ShiftDate = DateAdd("d", -1, Date)
Case Else
Me.ShiftDate = Date()
End Select
End Sub

You can't do this on the Table level at all.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com