How do you count time values?

[copleyuk]

Hi Everyone,

I am trying to count the number of table entries which have the same [starttime].

I am clearly missing something in the search criteria but cannot get the correct combination...
Here is the code:

Dim check As String

check = DCount("[starttime]", "request", "[StartTime] = #" & Me.StartTime & "#")

MsgBox ("" & check & "")

I have tried a number of combinations Dimming as a string and as a date, Starttime is of type date/time.

Whenever I change the code I am getting either 0 returned (I have created to entries with the same [Starttime]) but as I have just created my current record there should always be at least the current record returning at least a 1? Or I get data type error or a syntax error!

I'm sure this is probably a simple apostrophe in the wrong place or something but I cannot get my head around it so please help!!

Thanks in advance

Carl :)

[Mariostg]

What about Me.StartTime? Is is it Date/time format or convertable to that format?

[copleyuk]

Me.StartTime is a date/time (hh:mm) format.

[Mariostg]

Blank fields are not the issue.

[NeoPa]

Why not change line #5 to :

MsgBox (Check & " - " & Me.StartTime)

and let us know the results for a record that you expect to show a value of greater than zero.

I expect that will give us something to work with.

[copleyuk]

Thank you for your help so far...
I have made the change as you suggested which returned as:
0 - 12:44:00

I was unsure if I needed to be checking for 12:44:00 or 12:44 so changed the code again slightly to allow for formating the time value:

   times = Format(Me.StartTime, "HH:MM")
   check = DCount("[starttime]", "request", "[StartTime] = #" & times & "#")
   MsgBox (check & " - " & times & " - " & Me.StartTime)

I also then tried it as:

   times = Format(Me.StartTime, "HH:MM:SS")
   check = DCount("[starttime]", "request", "[StartTime] = #" & times & "#")
   MsgBox (check & " - " & times & " - " & Me.StartTime)

Other than the change in outputed time value ie 12:44 or 12:44:00 for times, check still returned 0 and Me.Starttime returned as 12:44:00.
I've been through the table and checked how the values are stored and they are set as time/date and are listed as 12:44:00 format. There are also multiple returns for 12:44 in the table...

Thanks again
Carl

[copleyuk]

Just tried something and got even stranger results hopefully this may make more sense to you guys cause I just went "huh??"

I thought that in order to test what I was trying to do I would build a query to show relevant results...
So I loaded the table and StartTime and the put the criteria in as #12:44:00# but instead of getting the list I expected I got 0 returns. So I changed the criteria to be Like #12:44:00# which returned the 6 records I was expecting.

Changed the code in VBA to be:

   Dim check As String
   Dim times As String
   
   times = Format(Me.StartTime, "HH:MM:SS")
   check = DCount("[starttime]", "request", "[StartTime] Like #" & times & "#")
   MsgBox (check & " - " & times & " - " & Me.StartTime)

And the outputted "check" now shows 6 also...

So I appear to have found a solution but really do not understand why this works but "=" doesn't!?

[Mariostg]

I think the problem is that eventhough in a table you specify format hh:mm, this is what will be displayed, not the way it is recorded. In such a field, you could enter Jul 12 and 0:00 and both will display 0:00 but the content will specifically be 2010-07-12 and 0:00.

convince yourself by running this query on your request table:

SELECT starttime, CDbl([starttime]) AS oops
FROM request;

[NeoPa]

I think Mario is on exactly the right track here.

The format of a field has no bearing whatsoever on the contents, except in as much as they are displayed. I expect your 6 records which have times of 12:44, actually have times more like :

12:44:05.86
12:44:19.03
12:44:25.99
12:44:34.07
12:44:47.31
12:44:52.29

Not exactly these of course, but you can see from these examples why your test of "=" would not work.

I would hazard a guess that the data is set automatically (by the system at point of entry) rather than typed in as 5 characters "12:44". This means that it makes no sense to look for values matching #12:44#, as these would happen less frequently than the proverbial blue moon.

If you want to check if the time value is one that displays as "12:44" then instead use :

Check = DCount("*", _
               "[Request]", _
               "Format([StartTime],'HH:mm') = '" & Format(Me.StartTime,"HH:mm") & "'")

[copleyuk]

The StartTime is manually entered, however your comment did give me a lightbulb moment.

Because data entry is such a pain at the best of times the Me.StartTime field is set with an input mask to set the : in place.

I am fairly sure from other issues I have previously run into that input mask characters are not actually part of the data but simply displayed in the appropriate place?

So I am guessing that this is what is causing the problem? = means exactly the same as 12:44 but there is no : whereas Like 12:44 matches correctly?

I ran the query as per your suggestion Mariostg which returned the following:
StartTime oops
12:44:00 0.5305555555555 56
12:44:00 0.5305555555555 56
12:44:00 0.5305555555555 56
12:44:00 0.5305555555555 56
12:44:00 0.5305555555555 56
12:44:00 0.5305555555555 56
12:44:00 0.5305555555555 56

Not quite sure what I am looking at there so not sure I am "convinced" of much... I think though that this shows the opposite of what was suggested in that actually all the values stored are identical?

Thank you both for your help with this :)

Carl

[NeoPa]

COpleyUK:
Not quite sure what I am looking at there so not sure I am "convinced" of much... I think though that this shows the opposite of what was suggested in that actually all the values stored are identical?

Quite right Carl. It does.

These values are the decimal equivalents of the time (so, the time value, but formatted as a simple decimal number). All are the same, and all are the exact value of #12:44:00#. I'm curious why there are seven (7) records shown though. I would have expected only six (6).

PS. Lightbulb moments are always good, but in this case the results also show that the correct time is being stored in each record, so there is no issue related to loss of a ":" character.

[NeoPa]

Carl, this is now intriguing enough for me to consider requesting a database copy from you. I will include below, the usual instructions for attaching databases. Some points may not apply, but please read through them anyway.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[copleyuk]

Erm... I will try and sort a copy of the database but it is a bit of a beast (65,000+ entries, 25+ linked tables, more forms then you can shake a stick at and just to make things even more fun - split front end/back end to a SQL server with just about everything being pass through not local) and contains sensative information which means that certain fields would need to be cleared out before sending it once a local copy was made available... However, with an indomitable spirit I will try and get it done!

The result of 7 might seem slightly less strange if I'd have pointed out that when testing I had added an extra record with 12:44 as the starttime...


Thanks for trying to sort this one out!

Carl

[NeoPa]

I so hear you Carl. This is indeed not straightforward , and I urge you to consider first if this is something you wish to do. I will happily look at it for you if you do of course, but if you feel the effort involved outweighs the potential benefit, then I will certainly understand.

On the other side of the coin, if you're ruthless about it and cut out linked tables (copying some reduced data locally instead) and getting rid of much you think is irrelevant, and it still exhibits the same behaviour of course, then you may well get a much better idea of what you're working with. It wouldn't be the first time that the effort of preparing such a beast was all that was required to find the issue itself.

Needless to say, this must be a copy and you should make sure you don't forget yourself and accidentally apply some of the changes to your main copy instead.

[Mariostg]

I don't know if that would suffice, but Carl, did you try to copy the data and structure of the reluctant table into an empty database and run your DCOUNT query. If it gives the same output, maybe this is all NeoPa would need i.e. that table. And if it gives something different as output... then I don't know and I am even more puzzled.