Help on Access Expression

[kbaisch]

Can someone tell me why this expression doesn't work in the build function of a form? Can a date be null? Check5 is a check box.

=IIf([Check5]=0,[received_date]=Null,Date())

Thank you for the help.

[NeoPa]

What do you mean doesn't work?

Certainly to determine if something holds a Null value or not is done differently within VBA and SQL. Neither uses the equality operator (=).

Perhaps if you could explain what you're trying to we could be more help. As it stands it looks like you are trying to assigne (rather than return) a value within the True part of the IIf() function - which makes no sense.

[NeoPa]

On closer inspection, the initial "=" would indicate SQL code within a query. In that case to determine if a value is Null you would use "x Is Null", but this still doesn't explain what you're intending to get from this.

[kbaisch]

I am trying to say, if check box, Check5, is not checked then the field "recieved_d ate" is null else todays date. Am I totally doing this wrong? Does this help you?

[kbaisch]

In other words, if the check box isn’t checked then I don’t want a date in the received_date field, if the check box is checked then I do want todays date in the received date_field.

[NeoPa]

Pretty well yes :(

Is this formula to be entered into the field [Received_Date] in the query?

If so, then use this :

=IIf([Check5],Date(),Null)

If not, then you'll need to explain how [Received_Date] fits in.

[kbaisch]

This formula is to be added to the check5 box “check box” on lost focus in the properties. If the box is check then I want todays date to appear in the received_date box, if it’s not checked then I want received_date to be blank with no date. So the check box comes first then the date box.

[NeoPa]

If I understand you correctly then you're actually looking for the following code.

Private Sub Check5_AfterUpdate()
  Me.Received_Date = IIf(Me.Check5,Date(),Null)
End Sub

NB. This would be in the AfterUpdate event procedure (not lost focus and not properties).

[kbaisch]

Thank you very much, that works.

[NeoPa]

It's a pleasure to Help :)