Perplexing DATE variable problem (1 Viewer)

[Shisho2k]

Solved: Perplexing, becuase nothing was wrong with it. Even without the time data, the functions still work as intended. So no date problem after all. Doing tests with static values showed accurate results. The bug has something to do with the code logic keeping the previous time, and it failing to do this one a page change for the first record.

The Problem:

I have a report that uses DateDiff() to compare two date/time fields I pull off a record. My code works just fine... until the report hits a new page. On the new page, only the first record line gets affected by this magical problem. What's stranger still is that the correct values are in the variables. One of the four values decides it doesn't want to be added in. Just once, and just for kicks I assume.

Code in Question:

My RecordSet. It's working fine, but here it is to see the field contents and ordinal orders.

 [SIZE=2]
Set rstTimes = CurrentDb.OpenRecordset("SELECT Start_Time, End_Time, Start_Date, End_Date FROM TimeTable WHERE Start_Date = #" & Me.Start_Date & "# ORDER BY Start_Time")[/SIZE]

Then it comes to a part where I add these things together to use in the function.

Dim dtmStart As Date, dtmEnd As Date

 [SIZE=2]
                
            dtmStart = DateValue(rstTimes(2).Value) + TimeValue(rstTimes(0).Value)
            dtmEnd = DateValue(rstTimes(3).Value) + TimeValue(rstTimes(1).Value)[/SIZE]

The issue is with dtmStart. Because running the report with MsgBox outputs shows that both of them work fine the whole way through. Then the dtmStart glitches for the first record of a new page. It simply won't add the rstTimes(0).Value to itself. I can actually add a literal and it'll work. (Like + TimeValue(12:00)). I did that just to test it.

The Output:

For that first record of a new page this is what the message boxes show:

Why does it work 30 times, then on a new page not add them together (even though it's not empty) just for the first row, and then go back to working just fine?

If that's just too bizarre, any one have any work arounds? I don't want to reinvent the date/time wheel. >_<

Thanks.

 

[pbaldy]

Is it always that time? A time of 12:00am is a time value of 0, so it's no surprise that it displays with no time. The test you did with a literal was 12:00pm, which is a whole different matter.You could probably force the issue with the Format function:

?format(date(),"m/d/yyyy h:nn")
8/31/2012 0:00

Why it did what it did:

?date() + 0
8/31/2012
?date + timevalue("12:00")
8/31/2012 12:00:00 PM

 

[Shisho2k]

Is it always that time? A time of 12:00am is a time value of 0, so it's no surprise that it displays with no time. The test you did with a literal was 12:00pm, which is a whole different matter.You could probably force the issue with the Format function:

?format(date(),"m/d/yyyy h:nn")
8/31/2012 0:00

Why it did what it did:

?date() + 0
8/31/2012
?date + timevalue("12:00")
8/31/2012 12:00:00 PM

Thanks for the reply. I'll give your format stuff a try tonight.

I can tell you the actual time in question doesn't seem to matter. It's doing this only to the first record of a new page only. If you delete that record, the next one in line gets the same treatment, and then the code resumes doing what it's supposed to.

I have that message box directly in the spot where the code gets executed and does it's condition logic.

It does make me wonder if that works as intended, like it should be empty as you say, and the DateDiff just works fine. Since it seems like when you apply dates with no time in it, it assumes 12:00AM.

I'll look more into that, and maybe that'll offer some clues. Though I should say it's not being affected at all for any other 12:00AM entry. There's even one that's exactly identical, and the code functions.

Will get back to you as soon as I can with more info. Thanks again.

 

[Shisho2k]

Is it always that time? A time of 12:00am is a time value of 0, so it's no surprise that it displays with no time.

Well this bit of information has definitely been helpful. I'm looking at the wrong cause of the problem because of that, but as you say, that's working as intended. The code seems to handle it fine.

I guess my issue is something funny with the variable I'm using to hold the previous time, to see if the record spans into a new day. But somewhere it's value setting it's current end time to the previous on the page change. Not sure exactly yet where, since it's all done in the Detail_Format() event.

But thanks, you got me on the right track again.

 

[pbaldy]

Happy to help. If you still have a problem, post the rest of the code, or a sample db.

 

[Shisho2k]

Happy to help. If you still have a problem, post the rest of the code, or a sample db.

Thanks, I managed to fix it, but it's rather ineloquent. XD

I'm not sure how to put this in meaningful terminology, but it appears that the Detail_Format() event performs all the code and then checks to see if it has run onto a new page. That's logical enough.

When it happens to run over it carries it's operations/results to the first section of Detail (the report section itself) on the new page. Again, logic I can follow, and expected.

Then, the Detail_Format() fires off on the first section of detail information (on the new page), and with the same data. So the previous ending date/time will be it's current ending date/time. This all gets compared, and it again puts all this down into the first line of Detail.

So it seems the Detail_Format() triggers itself a second time on the same stuff ( the code and the actual report line) when a new page begins.

I suppose this isn't noticeable normally, since everything would just overwrite itself with itself. This case I'm checking stuff with a previous date/time of the most recent record. This is where things get ugly. It's comparing against itself in this state.

I ended up having the page header format event count the pages, and toggle a boolean everytime it hits a new page. In the detail code, if it's a new page starting out, I use Exit Sub and set the flag off, so it continues normally. It just does no code the first detail line.

Then it executes whatever it does on the backend to generate that report line in the document, doing none of my code to change the controls. So they remain intact from the rollover at the end of the previous page. It moves to the second line, the boolean flag is off until the next page, so it continues logic and filling the report form controls as usual.

I'm not entirely sure how to handle what's going here from one page to the next. So for now I'm just killing the execution from happening a second time, and everything displays fine.

 

[pbaldy]

If you look in VBA help at the Report.FormatCount Property you'll see this:

Microsoft Access increments the FormatCount property each time the OnFormat property setting is evaluated for the current section. As the next section is formatted, Microsoft Access resets the FormatCount property to 1.

Under some circumstances, Microsoft Access formats a section more than once. For example, you might design a report in which the KeepTogether property for the detail section is set to Yes. When Microsoft Access reaches the bottom of a page, it formats the current detail section once to see if it will fit. If it doesn't fit, Microsoft Access moves to the next page and formats the detail section again. In this case, the setting for the FormatCount property for the detail section is 2 because it was formatted twice before it was printed.

You can use the FormatCount property to ensure that an operation that affects formatting gets executed only once for a section.

 

[Shisho2k]

If you look in VBA help at the Report.FormatCount Property you'll see this:

Microsoft Access increments the FormatCount property each time the OnFormat property setting is evaluated for the current section. As the next section is formatted, Microsoft Access resets the FormatCount property to 1.

Under some circumstances, Microsoft Access formats a section more than once. For example, you might design a report in which the KeepTogether property for the detail section is set to Yes. When Microsoft Access reaches the bottom of a page, it formats the current detail section once to see if it will fit. If it doesn't fit, Microsoft Access moves to the next page and formats the detail section again. In this case, the setting for the FormatCount property for the detail section is 2 because it was formatted twice before it was printed.

You can use the FormatCount property to ensure that an operation that affects formatting gets executed only once for a section.

Oh wow, that is massively helpful. That does explain exactly what is happening. Sometimes I really wonder why somethings are set up the way they are in Access. XD

For some reason that property was floating around in my head the other day when I looked at this, was wondering if it could be involved.

So wrap it in a "if formatcount = 1 then", and that'll work too.

 

[pbaldy]

Glad we solved the mystery.