By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Comparison Operator for Date

P: n/a

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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
and there's always BETWEEN

"nectar" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

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

Nov 12 '05 #2

P: n/a
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" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

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

Nov 12 '05 #3

P: n/a

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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.