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