How to create an expression that uses auto date with isnull

[Breeves22]

Hi I am fairly new to access and only just beginning to work with creating my own database.

I have three fields, the referral date, the visit date and the joint visit date.

I am trying to create an expression that will imput a date into the joint visit box based on whether the visit date box is filled in or not. For example if the referral box is filled in then but not the visit date then the joint visit box will come up with a date 30 days after the one in the referral box. but if the visit date box is filled in then the joint visit box will show a date ten days after the date in the visit box.

The code i have written is

=iif(isnull[Joint visit]), =DateSerial(Year([Referral]),Month([Referral]),Day([Referral])+30),=DateSerial(Year([Visit]),Month([Visit]),Day([Visit])+10))

however i am not sure where i am going wrong so any help would be greatly appreciated.

Thankyou in advance

[TheSmileyCoder]

Its a bit hard to see where your using this bit of code, and also your don't mention what error you are getting.

Im gonna guess that you written it in the controlsource of a textbox, since you have a = at the front. Be aware that what you will get then is a DISPLAYED/CALCULATED value, and that value will not be stored anywhere.

Also if your textbox is called [Joint Visit] and you try to reference itself within its on controlsource, you will likely get errors as it becomes a circular reference.

=DateSerial(Year([Referral]),Month([Referral]),Day([Referral])+30)
can be more smoothly written as:
=DateAdd("d",30,[Referral])

What you can do is tie the filling of the field into a certain event, but you have to be clear on what the event should be. Should it be after updating a certain field? Or before saving the record?

[Breeves22]

Sorry the error message i am getting is you may have entered an operand without an operator.

And i entered it wrong on my entry. where the code says Joint Visit its meant to say visit date. i already corrected that problem

[NeoPa]

Smiley raises a very fair point (See the bold text at the bottom of post #2).

I would guess you only want this value changed in certain, more specific, circumstances. Let me take a stab at guessing what you meant to say in the question and you can correct me if I'm wrong.

You have a form where you view/edit records which contain fields for the three dates named ([Referral], [Visit] & [Joint Visit]). Whenever the value in [Referral] is changed, but the value in [Visit] is still Null (empty), you want to set/update the value in [Joint Visit] to reflect a date 30 days after the date value in [Referral]. Whenever the value in [Visit] is changed, you want to set/update the value in [Joint Visit] to reflect a date 10 days after the date value in [Visit].

Can you please confirm or correct the accuracy of this for us before we proceed with suggesting a solution.

Welcome to Bytes!

[Breeves22]

Hi Neopa. Yes that is exactly what i am trying to do. Sorry for not making it clearer

[NeoPa]

You would need some code similar to the following in your form's module, with both controls properly set to trigger the event procedures :

Private Sub Referral_AfterUpdate()
    With Me
        If IsNull(.Visit) And IsDate(.Referral) Then _
            .[Joint Visit] = DateAdd("d", 30, .Referral)
    End With
End Sub

Private Sub Visit_AfterUpdate()
    With Me
        If IsDate(.Visit) Then _
            .[Joint Visit] = DateAdd("d", 10, .Visit)
    End With
End Sub

[Breeves22]

Thats Fantastic works great now. cheers

[NeoPa]

Very pleased to hear it :)