Solved Comparing dates of 2 fields and message box (1 Viewer)

[AnilBagga]

I have not used any message boxes in forms, only validations with inbuilt validation text. This time I want a customised message display without any validation!

I have 2 bound date fields in a table - A and B. The form is bound to a query and a date C is available from another table

When the user enters dates A and B in form, I want it to check date B such B<= A-C-10 and if not, I need a message box to pop up. I dont want any validation

I entered the following code in AfterUpdate of B

If me.B < (me.A - me.C - 10) Then
Msgbox "Check B date entered !"
Endif

I also tried to replace Msgbox with Debug.print

I dont get any syntax error but do not get any message display either

Where am I going wrong?

 

[bastanu]

Hi Anil,

Are all three dates populated? Put a break in the code and hover the mouse over each to see what you have.

You say date C is available from another table and yet you use Me.C, is C using a dlookup of some sort to get the value of date C into the Me.C control? Straight substarction as you have it works for number of days, but you could use the DateDiff function.

Cheers,
Vlad

 

[AnilBagga]

Hi Vlad,

There is query in data source of the form to link tables 1 and 2. A and B come from Table 1 and C from Table 2. Therefore I have use me.C.

I made a mistake. C is a number, not a date!

"Put a break in the code and hover the mouse over each to see what you have." I am sorry I am not able to follow this


If A = 10th Feb 2021
B = 10th Jan 2021 and C = 25

Then what we need to check is if A-B < C+10 or 35 in the example above

Would the correct argument therefore be

If datediff ("d", [A], ) < ([C]+10) Then
Msgbox "Check B date entered !"
me.B.setfocus
Endif

 

[AnilBagga]

I added the code as below, does not work

Private Sub EXWTargetDate_AfterUpdate()
If DateDiff("d", [DelyReqd], [EXWTargetDate]) < ([TransitTime] + 10) Then
Debug.Print "Recheck EXWTargetDate!"
Me.EXWTargetDate.SetFocus

End If

End Sub

 

[bastanu]

Hi Anil,
Could you please try:

Private Sub EXWTargetDate_AfterUpdate()
'check for empty date 
if Isnull(Me.EXWTargetDate) then Exit Sub
'Option 1
If (Me.DelyReq-Me.EXWTargetDate)< (Me.TransitTime + 10) Then
       'Debug.Print "Recheck EXWTargetDate!"
       Msgbox "Recheck EXWTargetDate!"
        Me.EXWTargetDate.SetFocus
End If
'Option 2
If DateDiff("d", Me.DelyReq,Me.EXWTargetDate)< (Me.TransitTime + 10) Then
       'Debug.Print "Recheck EXWTargetDate!"
       Msgbox "Recheck EXWTargetDate!"
        Me.EXWTargetDate.SetFocus
End If
End Sub

Check the controls names to match the code please.

Cheers,
Vlad

 

[MajP]

FYI,
You do not need datediff. Days are stored as floating point (I think a single) where the integer portion is the day and the decimal portion is the time as fraction of a day.
Today is 44180
and today + 10 is 44190
and Cdate(44190) = 12/25/2020

So that is likely not your problem.

 

[AnilBagga]

Hi Anil,
Could you please try:

Private Sub EXWTargetDate_AfterUpdate()
'check for empty date
if Isnull(Me.EXWTargetDate) then Exit Sub
'Option 1
If (Me.DelyReq-Me.EXWTargetDate)< (Me.TransitTime + 10) Then
       'Debug.Print "Recheck EXWTargetDate!"
       Msgbox "Recheck EXWTargetDate!"
        Me.EXWTargetDate.SetFocus
End If
'Option 2
If DateDiff("d", Me.DelyReq,Me.EXWTargetDate)< (Me.TransitTime + 10) Then
       'Debug.Print "Recheck EXWTargetDate!"
       Msgbox "Recheck EXWTargetDate!"
        Me.EXWTargetDate.SetFocus
End If
End Sub

Check the controls names to match the code please.

Cheers,
Vlad

I dont know what mistake I am making

I created a new TestDB when it was not working in my DB. The Test DB is enclosed. Cannot get it to work!

 

[bastanu]

Can you please check this updated file?

 

[AnilBagga]

Can you please check this updated file?

Hi Vlad

This is some other file, not mine. Which of the forms should I look at. I tried to see the design of each , but could not see an event similar to mine

 

[bastanu]

Sorry Anil!

Cheers,
Vlad

 

[AnilBagga]

Sorry Anil!

Cheers,
Vlad

Hi Vlad

The message box pops up in every condition

1. I kept C as 0 and entered dates of 12/Feb/21 as A and 12/Jan/21 as B. Diff is 30 days. With C being 0, msg box should not pop up and control should go to next record. The control goes to A and msg box pops up! I tested with positive C values too. It does not work

If (Me.A - Me.B) < (Me.C + 10)

2. Why do we keep both options in Events active - Msgbox and Debug.print with different IF conditions! I deactivated one to test. It works

3. I added an Else statement as shown in enclosed DB and the msgbox works fine and IF statement works as designed, but if it pops up pressing OK does not bring control to B as it should! The control goes to new record.

4. If the IF condition is not activated, then control goes to new record as it should (last item in the tab order). If I use this in a the actual scenario of my DB, the control should go to next tab control if EXW date is OK

5. I tried deactivating the check for empty B, but does not help!

 

[bastanu]

Hi Anil,
Please have a look at this update. I moved the code to the BeforeUpdate event as that has a cancel argument you can use to make user stay on the control.

 

[AnilBagga]

Hi Anil,
Please have a look at this update. I moved the code to the BeforeUpdate event as that has a cancel argument you can use to make user stay on the control.

Hi Vlad,

This is perfect.

For my learning, maybe I will need this in future, can we modify the message box such that the user can decide if he wants to amend the date or move to next field. Some thing like

"EXW Date should be earlier! Would you like to correct it? Yes/No"

If he types Y, control remains on B and if he types N, control goes to next field.

 

[bastanu]

Hi Anil,
Of course, look at the msgbox function:https://support.microsoft.com/en-us/office/msgbox-function-e23511c8-4157-4e09-93a6-ba9f1749f4c0

Basically you would have something like this:

If Msgbox ("EXW Date should be earlier! Would you like to correct it? Choose Yes to correct it or No to ignore.",vbYesNo,"ConfirmEXW Date")=vbYes then

    Cancel=True

End if

 

[AnilBagga]

Hi Anil,
Of course, look at the msgbox function:https://support.microsoft.com/en-us/office/msgbox-function-e23511c8-4157-4e09-93a6-ba9f1749f4c0

Basically you would have something like this:

If Msgbox ("EXW Date should be earlier! Would you like to correct it? Choose Yes to correct it or No to ignore.",vbYesNo,"ConfirmEXW Date")=vbYes then

    Cancel=True

End if

Perfect! Looks so easy once you see it