Daily total query

[carlisle]

Hi,

I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.

Any help would be appreciated,

Ben

 

[cuttsy]

What have you tried so far?
Are you looking for something like this?

SELECT COUNT(YourTable.SerialID) AS TotalEntries
FROM YourTable
WHERE YourTable.YourDate = #09/07/2006#

 

[carlisle]

That seems to be what I want, but it comes up eith syntax errror for some reason?

 

[cuttsy]

what does the error say?

have you changed 'YourTable' to the name of your table and 'YourDate' to the name of you date field?

 

[carlisle]

"The syntax of the subquery in this expression is incorrect/

Check the subquery's syntax and enclose the subquery in paretheses"

And, YES, I have changed the names! lol

Also, which field should I put the expression in?

 

[cuttsy]

if you switch to sql view in your query and copy the code i posted into the window and change the names it should work just fine. It works for me.

 

[carlisle]

Excellent! I didn't know about that SQL view. Thanks for the mentoring lol

 

[cuttsy]

No worries.

 

[carlisle]

Need some more help lol. Um, the date hasn't been automatically updated, so it's showing the records from yesterday, the 7th. Because of '#09/07/2006#'?

 

[BobJones]

Try

SELECT COUNT(YourTable.SerialID) AS TotalEntries
FROM YourTable
WHERE YourTable.YourDate = # & Format(Now(), "mm/dd/yyyy") & #

Because of the american date standards the date format has to be set as that, at least ive found anyway

 

[carlisle]

No, when I replace the previous date part with that expression it comes up with a syntax error?
Thanks for the attemp tho.

 

[cuttsy]

SELECT COUNT(YourTable.SerialID) AS TotalEntries
FROM YourTable
WHERE YourTable.YourDate = Format(Now(), "yyyy-mm-dd")

 

[carlisle]

There ya go! Cheers once again...