Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Trunc alternative in Access

    Below query works correctly in Oracle,but it gives error when run in the access as 'Undefined function 'trunc' in expression'.The trunc function in this query displays the date and hour as '12/9/2009 1:00:00 AM'.which function in access supports this type of calculation.

    SELECT count(order_id), trunc(ORDER_DATE,'HH'),sum(subtotal)
    FROM order_vw
    WHERE (ORDER_DATE between to_date('12/9/2009 00:00:01','MM/DD/YYYY HH24:MI:SS')
    and to_date('12/9/2009 23:59:59','MM/DD/YYYY HH24:MI:SS'))


    group by trunc(ORDER_DATE,'HH')
    order by trunc(ORDER_DATE,'HH')

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Maybe this will work for you: Format(Me.YourDate, "mm/dd/yyyy hh:mm:ss")

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Your suggestion works but it is displaying every donation per second
    I need it to be displayed for every hour.Suggest me a modified query.

    SELECT usertrack_plt, format(ORDER_DATE,'mm/dd/YYYY hh:mm:ss') as [Date and Hr] , count(order_id) as [Orders], sum(subtotal) as [Donations] FROM amounts_prod WHERE (ORDER_DATE >= #2009-12-10 00:00:00# and ORDER_DATE <= #2009-12-11 00:00:00#) AND (usertrack_plt IN ('STJGENLKALSAC1000001','STJGENLKALSAC1000002','ST JGENLKALSAC1000003')) group by format(ORDER_DATE,'mm/dd/YYYY hh:mm:ss'), usertrack_plt order by format(ORDER_DATE,'mm/dd/YYYY hh:mm:ss')

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Maybe someone that is better at queries than I will drop by.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Alternative to Join Property???
    By arthura in forum Queries
    Replies: 1
    Last Post: 05-22-2009, 12:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums