none
Access Table Default value based on another field in same table using expression RRS feed

  • Question

  • Hi all,

    I am even not sure if one field's default value can be defined from another field's value in the same table.

    For example I am trying a nested if expression depending on one of three options in on field and then have a default value in another:

    =IIf([Priority]="1(High)", "Date()", IIf([Priority]="2(Medium)","DateAdd("d", +1, [TaskDate])", IIf([Priority]="3(Low)","DateAdd("d", +3, [TaskDate])", "Nothing")))

    I have 3 options in [Priority] field and depending on the option I want to define a default value.

    if option - "1(High)" in [Priority] field then default value in [DueDate] field equal - "Date()"

    if option - "2(Medium)" in [Priority] field then default value in [DueDate] field equal - "DateAdd("d", +1, [TaskDate])"

    if option - "1(High)" in [Priority] field then default value in [DueDate] field equal - "DateAdd("d", +3, [TaskDate])"

    However, I keep on receiving a syntax error constantly.

    Any suggestions?

    Monday, November 11, 2019 8:13 PM

All replies

  • Does this work?

    =IIf([Priority]="1(High)", Date(), IIf([Priority]="2(Medium)",DateAdd("d", 1, [TaskDate]), IIf([Priority]="3(Low)",DateAdd("d", 3, [TaskDate]), "Nothing")))

    i.e. no quotes around the DateAdd expressions


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 11, 2019 9:23 PM
  • If you assign a value to a bound control's Default Value property on the basis of the value in other columns in the current record the value will be the default, not for the control in the current record, but in the next new record inserted via the form in the same session of the form.  Is that really what you are attempting?

    Or, as I think more likely, do you mean by 'default value' that you want to assign a value to the DueDate control in the current record, but allow the assigned value to be edited if necessary?  If so, you should do so in the Priority control's AfterUpdate event procedure.  A complicating factor is that in some cases the value assigned is derived from the TaskDate value, so it is necessary to ensure that control has a date before a value is inserted into the Priority control.  You can do this in the Priority control's BeforeUpdate event procedure with:

       Const MESSAGE_TEXT = "A task date must first be entered."
       
       If IsNull(Me.TaskDate) Then
           MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
           Cancel = True
           Me.Priority.Undo
       End If

    In the AfterUpdate event procedure of the Priority control assign a value to the DueDate control on the basis of the value in the Priority control:

        Select Case Me.Priority
            Case "1(High)"
            Me.DueDate = VBA.Date
            Case "2(Medium)"
            Me.DueDate = DateAdd("d",1,Me.TaskDate)
            Case "3(Low)"
            Me.DueDate = DateAdd("d",3,Me.TaskDate)
            Case Else
            Me.DueDate = Null
        End Select


    Ken Sheridan, Stafford, England

    Monday, November 11, 2019 11:07 PM
  • Hi, thank you for your help. Unfortunately, I still get syntax error: "You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks."
    Wednesday, December 4, 2019 8:49 AM
  • I can't see any errors in the code I posted.  Copy the exact code you have used in both event procedures to the clipboard and paste it into a reply here.

    Ken Sheridan, Stafford, England

    Wednesday, December 4, 2019 12:02 PM
  • Do you use ; as list separator on your system? If so, the expression should be

    =IIf([Priority]="1(High)"; Date(); IIf([Priority]="2(Medium)"; DateAdd("d"; 1; [TaskDate]); IIf([Priority]="3(Low)"; DateAdd("d"; 3; [TaskDate]); "Nothing")))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 4, 2019 12:08 PM