DCount and Date Problem 
Author Message
 DCount and Date Problem

I have a table that has records using the date in the format of 02/12/2000
11:30 PM.  I am trying to create a field which will count the number of all
records that are present for todays date.

I have tried a number of formats but cant get it.

The latest code is DCount("[OCA]","TblTable1", Format([Date], "Short Date")
= Date())

This does not work.  Any Ideas or help would be greatly appreciated.

Thanks in advance.

Pete



Sun, 04 Aug 2002 03:00:00 GMT  
 DCount and Date Problem
Try this:
DCount("[OCA]","TblTable1", "[date]='" & format(date(),"mm/dd/yy") & "'")

Quote:
> I have a table that has records using the date in the format of 02/12/2000
> 11:30 PM.  I am trying to create a field which will count the number of
all
> records that are present for todays date.

> I have tried a number of formats but cant get it.

> The latest code is DCount("[OCA]","TblTable1", Format([Date], "Short
Date")
> = Date())

> This does not work.  Any Ideas or help would be greatly appreciated.

> Thanks in advance.

> Pete



Sun, 04 Aug 2002 03:00:00 GMT  
 DCount and Date Problem
You don't want to use the format function with the DCount function.

The following code would provide a count of all records with Today's date.
DCount("[OCA]","TblTable1", "[Date]=" & Date())

To get the count of all records for the first day of the new millenium:
DCount("[OCA]","TblTable1", "[Date]=" & "1/1/2000")

Does this help?

Brian Beers

Quote:

>I have a table that has records using the date in the format of 02/12/2000
>11:30 PM.  I am trying to create a field which will count the number of all
>records that are present for todays date.

>I have tried a number of formats but cant get it.

>The latest code is DCount("[OCA]","TblTable1", Format([Date], "Short Date")
>= Date())

>This does not work.  Any Ideas or help would be greatly appreciated.

>Thanks in advance.

>Pete



Sun, 04 Aug 2002 03:00:00 GMT  
 DCount and Date Problem
This should work if the format in the table is mm/dd/yyyy  but it does not
in this instance.  The date stored in the table is mm/dd/yyyy hh:mm AM and
no records are pulled in from the query.

DCount("[OCA]","TblTable1", "[Date]=" & Date())

Thanks again.

Pete



Sun, 04 Aug 2002 03:00:00 GMT  
 DCount and Date Problem
You need to strip off the time values when making your comparison. You
can do this with DateValue or simply with Int. Also, you need to
enclose literal dates within pound signs (#):
DCount("[OCA]","TblTable1", "DateValue([MyDate])=#" & Date() & "#")

If you are in a locale where Date() returns day-month-year rather than
month-day-year, then you need to handle that as well, since Jet is
expecting month-day.

 -- Andy

Quote:

>This should work if the format in the table is mm/dd/yyyy  but it does not
>in this instance.  The date stored in the table is mm/dd/yyyy hh:mm AM and
>no records are pulled in from the query.

>DCount("[OCA]","TblTable1", "[Date]=" & Date())

>Thanks again.

>Pete



Mon, 05 Aug 2002 03:00:00 GMT  
 DCount and Date Problem
Got it.  Thanks to everyone that responded.

Pete



Mon, 05 Aug 2002 03:00:00 GMT  
 DCount and Date Problem


Quote:
>DCount("[OCA]","TblTable1", Format([Date], "Short Date")= Date())

Thinking about the comparison: you are comparing a string value --
format() -- with a date value. This relies on coercion between types
and conversions that you cannot control.

Try doing the comparison between strings:

  Format$([MyDate], "yyyy-mm-dd")=Format$(Date(), "yyyy-mm-dd")

or between numbers:

  CLng([MyDate]) = CLng(Date())

or between dates:

  DateValue([MyDate]) = DateValue(Date())

The forcing of Date() in the last two cases is probably unneccessary,
but in future versions of VBA, Date() may return a time value as
well... Best to be sure, I say. Be sure always to use the first one
when comparing time values, as very strange things happen in double
precision land :-)

HTH

Tim F

--



Tue, 06 Aug 2002 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. DCOUNT divided by DCOUNT

2. Help with DCOUNT overflow problem

3. Dcount VBA syntax problem

4. DCount problem

5. DCount - Problems

6. Problems with DCount function

7. MS-ACCESS query date problem + date formatting in datagrid

8. problems with VB date --> Access date

9. start date/time, end date/time problem

10. Dcount function - need help with the criteria part!

11. dcount or counting records code?

12. Dsum and Dcount syntax

 

 
Powered by phpBB® Forum Software