Thanks to the support in this forum I am close to finalizing my first database project. For now I have one more question: My database contains a table that has two fields named 'occurance1' and 'occurance2', both containing a range of different dates. In a form I have in that database, I would like to have a field displaying the total counts for both 'occurance1' and 'occurance2', but only the count for both occurances that have 'Today' as a date. Preferably without having to run yet another query, so if possible I would like that as soon as an occurance that has 'today' as a date is entered in the table, the total count shows correctly in the form. I hope I make any sense, and somebody can help me how to accomplish this?
display the count of 'today occurences' from a table in a form?
- Thread starter hiccup
- Start date
I don't know if I completely understand what you want, but it sounds like you want to get a particular field's record from a table. To do that you would use a DLookup (http://www.techonthenet.com/access/functions/domain/dlookup.php).
In that case you want DCount (http://www.techonthenet.com/access/functions/domain/dcount.php)
Hm, I really feel like a chimp with a calculator.
I have no understanding of codes in Access, and after some Googling I am assuming I must do the following:
- create a Text box in my form
- open the property sheet for that box
- go to the 'data' tab
- on the right of 'Control Source' enter: =DCount("field-name","table-name",[Date()])
As I understand that should count all 'today' dates present in that specific field column?
But if I do that, I get an error: 'invalid syntax'.
What am I doing wrong here?
I have no understanding of codes in Access, and after some Googling I am assuming I must do the following:
- create a Text box in my form
- open the property sheet for that box
- go to the 'data' tab
- on the right of 'Control Source' enter: =DCount("field-name","table-name",[Date()])
As I understand that should count all 'today' dates present in that specific field column?
But if I do that, I get an error: 'invalid syntax'.
What am I doing wrong here?
Not bad, 90% there. The criteria argument of your DCount is incorrect.
[Date()]
First, it needs to be a string, your's is techincally a reference to a field. Second, it needs to be a statement that evaluates to True/False, which usually means you need some sort of comparison operator (=, >, <).
You need to compare whatever field holds the date value to the current date. Your criteria argument should look something like this:
"[YourDateFieldName]=#" & Date() & "#"
[Date()]
First, it needs to be a string, your's is techincally a reference to a field. Second, it needs to be a statement that evaluates to True/False, which usually means you need some sort of comparison operator (=, >, <).
You need to compare whatever field holds the date value to the current date. Your criteria argument should look something like this:
"[YourDateFieldName]=#" & Date() & "#"
Weird, I'm using exact same code in your database and it works fine. Try this:
Put brackets around Date1 in the first argument:
=DCount("[Dates1]","tbl_Occurrences","[Dates1]=#" & Date() & "#" )
If that doesn't work, see if this will work:
=DCount("[Dates1]","tbl_Occurrences")
If that works, it means the criteria is the culprit.
Put brackets around Date1 in the first argument:
=DCount("[Dates1]","tbl_Occurrences","[Dates1]=#" & Date() & "#" )
If that doesn't work, see if this will work:
=DCount("[Dates1]","tbl_Occurrences")
If that works, it means the criteria is the culprit.
