448,973 Members | 1,528 Online
Need help? Post your question and get tips & solutions from a community of 448,973 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
8 Replies

 Expert Mod 15k+ P: 31,662 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 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 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

 Expert 5K+ P: 8,675 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 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 Dec 15 '07 #4

 Expert Mod 15k+ P: 31,662 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

 Expert 5K+ P: 8,675 In the AfterUpdate() Event of Field1: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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 Dec 15 '07 #6

 Expert Mod 15k+ P: 31,662 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

 Expert 5K+ P: 8,675 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

 Expert Mod 15k+ P: 31,662 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