date not working

[vlitim]

I am using the below code, so that an item is only displayed if completed within the last month. But it doesn't work, no errors are shown but the dates older than a month still show up. Can anyone shed some light on this!?

datenow = DateAdd("m", -1, Date)
sqlString = "active=1 And completedDate >= " & datenow & ""

Cheers
Tim

 

[shaddow]

The code is just ok but...
If u are using ACESS as database
use
datenow = DateAdd("m", -1, Date)
sqlString = "active=1 And completedDate >= #" & datenow & "#"
SQL database
use
datenow = DateAdd("m", -1, Date)
sqlString = "active=1 And completedDate >= '" & datenow & "'"
________
George, M

 

[vlitim]

I am using SQL and tried your suggestion but still it has no effect on the results for some reason

 

[shaddow]

Have u print the datenow value to see if it's ok?
________
George, M

 

[vlitim]

yep the date value is correct

 

[shaddow]

U need to check your final querry stored in sqlString in Querry Analizer to see if works...
select * ... and active=1 And completedDate >= '08/01/2001'
________
George, M

 

[chiph]

I've had the best luck with comparing dates when I use the BETWEEN operator. Try something like:
[tt]
WHERE Active<>0
AND completedDate BETWEEN '31-Dec-1899' AND datenow
[/tt]

Chip H.

BTW: I changed your test for the Active flag to &quot;<> 0&quot;, rather than checking for it to be equal to 1. In all lnguages I'm aware of, FALSE is always 0. But in many languages the &quot;TRUE&quot; condition is defined as being &quot;NOT FALSE&quot;, which could mean that True is a positive 1, or maybe a negative one, or even a MAX_INT. By checking for a non-zero, you'll never have to worry about being tripped up by it.

 

[vlitim]

Thanks for all the help have managed to figure it out now, its was the SQL date setting was different to what I was comparing against