SQL noob needs help: Finding unique field entries per day in Access Date field that includes timestamps

1

I have an Access Db that lots of automated reports write an error report to with a date/time stamp in the format 'dd-mmm-yyyy hh:mm:ss'. I'm currently pulling stats from this table using this statement:

sSQL = "SELECT COUNT(ErrorCode) FROM ErrorsTbl WHERE ErrorReport='" & ReportName & "' AND ErrorDate>#" & Now - 28 & "#;"

(issued in VBA)

However, if a report tries to save outputs to 5 different locations while the drive is disconnected, for example, then it'll write 5 different error reports to the db. Therefore, I need to pull the number of days a report has logged an error in the last 28 rather than a count of errors logged.

If my date and time fields were split I think I could figure it out, but since they're merged....and it's a major code rewrite of 200 ish reports to split them....can someone please help me with the SQL to pull this?

2

This is more an Access question than a SQL question I think. Maybe try an MS Access forum?

3

Forgive me for saying so but on what planet is a question about SQL query syntax about Access?

especially when the user doesn't have Access installed....

4

Transaction SQL is different from Access SQL or VBA.
In T-SQL there is no "#" around the date and the "&" sign is generally not used to concatenate.

5

If your question is a purely SQL issue, we could help.

Are you saying something like this:
Nov 10 10:00:00 am - Error x
Nov 10 10:00:01 am - Error x
Nov 10 10:00:02 am - Error x
Nov 10 10:00:03 am - Error x
Nov 10 10:00:04 am - Error x
Nov 11 9:00:00 am - Error y

And you get error count = 6. But you want error count = 2 ?