-2

Using MS Access expression builder for if fieldA value = " then fieldBdate = " table name is myOrder; myOrder has several fields including fieldA text (using value list for value selection, of which "Received" is one); And another field is named fieldBdate of DateTime type.

either
iif (fieldA] = "Received",[ fieldBDate] = Date(), null);
or 

if ([fieldA] = "Received" then[ fieldBdate] = Date()

failed to meet with Access expression syntax

with both Access 2000 and Access 2010. What's the correct syntax?

Thanks.

user963063
  • 73
  • 1
  • 9

1 Answers1

1

Time for you to learn how to use built-in VBA manual with Intellisense and even basic google search of IIf syntax

How to use:

IIF( <test-for-condition>, <value if true>, <value if false> )

In your case:

FieldBDate = IIf([FieldA] = "Received", Date(), Null)

EDIT - Based upon your comment that you want to set Default Value

You cannot set the Default Value expression based on another field in a table design. Think about it - there's no way that Access knows what the other field's value is until it's actually entered.

What you need to do is add the above code to your FieldA_AfterUpdate event

dbmitch
  • 4,400
  • 4
  • 21
  • 37
  • Informative and it's very logical. However, with Access table design, and then, Default Value field (for the fieldB): either = IIf([FieldA] = "Received", Date(), Null) or fieldB = IIf([FieldA] = "Received", Date(), Null) failed with the error msg to the event that Access does not recognize the field. Thanks. – user963063 Aug 27 '16 at 04:03
  • You're trying to use this with "Default Value"? You didn't think that was pertinent to the question? Maybe add this as well as actual field names and types to your question – dbmitch Aug 27 '16 at 17:07