Comparison Operator for Date

[nectar]

Hi

In VBA I need to count records from an Offers table, that were created beetwen 2 dates (strDate1 and strDate2).

Using the DCount as follow gives me strange results: records that were created ON strDate1 are taken in account while records that were created ON strDate2 are not. Records that were created before strDate2 are taken in account.

In others words, it works fine unless that it doesn't take the = part of the second operator.

DCount("*", "Offers", "([Date] >= #" & strDate1 & "#) AND (#" & strDate2 & "# >= [Date])")

Any idea? Thanks in advance!
nectar

 

[Rich]

Date is a reserved word in Access, you should re-name the field

 

[nectar]

Rich,

Many thanks for the hint. I did rename the field, however it doesn't make any difference for the problem described in the post!

 

[Mile-O]

[MyDate] Between #" & strDate1 & "# And #" & strDate2 & "#"

 

[nectar]

It does exactly the same behavior!! The upper limit is one day less than it should be...

The only other thing I do before is to format the date with:

strDate1 = Format(Me!Date1, "mm/dd/yyyy")

As a matter of fact here in belgium we have date as dd/mm/yyyy. I've read somewhere that I should use american style date.