Results 1 to 6 of 6
  1. #1
    pme4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    5

    Filtering by DateTime field in VBA and SQL

    G'day, I am looking to upsize an Access app moving the backend to SQL Server. I am having issues with two areas at the moment.



    I want an SQL query that displays records created today, I have been using this with the Access backend:
    Code:
    SELECT tblAudit_Trail.AuditTrailID, tblAudit_Trail.DateTime, tblAudit_Trail.UserID, qryStaff.[Full Name], tblAudit_Trail.FormName, tblAudit_Trail.Stream, tblAudit_Trail.ParentRecord, tblAudit_Trail.RecordID, tblAudit_Trail.FieldName, tblAudit_Trail.OldValue, tblAudit_Trail.NewValue, tblAudit_Trail.ActionTakenFROM tblAudit_Trail LEFT JOIN qryStaff ON tblAudit_Trail.UserID = qryStaff.ID
    WHERE (((tblAudit_Trail.DateTime)>"Date()-1"))
    ORDER BY tblAudit_Trail.DateTime DESC;
    But I get and "Internal Error" if I try to use the Date() function.

    In addition, I have a search function that uses the following:
    Code:
    strWhere = "[DateTime] BETWEEN #" & Format(.txtFromDate.Value - 1, "mm/dd/yyyy") & "# And #" & Format(.txtToDate.Value + 1, "mm/dd/yyyy") & "#"
    This returns an error of "Search Key was not found in any records". I can't find anything to provide guidance on how to fix these issues.

    The SQL does work if I hard code dates in and the SQL Server data types are datetime.

    Thanks

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You would not have quotes around the date() -1 ?

    Debug.Print strWhere
    You are not showing all the code, so I hope that is within a With Me block?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    pme4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    5
    Thanks for responding Welshgasman.

    Removing the quotes around the date() -1 I get a type mismatch error.

    With the Where statement, I apply that as a filter to a recordset.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    What SQL driver are you using? Open the linked SQL table in design view in Access and confirm that the data type shows as Date\Time.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Assuming your date time field contains times then Date()-1 will give you yesterdays records as well

    are you running this as an access query or a pass through query? If the latter then date() won’t be recognised. Check out the sql server getdate function

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    if it is a pass-through query executed on SQL server the WHERE clause :

    Where Mydate >= dateadd(day, -1 ,cast(getdate() as date) )

    gives all records from yesterday and today

    Where MyDate >= cast(getdate() as date)

    gives all records entered today
    Last edited by NoellaG; 07-30-2022 at 09:13 AM. Reason: typo

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

Similar Threads

  1. Replies: 5
    Last Post: 01-05-2022, 06:04 PM
  2. DLookup on a DateTime field
    By rjackson in forum Queries
    Replies: 1
    Last Post: 08-22-2016, 07:39 PM
  3. Replies: 1
    Last Post: 08-15-2016, 10:29 PM
  4. Conditional Formatting on datetime field
    By Delta729 in forum Access
    Replies: 3
    Last Post: 05-06-2015, 03:37 PM
  5. Help with datetime field in subform
    By Delta729 in forum Access
    Replies: 3
    Last Post: 04-07-2015, 04:47 PM

Tags for this Thread

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