another date question

[Bruce75]

Hi,

I have seen that there are lots of threads about working out the difference between two dates, but I think my question is a little different.

I have a form, on which their are 3 fields, Referral Date ("referraldate", 23/07/2007), Discharged (yes/no field) and discharge date ("dischargedate", 23/07/2007).

I would like there to be a control on the form that shows the length of time a patient has spent in the service. If they have been discharged, it would be dischargedate-referral date. However, if they have not been discharged, it would be today's date - referral date.

How do I go about this? I am still a beginner, so any help would be appreciated.


cheers

Bruce

 

[rainman89]

try something like this

if discharge = "yes" Then
anewfield on your form= datediff(d,[dischargedate],[referaldate]
else
anewfieldonyourform = datediff(d,[referaldate],date())
end if

 

[Bruce75]

thanks for your promt reply

I have tried the following code in the forms OnLoad event

Private Sub Form_Load()

If discharged <> 0 Then
timeinservice = DateDiff(d, [dischargeddate], [referraldate])
Else
timeinservice = DateDiff(d, [referraldate], Date)
End If

End Sub

but it does not work, it says "you cannot assign a value to this object".

Sorry to bother you again, but what am I doing wrong?

cheers

 

[rainman89]

if discarged is a yes/no field then you can use "yes" in it.
im assuming timeinservice is a field on your form?

try

Private Sub Form_Load()

If discharged= "yes" Then
me.timeinservice = DateDiff("d", [dischargeddate], [referraldate])
Else
me.timeinservice = DateDiff("d", [referraldate], Date())
End If

End Sub

 

[CEH]

I think your error is in the line "If discharged <> 0 Then"
try using something like "If Me.Discharged = True then"

 

[Bruce75]

Thanks to both of you,

I have tried all your suggestions, and now it is coming up with a different error: run-time error '5' Invalid procedure call or argument

this is what i have

Private Sub Form_Load()

If Me.discharged = True Then
Me.timeinservice = DateDiff(d, [dischargeddate], [referraldate])
Else
Me.timeinservice = DateDiff(d, [referraldate], Date)
End If

End Sub

any more help is much appreciated>

thanks

 

[Bilbo_Baggins_Esq]

I could be wrong here, but couldn't it be:

DateDiff(d, [referraldate], Date())

 

[Brianwarnock]

Datediff("d" note the parameter is in "

Brian

Note on Date function it does not need () in code, but it does elsewhere.

 

[Bruce75]

thanks to all. it was a mistake with syntax.

you are all very generous with your time. thanks again

 

[rainman89]

Glad to help

 

[missinglinq]

I notice that all the code being posted here appears to be in the Form_Load() sub; this code will only apply the the first record! For it to work with each record it has to be in the Form_Current() sub!

 

[CEH]

I think you will find you also need it on the OnClick event of your checkbox.

 

[boblarson]

I think you will find you also need it on the OnClick event of your checkbox.

I, personally, would use the After Update event of the checkbox. I used to use the click event of the checkbox, but have since learned that, if you need to test the value of the checkbox, the new value isn't reflected in the click event. So, if you had code that tests whether the checkbox itself is true or false and let's say that it was true and then you click it to make it false, if you use the Click event to capture the value it will still show as true but in the After Update event it will show as false.

So, to be consistent, I always use the After Update event of checkboxes, frame controls, etc. unless I absolutely can't.

 

[missinglinq]

Me.too, Bob!

;0)>