DCOUNT with two criteria (CURRENT DATE and VALUE (from COMBOBOX) )

[vazdajic]

Hello there..



I would like an unbound textbox to show me the number of today's records for an user which is currently selected in a combobox: [Combo172]

Now, with following expression (on that unbound textbox ) I see how many records 6 of us have input in the database today:

=DCount("[CHECKED BY]", "Table","[DATECHECKED] = Date()")


Written above works fine but I would like to see how many records I have added to the database today and if I select another user, the textbox should update and show me his today's records etc.

HAVING clause is the one I have tried with but with no success..

[CJ_London]

modify your where parameter to something like

"[DATECHECKED] = Date() AND user=" & me.user

where user is the id number for the current user. if user is text then don't forget to add the single quotes

[vazdajic]

Hm.. I didn't defined users with primary key. Any text can be user so for me it is important to be able to use one of listed users in combobox as a criteria... I just need that text currently shown in combobox as second critecia ( first is Date() )

[CJ_London]

me.user would be the name of your combobox

[vazdajic]

I get an error

Any new advice??

PS: Combobox is a Text combobox: [Combo172]

[CJ_London]

not without knowing what you have actually used as code, what the actual error is and an example of the data being used

[vazdajic]

Picture tells it all. Plus, I have uploaded the database. If you have time, please take a look..

[CJ_London]

you would need something like

DCount("[CHECKED BY]", "Table","[DATECHECKED] = Date() AND [Checked By]='" & combo172 & "'")

'Table' is probably the worst name you can use for your table name - it is a reserved word and using it can cause unexpected bugs difficult to identify. STRONGLY suggest change it to something more meaningful

Similarly using spaces in names makes it a) easier to create a bug, b) more difficult to debug and c) when used in conjunction with a reserved word (Date in this case) can still create difficult to identify errors.

Google 'Access reserved words' for a full list

[vazdajic]

In a hurry I left "Table" as a name
I changed it now to [Table1] but second box doesnt give me nr.3 but it gives me an error (#Name?)

Hm...

[CJ_London]

that means you have spelt something incorrectly - you did include the '='?

[vazdajic]

It works excellent:

=DCount("[CHECKED BY]","Table1","[DATE CHECKED] = Date() AND [Checked By]='" & [combo8] & "'")

My mistake(s)... I have had on combobox label Combo172 but it's actual name was Combo8.. Also, I have a space in [DATE CHECKED]..

My mistakes. Your code works perfect. Many thanks!!!!!!!!!!!

[CJ_London]

just think how much time you would have saved if you named things properly