Prev: vb script for opening notepad
Next: regtlib.exe and Windows 7
From: David on 23 Apr 2010 19:37
I'm doing a date rollup. The rollup period varies based on user input. The
following sub works EXCEPT when I cross Over midnight, the rollup begin time
needs to reset for a new day with the new being time being midnight and the
new end time being midnight plus the user specified rollup period (e.g. 15
minutes).

I previous days ending period will be clipped at midnight (which may leave a
portion of the user period. However, I'm Not sure how to screen ("test")
for next record (i.e. the beginning record for next day) because of the
loop.

Any input appreciated.
This works except for the midnight crossover:

'Create a Date Loop Not a Recordset Loop
Do

'Query Returns Recordset for Period
Set rsQuery = Query_GetData(strSrcTBLName, dtmDateBeg, dtmDateEnd)

'Since the Do/Loop increments the Date to be Rolled
'it may return a Date with Null Records since the
'underlying Source Table does Not contain
'continuous 24 hours data.

If Not IsNull(rsQuery!fldPay) Then
rsDest.Index = "PrimaryKey"
rsDest.Seek "=", MDateTime.StripMilliSeconds(dtmDateEnd)

If rsDest.NoMatch Then

'Save the Return Records and Ending Date to Dest Table
rsDest.AddNew
rsDest!fldHistDateTime = MDateTime.StripMilliSeconds(dtmDateEnd)
rsDest.Update

Else

rsDest.Edit
' rsDest!fldHistDateTime =
MDateTime.StripMilliSeconds(dtmDateEnd) '<<already exists
rsDest.Update

End If 'No Match
End If 'IsNull

rsQuery.Close '<<Critical

If dtmDateEnd >= dtmTBLDateEnd Then Exit Do

'Increase Each Time by One SubPeriod (e.g. minutes)
dtmDateBeg = DateAdd("n", iSubPeriod, dtmDateBeg)
dtmDateEnd = DateAdd("n", iSubPeriod, dtmDateEnd)

Loop 'Dates





From: David on 23 Apr 2010 22:45
I believe I have a solution.
Thanks to anyone who perused this.

David

"David" <NoWhere(a)earthlink.net> wrote in message
news:%23pSli3z4KHA.5476(a)TK2MSFTNGP06.phx.gbl...
> I'm doing a date rollup. The rollup period varies based on user input.
> The following sub works EXCEPT when I cross Over midnight, the rollup
> begin time needs to reset for a new day with the new being time being
> midnight and the new end time being midnight plus the user specified
> rollup period (e.g. 15 minutes).
>
> I previous days ending period will be clipped at midnight (which may leave
> a portion of the user period. However, I'm Not sure how to screen
> ("test") for next record (i.e. the beginning record for next day) because
> of the loop.
>
> Any input appreciated.
> This works except for the midnight crossover:
>
> 'Create a Date Loop Not a Recordset Loop
> Do
>
> 'Query Returns Recordset for Period
> Set rsQuery = Query_GetData(strSrcTBLName, dtmDateBeg, dtmDateEnd)
>
> 'Since the Do/Loop increments the Date to be Rolled
> 'it may return a Date with Null Records since the
> 'underlying Source Table does Not contain
> 'continuous 24 hours data.
>
> If Not IsNull(rsQuery!fldPay) Then
> rsDest.Index = "PrimaryKey"
> rsDest.Seek "=", MDateTime.StripMilliSeconds(dtmDateEnd)
>
> If rsDest.NoMatch Then
>
> 'Save the Return Records and Ending Date to Dest Table
> rsDest.AddNew
> rsDest!fldHistDateTime =
> MDateTime.StripMilliSeconds(dtmDateEnd)
> rsDest.Update
>
> Else
>
> rsDest.Edit
> ' rsDest!fldHistDateTime =
> MDateTime.StripMilliSeconds(dtmDateEnd) '<<already exists
> rsDest.Update
>
> End If 'No Match
> End If 'IsNull
>
> rsQuery.Close '<<Critical
>
> If dtmDateEnd >= dtmTBLDateEnd Then Exit Do
>
> 'Increase Each Time by One SubPeriod (e.g. minutes)
> dtmDateBeg = DateAdd("n", iSubPeriod, dtmDateBeg)
> dtmDateEnd = DateAdd("n", iSubPeriod, dtmDateEnd)
>
> Loop 'Dates
>
>
>
>
>


 | 
Pages: 1
Prev: vb script for opening notepad
Next: regtlib.exe and Windows 7