By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Combining Expressions

P: 2
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!!!
Dec 14 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,273
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 :
Expand|Select|Wrap|Line Numbers
  1. Me.[Field 3] = DateDiff("d", intDays, Me.[Field 2])
Dec 14 '07 #2

P: 2
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 :
Expand|Select|Wrap|Line Numbers
  1. 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!!
Dec 15 '07 #3

ADezii
Expert 5K+
P: 8,607
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Field1_AfterUpdate()
  2. If Not IsNull(Me![Field1]) And Not IsNull(Me![Field2]) Then
  3.   Select Case Me![Field1]
  4.     Case "1 Month"
  5.       Me![Field3] = DateAdd("d", -30, Me![Field2])
  6.     Case "2 Months"
  7.       Me![Field3] = DateAdd("d", -60, Me![Field2])
  8.     Case "3 Months"
  9.       Me![Field3] = DateAdd("d", -90, Me![Field2])
  10.     Case Else
  11.       'do nothing
  12.   End Select
  13. End If
  14. End Sub
Dec 15 '07 #4

NeoPa
Expert Mod 15k+
P: 31,273
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 ;)).
Dec 15 '07 #5

ADezii
Expert 5K+
P: 8,607
In the AfterUpdate() Event of Field1:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Field1_AfterUpdate()
  2. If Not IsNull(Me![Field1]) And Not IsNull(Me![Field2]) Then
  3.   Select Case Me![Field1]
  4.     Case "1 Month"
  5.       Me![Field3] = DateAdd("d", -30, Me![Field2])
  6.     Case "2 Months"
  7.       Me![Field3] = DateAdd("d", -60, Me![Field2])
  8.     Case "3 Months"
  9.       Me![Field3] = DateAdd("d", -90, Me![Field2])
  10.     Case Else
  11.       'do nothing
  12.   End Select
  13. End If
  14. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Field1_AfterUpdate()
  2. If Not IsNull(Me![Field1]) And Not IsNull(Me![Field2]) And IsDate(Me![Field2]) Then
  3.   Select Case Me![Field1]
  4.     Case "1 Month"
  5.       Me![Field3] = DateAdd("d", -30, Me![Field2])
  6.     Case "2 Months"
  7.       Me![Field3] = DateAdd("d", -60, Me![Field2])
  8.     Case "3 Months"
  9.       Me![Field3] = DateAdd("d", -90, Me![Field2])
  10.     Case Else
  11.       'do nothing
  12.   End Select
  13. End If
  14. End Sub
Dec 15 '07 #6

NeoPa
Expert Mod 15k+
P: 31,273
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.
Dec 16 '07 #7

ADezii
Expert 5K+
P: 8,607
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).
Dec 16 '07 #8

NeoPa
Expert Mod 15k+
P: 31,273
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.
Dec 16 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.