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

DateDiff

geraldinegrieve
P: 14
I have a table in access that holds data on vehicle there are 3 fields holding dates on MOT, Tax and Insurance renewal I am looking for 3 different messages on opening if date of any is within next 2 weeks e.g.
Expand|Select|Wrap|Line Numbers
  1. If DateDiff("d", Me.MOT_Renewal_Date, Now()) < 15 Then
  2.     MsgBox "Vehicles Due MOT Renewal", , "Renewal Due"
  3.     If DateDiff("d", Me.Tax_Renewal_Date, Now()) < 15 Then
  4.     MsgBox "Vehicles Due Tax Renewal", , "Renewal Due"
  5.     If DateDiff("d", Me.Insurance_Renewal_Date, Now()) < 15 Then
  6.     MsgBox "Vehicles Due Insurance Renewal", , "Renewal Due"
  7.  
  8. End If
  9.  
now i have tried various variations of this but all messages appear at all times regardless of dates any help greatly apprecipated Thanks in advance
Dec 12 '08 #1
Share this Question
Share on Google+
9 Replies


geraldinegrieve
P: 14
I have now tried this in a macro and works ok if 1st record holds the true data but not if it is the 2nd or any other record e.g.
Condition ([tax_renewal_Date]-Now())<15 Action Message Box
Condition ([MOT_renewal_Date]-Now())<15 Action Message Box
Condition ([Insurance_renewal_Date]-Now())<15 Action Message Box SO CLOSE but not quite correct
Dec 12 '08 #2

MindBender77
100+
P: 234
@geraldinegrieve
Have you tried using "ElseIf". I added this to the code you provided. I believe this should correct or issue but, is just air code.

Note: You could also make 3 IF statements for each condition as well.

HTH,
Bender
Dec 12 '08 #3

geraldinegrieve
P: 14
Thanks for replying so quickly I did try an Else if but only does 1st message if true and stops and if 1st message not true then 2nd gets displayed and stops. I am not clear on your advice 3 seperate if statements because i did try that but all messages at all dates appeared Thanks in advance
Dec 12 '08 #4

MindBender77
100+
P: 234
@geraldinegrieve
Here is making the 3 IF statements. No matter if the first if is true or false, it will then move to the next IF. The same will happen for the second IF etc.

Bender
Dec 12 '08 #5

geraldinegrieve
P: 14
That was also my understanding of it, but maybe I am putting it in the wrong place i have it in the Form oncurrent and it is not taking the criteria into consideration it is giving back all 3 messages regardless of dates
Dec 12 '08 #6

MindBender77
100+
P: 234
If that is the case, it seems your DateDiff statement may be to blame. Have you tried debugging line by line to see how the criteria is being handled? Also, this might do nothing but, in your DateDiff statement, remove the empty parenthesis after Now. Without knowing what your project does, the OnCurrent should be fine.

So something like this:
DateDiff("d", Date1, Now) < 15

HTH,
Bender
Dec 12 '08 #7

geraldinegrieve
P: 14
Thanks a million for this I got it working so as if criteria is on first record then it gives message but if criteria is in any other record message doesn't appear until you click into the record so so close
Dec 15 '08 #8

missinglinq
Expert 2.5K+
P: 3,532
if criteria is on first record then it gives message but if criteria is in any other record message doesn't appear until you click into the record
This would indicate that you're dealing with a Datasheet or Continuous View form, in which case it is only going to work as you've just described. The Form_Current event fires as you move to a record, so the warning for a given record won't pop up until you move the focus to that record. .

An alternative, for a Continuous or Datasheet view, would be to use Conditional Formatting to change the background color of the appropriate textboxes to indicate that the fee is due.

Welcome to Bytes!

Linq ;0)>
Dec 15 '08 #9

geraldinegrieve
P: 14
Thanks I already have conditional formating just wanted the message as form opened but thanks a million for that info would make perfect sense
Dec 16 '08 #10

Post your reply

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