my computer cant compare dates

[alaric]

Hi,

Im confused about the results a report is showing.
I open the report with a filter;

Dim strWhereCondition As String
Dim dtDatum As Date
dtDatum = Date - gbtBetaalTermijn
strWhereCondition = "Betaald = 0 AND DatumBetAangemaakt < #" & dtDatum & "#"
DoCmd.OpenReport "RBetalingen", acPreview, , strWhereCondition​

What happens is:
when the variable dtDatum = 2-1-2005. It should NOT return the records > 2-1-2005.
BUT is DOES show records in the report like 4-1-2005 .

in the immediate window:
?strWhereCondition
Betaald = 0 AND DatumBetAangemaakt < #02-01-2005#

?#4-1-2005# < #2-1-2005#
False (ofcourse)

What Im I doing wrong?

Appreciate your thoughts

Al

 

[llkhoutx]

4-1-05 is not an standard access date format, it should be in the format 4/1/05.

Note also that Access date datatypes can incluide a time component. The Date() function store a mere date; the Now() function stpores a date with a time of day component being less than 1. Two dates, one with a time component will not compare exactly.

 

[Rich]

Try < #" & Format(dtDatum,"mm\/dd\/yyyy") &

it will be interesting to see if it takes care of the Regional setting date separator issue

 

[Pat Hartman]

dtDatum = Date - gbtBetaalTermijn

If you want to add or subtract some number of days to a date, use the DateAdd() function.

 

[alaric]

thank you guys

following did the job

Dim dtDateNow, dtDatum As Date
dtDateNow = Date

Dim strWhereCondition As String

dtDatum = DateAdd("d", dtDateNow, "-" & gbtBetaalTermijn)
strWhereCondition = "[Betaald] = 0 AND [DatumBetAangemaakt] < #" & dtDatum & "#"

DoCmd.OpenReport "RBetalingen", acPreview, , strWhereCondition

THX
Al