Calculate a date based on text box 1

[akirk]

Working on a form where I need to calculate a date in one text box based on data in two others. There are three textboxes involved. I have tried searching, but for some reason, the search function does not seem to work if I add a second word to the search.

txtType (text)
txtEventDate (date/time)
txtEstDateSend (date/time)

if txtType = A
take the date value in txtEventDate and subtract 21 days, placing this value in txtEventDate

if txtType = B
take the date value in txtEventDate and subtract 42 days, placing this value in txtEventDate

if txtType=anything else
put nothing in txtEvent Date

I have tried the following VB in the afterupdate attribute of txtEventDate, but nothing happens:

Private Sub txtEventDate_AfterUpdate()
If Me!cmbEvent.Value = "Seminar" Then
Me!txtEstimateDateSend.Value = DateAdd("d", -21, Me!txtEventDate.Value)
Else: Me!txtEstimateDateSend.Value = ""
End If

End Sub

 

[rdodge]

If you don't have to have your dates go before January 1, 1900, you can use the following:

Private Sub txtEventDate_AfterUpdate()
If Me!cmbEvent.Value = "Seminar" Then
Me!txtEstimateDateSend.Value = CDate(Me!txtEventDate.Value) - 21
Else: Me!txtEstimateDateSend.Value = ""
End If
End Sub

If you have not set your format on the field/textbox, then you could use the following code instead:

Private Sub txtEventDate_AfterUpdate()
If Me!cmbEvent.Value = "Seminar" Then
Me!txtEstimateDateSend.Value = Format(CDate(Me!txtEventDate.Value) - 21,"Short Date&quot
Else: Me!txtEstimateDateSend.Value = ""
End If
End Sub


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[67peshawar294]

akirk,
I'm from the KISS school.
in an unbound txtbox, control source;
=IIf(textboxone="A",date()-21,IIF(textboxtwo="B",date()-42,"&quot)

 

[rdodge]

I'm with you xaf294, I don't have any of my forms/controls bound cause of the issue I have with regards to a conflict between friendliness of the forms/controls for mouse users while still having the strict required data validation checks in place and executed at the appropriate times.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[akirk]

Thanks for the help, however, after trying both of Ron's codes in the after_update attribute for txtEventDate, nothing happens upon tabbing to the next field.

Not sure I can use the other solution, as this form is to enter data into a table. I tried it, but kept getting the #name? error in the txtEstimatedDateSend field.

Do I need to add some code to the txtEstimatedSend field as well?

kirk

 

[rdodge]

Check in your references list as you should have the following:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3

If using DAO, have

Microsoft DAO 3.6 Object Library

To check this list, follow the steps below:

Press Alt-F11 to get into Visual Basic Editor (if not already there)
Click on Tools>References

Be sure the above are checkmarked.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[OhioSteve]

You don't tell us much about tEstDateSend. Consequently, I'm assuming that you just need help with txtEventDate. I would handle the situation using calculated fields in a select query. I would bind the query to the form.

In the query, start with two normal fields, type and baseDate (they would be fields in my table). Add a calculated field called calculatedDate. Here is how to set up calculatedDate:

calculatedDate: iif([type]="A",[baseDate]-21,iif([type]="B",[baseDate]-42,[baseDate]))

calculatedDate should be the control source for txtEventDate.

 

[akirk]

Thanks Ron, I have all those settings checked off. Sure I am overlooking something simple.

 

[akirk]

Finally stopped getting error messages, but still do not get anything in the desired textbox.

Not sure what OhioSteve means about knowing about EstDateSend. The form is intended to be used to enter and track marketing information for a single person. For ease of data entry, they want this field to autopopulate for two types of events, but not to populate for any other types.

Here is the code I have. I truly appreciate all the help.

Private Sub txtEventDate_AfterUpdate()
On Error GoTo errorHandler


If Me!cmbEvent.Value = "Seminar" Then
Me!txtEstimateDateSend.Value = Format(CDate(Me!txtEventDate.Value) - 21, "Short Date&quot
Else
If Me!cmbEvent.Value = "Training" Then
Me!txtEstimateDateSend.Value = Format(CDate(Me!txtEventDate.Value) - 42, "Short Date&quot
Else
Me!txtEstimateDateSend.Value = ""
End If
End If

errorHandlerExit:
Exit Sub

errorHandler:
MsgBox Error.Description
Resume errorHandlerExit
End Sub

 

[rdodge]

Since a Textbox is by default in String Data Type (unless one of the properties has been changed to reflect otherwise), I did the following 2 tests in a Standard Module, both of which works just fine me. They both return the date of "12/04/2003" (December 4th, 2003) going back from "12/25/2003" or it's equivalent date number "37980".

Sub TimeTest()
Dim StringDate As String, StringDate2 As String
StringDate = "12/25/2003"
StringDate2 = Format(CDate(StringDate) - 21, "Short Date&quot
End Sub

Sub TimeTest()
Dim StringDate As String, StringDate2 As String
StringDate = "37980"
StringDate2 = Format(CDate(StringDate) - 21, "Short Date&quot
End Sub


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[OhioSteve]

At the start of your original post, you tell us that you are working with three fields:

txtType (text)
txtEventDate (date/time)
txtEstDateSend (date/time)

But then you never mentioned txtEstDateSend again. That is what I meant. Perhaps there are some typos in your original post. When you wrote "...placing this value in txtEventDate..." did you intend to write "...placing this value in txtEstDateSend..." ??

In any event, the line of Access SQL that I have already posted will calculate the values that you need. You don't have to write a VB routine.

calculatedDate: iif([type]="A",[baseDate]-21,iif([type]="B",[baseDate]-42,[baseDate]))