Results 1 to 13 of 13

Thread: Date (97)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date (97)

    5/6/02

    The following is a WHERE statement....it works fine.
    ...........this will give me the year
    WHERE (Year([Date])=Year(Now()))
    ...........this will give me the month
    (Month([Date])=Month(Now()))
    ...........what gives me the DAY?

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date (97)

    As you might guess, Day is a VB function just like Month and Year.

    But I suppose there is a question behind this?

  3. #3
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date (97)

    You don't really have a field named Date, do you?

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date (97)

    No....the field name is really...AssociateCallDate.

    I want to find the number of calls a person makes on each day.

    I'm currently using the count function for AssociateCallDate to retrieve the number of calls each associate makes...I want to take this a step further and track it by DAY.. If I use the above functions, I can get the count for the month, I can get the count for the year, but I can't get count for the day.

    this is what I'm using and it doesn't work:

    Day([AssociateCallDate]) and in the criteria section I have Day(Date())...it returns nothing....and I have the Where as the total....

    Any suggestions?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date (97)

    Can't you just use:
    WHERE [YourDateField] = DateValue(Now())

    HTH <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date (97)

    NOPE.....still it returns nothing....if I put 5/6/02 in the crieteria, it returns records, if I put that code in...it returns nothing....

    I'm open to more suggestions.....thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date (97)

    =Date() in the criteria for [AssociateCallDate],leave out the Day

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date (97)

    Are you running this code from a form or in a query?

    It could be that you need to surround your date with #...#

    Such as WHERE [YourDate] = #" & DateValue(Now()) & "#"

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date (97)

    Now() returns a date and time. All you want to compare is a short date like 5/6/02, not 5/6/02 19:17.33. You'll have to either use Date(), assuming it works properly for you, or you'll need to format the date field as a short date, which you can do in the field properties of the query.
    Charlotte

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date (97)

    I was trying to do something similar with a date/time field used as a timestamp. When user saves record value is set to "Now()" so includes both date and time data. Using DateValue function to run totals query that groups records processed by date works fine (have to exclude null values). But had all kinds of problems if tried to set criteria = current date. Only way was able to make this work like was:

    SELECT DateValue([DATE_UPDTD]) AS [DATE UPDATED], Count(TABLENAME.SERIAL_NO) AS PROCESSED
    FROM TABLENAME
    WHERE (((TABLENAME.DATE_UPDTD) Is Not Null))
    GROUP BY DateValue([DATE_UPDTD])
    HAVING (((DateValue([DATE_UPDTD])) Like CStr(Date())))
    ORDER BY DateValue([DATE_UPDTD]) DESC;

    where DATE_UPDTD is the date/time field used as timestamp. Using criteria such as "Date()", "DateValue(Now())", etc did not work. (Formatted field as Short Date which is format DateValue allegedly returns.) Even CStr(Date()) w/o "Like" operator did not work. These other expressions all resulted in "Data type mismatch in criteria expression" error. This tends to suggest DateValue returns string not date variant as advertised?? Maybe someone knows a simpler way to select current date with date/time field with both date & time info.

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date (97)

    In further reply, a simpler approach may be to use Format function, ex:

    UPDATE: Format([DATE_UPDTD],"Short Date")

    Then simply entering "Date()" in criteria will return current date's records. Specify "Short Date" format for field to return actual date value rather than string equivalent (values will be right-aligned in datasheeet view).
    HTH

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date (97)

    Thanks everyone for your efforts. I've tried each suggestion but none of them work.

    After reading the post, I'm afraid we got off of the original post.
    In a TOTALS QRY:

    Field: Year([AssociateCallDate])
    Total: WHERE
    Criteria: Year(Now())
    ***********************************
    Count=Records Returned
    ***********************************

    Field: Month([AssociateCallDate])
    Total: WHERE
    Criteria: Month(Now())
    **************************************
    Count= Records Returned
    **************************************

    Field: Day([AssociateCallDate])
    Total: WHERE
    Criteria: Date(Now())
    **************************************
    Count= No records returned.
    **************************************

    Maybe this is more clear...and hopefully there are more suggestions....

    What code must I put in the get the count for the records for the DAY?

    Many thanks to all.....
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date (97)

    Still not sure if I'm clear on the concept but if the idea is to calculate how many calls each associate has made in current year, month, and date, then query similar to below example will provide totals grouped by associate for current year, month, & date:

    SELECT TBL_NAME.ASSOCIATE_ID, Count(TBL_NAME.CALLDATE) AS
    [YEAR TO DATE], Sum(IIf(Month([CALLDATE])=Month(Now()),1,0)) AS
    [MONTH TO DATE], Sum(IIf(Day([CALLDATE])=Day(Now()),1,0)) AS TODAY
    FROM TBL_NAME
    WHERE (((Year([CALLDATE]))=Year(Date())))
    GROUP BY TBL_NAME.ASSOCIATE_ID
    HAVING (((Count(TBL_NAME.CALLDATE)) Is Not Null))
    ORDER BY TBL_NAME.ASSOCIATE_ID;

    Above example uses conditional statements with Month and Day functions to calculate the month and day totals; since WHERE criteria = current year no need for conditional statement in YEAR TO DATE column.

    Note: In your Day criteria you have "Date(Now())"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •