Results 1 to 3 of 3
  1. #1
    btappan is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    43

    limit query results to last 30 days

    I am querying a linked ODBC table with a date column called "db_created_tms" that has dates in formatted like "2/25/2004 8:54:02 PM" , becasue it is a linked table I can't edit the data type. How can I limit my query results to the last 30 days.

    my query without the date limit currently looks like this:

    SELECT QBReportAdminGroup_v_txn_po_line.doc_num_h AS po, QBReportAdminGroup_v_txn_po_line.quantity_qnty AS qty, QBReportAdminGroup_v_lst_item.name AS REFERENCE, QBReportAdminGroup_v_txn_po_line.unit_price_amt AS cost, QBReportAdminGroup_v_lst_vendorODBC.name AS vendor, "" AS LOCATION
    FROM (QBReportAdminGroup_v_txn_po_line INNER JOIN QBReportAdminGroup_v_lst_item ON QBReportAdminGroup_v_txn_po_line.item_id = QBReportAdminGroup_v_lst_item.id) INNER JOIN QBReportAdminGroup_v_lst_vendorODBC ON QBReportAdminGroup_v_txn_po_line.vendor_id = QBReportAdminGroup_v_lst_vendorODBC.id;

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try adding:

    WHERE DateValue(db_created_tms) Between Date() - 30 And Date()
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That date field is a text type?

    Assuming no dates later than the current date exist, try:

    WHERE CDate(db_created_tms) > Date()-30
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 13
    Last Post: 11-17-2013, 03:33 PM
  2. Replies: 3
    Last Post: 07-18-2012, 10:08 PM
  3. Limit Results
    By hithere in forum Queries
    Replies: 4
    Last Post: 02-23-2012, 02:13 PM
  4. Limit Query Results
    By ocampod in forum Queries
    Replies: 5
    Last Post: 01-07-2011, 10:47 AM
  5. Limit Query results to 5 contacts per site
    By wobvintage3 in forum Queries
    Replies: 2
    Last Post: 12-01-2010, 12:23 PM

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