0

I'm trying to find out why the below doesn't work. What I'm trying to do is if the TnCReviewDate is greater than a year in the past, then output a messagebox. I tried to use the DateDiff function to facilitate this, but it's not working. D2 is the current date and d1 is supposed to be one year in the past, and diff is the comparison between the two which should be a year. If the TnCReviewDate is newer than a year, nothing should happen. What am I doing wrong?

dim d1, d2, diff

    d1 = DateAdd("y",-1,d2)
    d2 = now()

    diff = DateDiff("y", d1, d2)

    if SystemVariables.CodeObject.Company = "T" then
        if AdditonalFields.CodeObject.TnCReviewDate > diff and (AdditonalFields.CodeObject.KeyAccount = "Y") then
            msgbox "Please review T & C for this customer!"
            else
            ' do nothing
        end if
    end if
majinvegito123
  • 25
  • 5
  • You want to use [`DateDiff()`](https://learn.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/scripting-articles/xhtyw595(v=vs.84)) to compare two dates and return an "interval" (days, months, years etc.). – user692942 Nov 30 '22 at 15:45
  • Does this answer your question? [VBScript DateDiff month](https://stackoverflow.com/a/27034831) – user692942 Nov 30 '22 at 15:47
  • Btw, the equivalent of `GETDATE()` in VBScript is the [`Now()`](https://learn.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/scripting-articles/0w568awd(v=vs.84)) function. – user692942 Nov 30 '22 at 15:55
  • Okay, I understand that @user692942 but I'm struggling to construct the correct script. Can you help me with it? – majinvegito123 Nov 30 '22 at 16:10
  • There are two listed examples that should help you get closer, when you have a specific issue [edit] the question to clarify what isn't working then we will help further. VBS and VBA are very similar which is why I find it hard to believe if you use VBA how you didn't know `Now()` is how you get a date and time not `GETDATE()`. – user692942 Nov 30 '22 at 16:46
  • @user692942 I edited the code to show what isn't working. Hope this helps. – majinvegito123 Nov 30 '22 at 16:57
  • Have you checked the value of `diff` to see what it contains? I think you have your dates the wrong way around which will be generating a negative value. This kind of problem is easily rectified by simple debugging techniques like checking the value of the `diff` variable in your script. – user692942 Nov 30 '22 at 17:58
  • @user692942 diff will become a number like 1 or 2 which II can see being a problem. So I changed diff to use DateAdd("y",-1, now()) and it still didn't work – majinvegito123 Nov 30 '22 at 19:03
  • Why is that a problem? `DateDiff()` works by comparing two dates and giving back the interval requested, which will be a positive or negative number depending on which way around the dates are compared. You are not explaining yourself very well. – user692942 Nov 30 '22 at 19:18
  • Also not sure what `d1 = DateAdd("y",-1,d2)` is doing as `d2` isn't set until after this line? You need to move `d2 = now()` above that line. A lot of this just comes down to debugging your values as you go, i.e. `MsgBox("d2 = " & d2)`. – user692942 Nov 30 '22 at 20:00
  • @user69242 so I was really just being ignorant. I didn't need to do any of that. I solved it with 'if AdditonalFields.CodeObject.TnCReviewDate > DateAdd("yyyy",-1,now())' the thing that made it wrong the whole time was that i thought "y" was year instead of "yyyy".... – majinvegito123 Nov 30 '22 at 20:38
  • Yeah `y` is "Day of year" and `yyyy` is "Year". – user692942 Nov 30 '22 at 20:56

0 Answers0