How to calculate total records with same hour?

[marcossch]

I am struggling days to solve this. A have to show in a form, how many clients (records) are scheduled at the same date and time. I tried to use =DCount("*";"Agenda";" [Hour] = " & [Hour] & "") with no success.



Thanks for any help!

[orange]

What is the datatype of field Hour in your table?
Can you show your Hour table design and a few records?

[Micron]

What is the datatype of field Hour in your table?

I'll bet your money that it is text (care to guess why?).
In which case, maybe
DCount("*";"Agenda";"[Hour] = '" & [Hour] & "'")
But getting an answer to your question would be good.

[Missinglinq]

I suspect that you're right! The Combobox for hours probably has a Value List as its Row Source Type...which I believe would make the value Text.

Linq ;0)>

[Micron]

I'm going by what I can see, not by what I can't. But that's a good guess.

[marcossch]

What is the datatype of field Hour in your table?
Can you show your Hour table design and a few records?

For sure. I am not an expert and may be doing some huge mistake.

[marcossch]

Couldn´t discover where is the delete post.... got duplicated.

[marcossch]

Micron genius!. DCount("*";"Agenda";"[Hour] = '" & [Hour] & "'")
As text it gives me a number, but I guess it is counting for every day. How could I count only the same day and same hour?

[Micron]

I think my suggestion in my first post applies. Spaced out to see the difference more clearly: " DCount("*";"Agenda"; ' " [Hour] = " & [Hour] & " ' ")
I think the major mistake is that a time field should be Date/Time data type and not text.

In case anyone is wondering, the clues were the left justification of the hour values (by default, text is left, numbers are right). I discounted the possibility that OP would be justifying this field manually. Plus, the DCount expression is trying to add a zls.

[Micron]

I see our posts crossed. Try adding the date as criteria as well as the hour. Maybe
DCount("*";"Agenda";" "[Data] = #DateHere# And [Hour] = '" & [Hour] & "'")

I'm a bit confused as to why it works properly (if it does) when your field is named Horario but you use Hour. Also, Data is a reserved word. I wonder what the implications are when other languages are involved. Or does Data not translate to Date?

[marcossch]

I see our posts crossed. Try adding the date as criteria as well as the hour. Maybe
DCount("*";"Agenda";" "[Data] = #DateHere# And [Hour] = '" & [Hour] & "'")

I'm a bit confused as to why it works properly (if it does) when your field is named Horario but you use Hour. Also, Data is a reserved word. I wonder what the implications are when other languages are involved. Or does Data not translate to Date?

=DCount("*";"Agenda";"[Data] = #" & [Data] & "# AND [Horário] = '" & [Horário] & "'") solved the problem!!!!!!
I just translated in the first post Horário to hour to keep the forum language, but I had to use Horário in order to work.
Perfect solution. Thanks a lot.
I think Access could give us a hint other than just ?ERROR and nothing else.

[Micron]

I think Access could give us a hint other than just ?ERROR and nothing else.

I understand why you say that, but the problem is that those types of messages can only be generic because of the vast multitude of reasons that cause them. It could be
- a missing ' or " or [ or ( or ] or )
- a misspelled control name
- a mispelled field name
- improper use of an operator; e.g. LIKE, >, =, etc.
- improper reference to a main form, subform or one of its fields/controls
- where you use a domain aggregate function (e.g. DSum) in an expression where the field and form control have the same name
- etc.
I guess at least they try to narrow down the reasons by distinguishing between #Name and #Error. There may be others that I can't think of right now.