I have a form in which I enter a record and I have a field that automatically updates with the current date. I would like to add a subform that tells me how many records have been entered "today" based on current date (today's date). I would like it to keep a running total. I am new to asking questions in this format and appreciate any help and understanding.
+1
A:
For Access:
SELECT COUNT(*) FROM ... WHERE add_date BETWEEN DATE() AND DATE()+1;
as suggested by David W. Fenton in the comments.
André Hoffmann
2009-08-25 23:13:10
Thank you for your quick reply. I typed the following:SELECT count (*)FROM MonitorsWHERE radd_date>=curdate();the error message I get is:Undefined function 'curdate' in expression.What info am I missing to provide to you?
2009-08-25 23:18:25
You have copied my previous revision which was meant to be for mysql. Try the current one and it should work.
André Hoffmann
2009-08-25 23:19:51
In other words: it's DATE() in access and not CURDATE()
André Hoffmann
2009-08-25 23:20:37
I actually got it to run, however the count was "0". I know that for the date of 08/25/09, I have records entered.
2009-08-25 23:26:38
What data type does the column radd_date have in your table? Could you maybe post a dump of the first say 3 rows just so we know how your table looks?
André Hoffmann
2009-08-25 23:30:16
Allow for a my ignorance. how do I post a dump of the first 3 rows? The column I Im trying to add is titled Today's Date and Data type is Date/Time, with format as general date. My Table is titled Monitors.
2009-08-25 23:38:35
Well that's odd. Could you check what SELECT DATE(); returns?
André Hoffmann
2009-08-25 23:51:35
Andre, Thank you for not giving up on me. how would you write out the statement? I am not sure where I should put SELECT DATE()
2009-08-26 00:05:10
Actually it should be the same place where you currently run the other statement. You then should see what DATE() returns and can compare it to what's stored in your table. Maybe that'll bring you one step closer to why a simple >= comparison isn't working.
André Hoffmann
2009-08-26 11:13:52
Date() won't match any records where your date field has a time component. If the fields are populated with Now(), they will have times, and won't match. In that case, you your criteria would be BETWEEN Date() AND Date()+1. That's preferable to processing the values to get rid of the time part, as a function can't use an index, whereas the BETWEEN can.
David-W-Fenton
2009-08-26 19:14:01
Thank you to all who have responded. Just a quick not of Thanks. Have been busy taking care of medical issues for my father, but did not want to be ungrateful for all the suggestions presented. Once again, Many Thanks.
2009-09-10 23:07:47
Now please select the answer that solved your problem by clicking onto the check mark next to the post on the left. You thereby mark your question as answered.
André Hoffmann
2009-09-11 00:29:13
+1
A:
What development environment are you using?
Windows or web application?
Are these records being inserted into a database?
Is the database sql based?
Please provide more information.
Thanks
Joe
Joe Pitz
2009-08-25 23:13:57
Why -1, when I answered there was no other posts. SQL syntax is different for access then any other database. He asked how to add to a sub form, unless I know what he is developing in how can I provide an answer unless I know development platform.
Joe Pitz
2009-08-25 23:19:15
Maybe someone didn't like that you posted this as an answer instead of a comment. Anyway, +1 for karma :)
André Hoffmann
2009-08-25 23:22:18
If you are going to give negative points for asking intelligent questions in order to solve a problem rather than just firing off an answer just to be be the first one to post an answer. You are then violating the spirit of this site.
Joe Pitz
2009-08-25 23:25:55
While I realize you don't have enough reputation to add comments yet, that's a better place for your questions. Your answer isn't really an answer.
GApple
2009-08-25 23:27:08