Comparison Operator for Date

[nectar]

Hello



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", "([OfferDate] >= #" & strDate1 & "#) AND (#" &
strDate2 & "# >= [OfferDate])")



Any idea? Thanks in advance!

nectar


--
Posted via http://dbforums.com

[Mike MacSween]

Re: Comparison Operator for Date

and there's always BETWEEN

"nectar" <member40324@db forums.com> wrote in message
news:3389331.10 63957881@dbforu ms.com...[color=blue]
>
> Hello
>
>
>
> 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", "([OfferDate] >= #" & strDate1 & "#) AND (#" &
> strDate2 & "# >= [OfferDate])")
>
>
>
> Any idea? Thanks in advance!
>
> nectar
>
>
> --
> Posted via http://dbforums.com[/color]

[Douglas J. Steele]

Re: Comparison Operator for Date

Your values probably have time in them as well as the date (are you using
the Now() function to populate them?) Date/Time values are stored as 8 byte
floating point numbers, where the integer part represents the date as the
number of days relative to 30 Dec, 1899, and the decimal part represents the
time as a fraction of a day. If all you specific is a date, it's assumed to
be midnight of that day, and any records for that day which include a time
will be greater than just the date.

You can use the DateValue function to remove the time from your values, or
you can just use one day higher in your comparison.

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

"nectar" <member40324@db forums.com> wrote in message
news:3389331.10 63957881@dbforu ms.com...[color=blue]
>
> Hello
>
>
>
> 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", "([OfferDate] >= #" & strDate1 & "#) AND (#" &
> strDate2 & "# >= [OfferDate])")
>
>
>
> Any idea? Thanks in advance!
>
> nectar
>
>
> --
> Posted via http://dbforums.com[/color]

[nectar]

Re: Comparison Operator for Date


MIKE, thanks very much for your input.



Here is my answers:



- strDate comes from a String field from a form. Before using it in the
DCount I do strDate1 = Format(Me!Date1 , "mm/dd/yyyy"). I tested it is
a date and got back a true...

- Actually it's >= AND <=. Just that the memebers of the inequality
are inverse

- I already tried BETWEEN and it didn't work.


--
Posted via http://dbforums.com