Date Comparison Problem. 1

[Junior1544]

I need to write an if statment to compare dates...

I will attach the code shortly...

The problem I'm having is that it alwas reports false... I've tried reversing the logic...

I've even done it in the imediate window with the real dates... when I use the real dates it works fine... but when I use the variables it reports false...

I can't hard code dates because they are alwas changing and I'm not going to change the code for every item...

Thanks for taking a look at this.

btw, I'm using access 2k, and using dao recordsets...



Dim rst As dao.Recordset
Set rst = CurrentDb().OpenRecordset("SELECT UserPW.* FROM UserPW WHERE (((UserPW.UsrName)=CurrentUser()));&quot

If (rst!Date > Date - 80) And (rst!Date < Date - 90) Then
MsgBox &quot;You have &quot; & DateDiff(&quot;d&quot;, rst!Date, Date) & &quot; days to change your password.&quot;

DoCmd.Close
DoCmd.OpenForm &quot;Switchboard&quot;

end if


I forgot to say what i'm tring to do with the if statement...

I want it to check to see if the date reported by the recordset is between 80 and 90 days ago...

if there's a better way to do this let me know, thanks...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[VBslammer]

You are getting an empty recordset because your '=CurrentUser()' function is inside the SQL string. Try this:

Set rst = CurrentDb().OpenRecordset(&quot;SELECT UserPW.* FROM UserPW WHERE (((UserPW.UsrName)='&quot; & CurrentUser() & &quot;'));&quot


VBSlammer

 

[PaulBricker]

Before your
If (rst!Date....line add
rst.MoveFirst

See if that helps.

Paul

 

[Junior1544]

I have other code running at the same time within the procedure... all using the recordset... and every thing else comes up ok...

I just changed it and it's still not working right...

I also tried instead of using the recordset, I tried using variables that I hard coded just to see, and it's still not running my code when I put the variable date between the 10 days I've put in...

Any other idea's??


btw, Thank you for the thought... if I were looking at some one else's code I problably would have thought some thing similare...

Thanks...

--James

junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[VBslammer]

Forget the expression and use:

Dim intDiff As Integer

intDiff = DateDiff(&quot;d&quot;, Date, rst!Date)

If (intDiff >= 80) and (intDiff <=90) Then
'....do stuff
End If

VBSlammer

 

[VBslammer]

Actually I think I reversed the arguments if you're after a negative date:

intDiff = DateDiff(&quot;d&quot;, rst!Date, Date)


VBSlammer

 

[VBslammer]

And the rest:

If (intDiff >= -80) and (intDiff <= -90) Then
'....do stuff
End If
VBSlammer

 

[PaulBricker]

Sorry, you don't need the rst.MoveFirst statement.
I ran the code and found some strange behavior. First I couldn't get any reponse, just as you described. Then I changed the > in the first argument to < and the < in the second argument to >. This didn't do anything, but when I changed them back to the original operands, it worked. So then I modified it a little to use a date value and used this
dim rst...etc
dim dteHolder as Date
Set rst...etc
rst.MoveFirst
dteHolder = rst![Date]
If dteHolder > Date() - 80 And dteHolder < Date() - 90 Then
etc....

and that works. Not sure what's screwy but something is.

Paul

 

[VBslammer]

Arrg...better to test it first =(

If (intDiff <= -80) And (intDiff >= -90) Then
    '...do stuff      
End If

VBSlammer

 

[Junior1544]

this is the if statment that is working for me, thanks guys...

If (intDiff >= 80) And (intDiff < 90) Then
'do stuff.
end if
junior1544@jmjpc.net
Life is change. To deny change is to deny life.