Create a Default Value

[Dwight]

My database has a form that allows users to input transaction data. Two dates must be entered: 1) Trade Date and 2) Settle Date.

I would like the default value of Settle Date to be Trade Date + 3 but if the Trade Date is a Friday it should be Trade Date + 5.

Can a kind soul help me with the VBA code? I think I would put this on the Before Update property of the Settle Date control?

If the Friday thing makes it hard then just axe it. Mainly I want to know how to put the code on the control to create a default value. This will come in handy in several places.

For example, another field is Gross Amount. I want its default value to be the product of the Units and Price fields.

Kind regards,

Dwight

 

[Rich]

Article ID: Q149127

 

[jon_sg]

Check out the weekday function built into Access. This returns the day of the week as an integer. The basis of your code could be:

Dim tradedate As Date
Dim stldate As Date
Dim response As Integer
tradedate = Me.[control1]
response = Weekday(tradedate, vbSunday)
If response = 6 Then
stldate = tradedate + 5
Me.[control2] = stldate
Else
stldate = tradedate + 3
Me.[control2] = stldate
End If

End Sub

This code could be put into the after update event on your transaction date control on your form. You will have to change control1 and control2 to the field names for transaction date and settlement date as per your form.

 

[Dwight]

Thanks to both of you.

Rich, the article is very good and now resides in my reference pile.

Jon_Sg your solution was made to order. Works great!

Kind regards, Dwight

 

[jon_sg]

Jons inelegant subs

Dwight,
just to let you know that the sub I wrote for you was done v quick. When I looked at it after your reply I noticed that Me.[control2] = stldate should be after the end if. Instead of being duplicated within the If statement. I should also have used "!" operator rather than "."

It dosn't really matter but it is better programming form to do it that way.