Results 1 to 5 of 5
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209

    Query To Return All Records From Today

    I have a linked SQL Server table into my access database. the field is called saleDT and it is a date time field. What would the syntax of my query be in my access query to return all records for todays date? I tried the below but it returned 0 results


    Code:
    Select saleDT, * FROM importedData Where SimportDT = Date()

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Field likely has time component so value will never = Date().

    Extract just date (mm/dd/yyyy) from value.

    For an Access date/time field, use DateValue([fieldname]). Not sure if that will work with SQLServer field. The extraction might have to be done on the SQLServer side.
    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.

  3. #3
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209
    Unfortunately using DateValue([fieldname]) does not work, it also returns 0 rows.

    If it's not possible to do using the query GUI - is it possible to do via VBA? Unfortunately I can't modify the SQL Server table structure...

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can you set up a view in the SQLServer? Then have Access pull from that view?

    I don't use SQLServer, only read about views and pass-through queries as tools for Access interaction with SQLServer. I have also read that Access manipulating SQLServer date/time type fields can be difficult.

    Could try: [SimportDT] BETWEEN Date() & " 00:00:00" AND Date() & " 23:59:59"
    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.

  5. #5
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209
    Hah - that got it.

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

Similar Threads

  1. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  2. Replies: 5
    Last Post: 10-12-2012, 11:00 AM
  3. Replies: 4
    Last Post: 03-23-2012, 01:18 PM
  4. query doesn't return anything for some records
    By dylan_dog in forum Queries
    Replies: 3
    Last Post: 02-08-2012, 09:55 PM
  5. Return all records from Query
    By ysrini in forum Queries
    Replies: 1
    Last Post: 01-15-2010, 09:52 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