Combining Expressions

[Roger4607]

Hi,

Here's what I would like to do - I have 3 fields that are relative to one another.

in field 2 i want to put a date, and then i need field 3 to calculate another date that is earlier than the date put in field 2.

for this I am using the following expression - =DateDiff("d",+7,[Field 2])

This works fine, however this is where it gets complicated.

The DateDiff varies depending on what is entered in field one.

If in field 1 I enter the text 1 Month, I need the DateDiff to be for example 7 days. If I enter 4 Months, I need the date diff to be 90 days, If I enter 6 Months I need the DateDiff to be 120 days .....

So you see my problem .... any ideas very gratefully accepted!!!

[NeoPa]

Firstly, you confuse things by saying an earlier date then illustrating with DateDiff("d", +7, ...)
An earlier date would have a negative (-) number there.
For your problem use "Select Case Me.[Field 1]" and assign the value of days different to a variable (intDays).
After that's completed use :

Me.[Field 3] = DateDiff("d", intDays, Me.[Field 2])

[Roger4607]

Firstly, you confuse things by saying an earlier date then illustrating with DateDiff("d", +7, ...)
An earlier date would have a negative (-) number there.
For your problem use "Select Case Me.[Field 1]" and assign the value of days different to a variable (intDays).
After that's completed use :

Me.[Field 3] = DateDiff("d", intDays, Me.[Field 2])

I really appreciate your help here, -- but I still cant get it to work!!

I'm using a drop down box for field one which has different values - 1 Month, 2 Month, 3 Month.

When a date is entered in field 2 I need field 3 to fill in automatically with an earlier date depending on what is entered in field 1. For 1 month is should show 30 days earlier than the date put in field 2, for 2 month 60 days and 3 month 90 days.

I've tried phone a friend and the computer's already taken away 2 wrong answers .....

Any help greatly appreciated!!

[ADezii]

I really appreciate your help here, -- but I still cant get it to work!!

I'm using a drop down box for field one which has different values - 1 Month, 2 Month, 3 Month.

When a date is entered in field 2 I need field 3 to fill in automatically with an earlier date depending on what is entered in field 1. For 1 month is should show 30 days earlier than the date put in field 2, for 2 month 60 days and 3 month 90 days.

I've tried phone a friend and the computer's already taken away 2 wrong answers .....

Any help greatly appreciated!!

In the AfterUpdate() Event of Field1:

Private Sub Field1_AfterUpdate()
If Not IsNull(Me![Field1]) And Not IsNull(Me![Field2]) Then
  Select Case Me![Field1]
    Case "1 Month"
      Me![Field3] = DateAdd("d", -30, Me![Field2])
    Case "2 Months"
      Me![Field3] = DateAdd("d", -60, Me![Field2])
    Case "3 Months"
      Me![Field3] = DateAdd("d", -90, Me![Field2])
    Case Else
      'do nothing
  End Select
End If
End Sub

[NeoPa]

ADezii's answer is pretty similar to what I was saying. A nice full solution for you too. Make sure when you use it though, that you change the control names to match yours exactly. Me![Field2] =/= Me![Field 2]. We don't have your database to hand so we can only use our best guesses and what you tell us.
Another solution, though a little harder to get your head around, is to have an extra column in the ComboBox and refer to that for the second parameter to the DateAdd() call. You shouldn't need to worry about that though, as your situation seems relatively straightforward (and ADezii has already provided some pretty neat code anyway ;)).

[ADezii]

In the AfterUpdate() Event of Field1:

Private Sub Field1_AfterUpdate()
If Not IsNull(Me![Field1]) And Not IsNull(Me![Field2]) Then
  Select Case Me![Field1]
    Case "1 Month"
      Me![Field3] = DateAdd("d", -30, Me![Field2])
    Case "2 Months"
      Me![Field3] = DateAdd("d", -60, Me![Field2])
    Case "3 Months"
      Me![Field3] = DateAdd("d", -90, Me![Field2])
    Case Else
      'do nothing
  End Select
End If
End Sub

I can't believe that I am actually crazy enough to Reply to my own Post, but to actuallly make the code more efficient:

Private Sub Field1_AfterUpdate()
If Not IsNull(Me![Field1]) And Not IsNull(Me![Field2]) And IsDate(Me![Field2]) Then
  Select Case Me![Field1]
    Case "1 Month"
      Me![Field3] = DateAdd("d", -30, Me![Field2])
    Case "2 Months"
      Me![Field3] = DateAdd("d", -60, Me![Field2])
    Case "3 Months"
      Me![Field3] = DateAdd("d", -90, Me![Field2])
    Case Else
      'do nothing
  End Select
End If
End Sub

[NeoPa]

I'm not even SURPRISED ;)
Checking that the input conforms to the required format is always a good thing to do thoroughly though. Nice addition.

[ADezii]

I'm not even SURPRISED ;)
Checking that the input conforms to the required format is always a good thing to do thoroughly though. Nice addition.

I'm not even SURPRISED

I guess after all this time nothing surprises you, right NeoPa (LOL).

[NeoPa]

Your craziness? - certainly not ADezii :D

BTW Was that an age dig? (I guess, after all this time, ...)
I'm not old!! I'm a very young 47 - LOL.