Adding numeric data available in text field

[Guest]

I have a table which has following columns
Log dt as text -->Ex:070101 (yymmdd)
strt tm as text -->Ex:000600(which means 12:06 am)
end tm as text--> no data
dur as text--> Ex: 000030( 30 secs duration)
I want to loop through each record and have to fill up end tm as [strt
tm]+[dur],
in this example [end tm] should be displayed as 000630,and then this end
time should be the start time for next record.
Another scenario:
strt tm-->000630
end tm-->null
dur-->000030
end tm should be updated to 000700 not as 000660 when adding
Can anyone help me on this issue

 

[strive4peace]

Calculate End Time
---

since end time can always be calculated from the start time and the
duration, there is no need to store it.

If the duration puts the resulting time into the next day, a function
could be written to handle this, here is a simple equation you can test
in a query

Endtime: format(cLng([strt tm]) + cLng([dur]),"000000")

.... this is just a start -- you will need to move this equation into a
function anyway in order to increment the counter when 60 is reached.

how comfortable are you with Visual Basic or with using functions?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi,

Thank you very very much for your help.
I tried the equation in the query and i got awesome result for end time
calculation.
But i am not familiar with looping structure while writing a function.
can you please guide me on this?

Kowsy

Calculate End Time
---

since end time can always be calculated from the start time and the
duration, there is no need to store it.

If the duration puts the resulting time into the next day, a function
could be written to handle this, here is a simple equation you can test
in a query

Endtime: format(cLng([strt tm]) + cLng([dur]),"000000")

.... this is just a start -- you will need to move this equation into a
function anyway in order to increment the counter when 60 is reached.

how comfortable are you with Visual Basic or with using functions?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a table which has following columns
Log dt as text -->Ex:070101 (yymmdd)
strt tm as text -->Ex:000600(which means 12:06 am)
end tm as text--> no data
dur as text--> Ex: 000030( 30 secs duration)
I want to loop through each record and have to fill up end tm as [strt
tm]+[dur],
in this example [end tm] should be displayed as 000630,and then this end
time should be the start time for next record.
Another scenario:
strt tm-->000630
end tm-->null
dur-->000030
end tm should be updated to 000700 not as 000660 when adding
Can anyone help me on this issue

 

[strive4peace]

General Function GetEndTimeString
---

Hi Kowsy,

'~~~~~~~~~~~~~~~~~~~~~~~~
Function GetEndTimeString( _
pStart As String _
, pSeconds As String _
) As String

'if one of the parameters is missing, don't calculate anything
If Len(Trim(Nz(pStart, ""))) = 0 Then Exit Function
If Len(Trim(Nz(pSeconds, ""))) = 0 Then Exit Function

'could Dim these as Integers
'but I like to use Longs for calculation reasons

Dim mHr As Long _
, mMin As Long _
, mSec As Long

mHr = CLng(Left(pStart, 2))
mMin = CLng(Mid(pStart, 3, 2))
mSec = CLng(Right(pStart, 2))

mSec = mSec + CLng(pSeconds)

mMin = mMin + (mSec \ 60)
mSec = mSec Mod 60

mHr = mHr + (mMin \ 60)
mMin = mMin Mod 60

'okay, I think we have all the right numbers... lets combine them...

GetEndTimeString = Format(mHr, "00") _
& Format(mMin, "00") _
& Format(mSec, "00")

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

~~~~~~~~~~~~~~~~~~~~~~~~~

to use the function in a query -->

Endtime: GetEndTimeString([strt tm],[dur])


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,

Thank you very very much for your help.
I tried the equation in the query and i got awesome result for end time
calculation.
But i am not familiar with looping structure while writing a function.
can you please guide me on this?

Kowsy

Calculate End Time
---

since end time can always be calculated from the start time and the
duration, there is no need to store it.

If the duration puts the resulting time into the next day, a function
could be written to handle this, here is a simple equation you can test
in a query

Endtime: format(cLng([strt tm]) + cLng([dur]),"000000")

.... this is just a start -- you will need to move this equation into a
function anyway in order to increment the counter when 60 is reached.

how comfortable are you with Visual Basic or with using functions?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a table which has following columns
Log dt as text -->Ex:070101 (yymmdd)
strt tm as text -->Ex:000600(which means 12:06 am)
end tm as text--> no data
dur as text--> Ex: 000030( 30 secs duration)
I want to loop through each record and have to fill up end tm as [strt
tm]+[dur],
in this example [end tm] should be displayed as 000630,and then this end
time should be the start time for next record.
Another scenario:
strt tm-->000630
end tm-->null
dur-->000030
end tm should be updated to 000700 not as 000660 when adding
Can anyone help me on this issue

 

[Guest]

Hi,

Thank you very very much for all your help.
But i want to loop through all records to satisfy a criteria

Table structure:

Type-->text
LOG DT(YYMMDD)-->TEXT
strt tm-->text
end tm-->text
dur-->text

Data in the table:
TYPE LOG DT STRT TM END TM DUR
PRG 070101 003000 010000 003000
COM 070101 003200 000030
PRO 070101 003500 000015
PRO 070101 003515 000045
COM 070101 003600 000030
PRG 070101 010000 013000 003000
COM 070101 011000 000030
PRO 070101 011030 000015
PRO 070101 011045 000200
COM 070101 011245 000030

So My concern is
1)Whenever you have Type as PRG which has both strt tm and end tm
change the end tm of PRG to next line strt tm and calculate the duration
based on the new value
So in the example i stated below
PRG 070101 003000 010000 003000
COM 070101 003200 000030
The result should show as
PRG 070101 003000 003200 000200-->duration is calculated on
new val
COM 070101 003200 003230 000030-->Add the duration to strt
tm and output that in end tm

2)If the Type is not PRG then add the duration to strt tm and calculate the
end time using the function you mentioned before

I want to loop through all of these records and check for the condition and
do the manipulation?

I know i have written an long paragraph
Thanks in advance
Regards
Kowsy

General Function GetEndTimeString
---

Hi Kowsy,

'~~~~~~~~~~~~~~~~~~~~~~~~
Function GetEndTimeString( _
pStart As String _
, pSeconds As String _
) As String

'if one of the parameters is missing, don't calculate anything
If Len(Trim(Nz(pStart, ""))) = 0 Then Exit Function
If Len(Trim(Nz(pSeconds, ""))) = 0 Then Exit Function

'could Dim these as Integers
'but I like to use Longs for calculation reasons

Dim mHr As Long _
, mMin As Long _
, mSec As Long

mHr = CLng(Left(pStart, 2))
mMin = CLng(Mid(pStart, 3, 2))
mSec = CLng(Right(pStart, 2))

mSec = mSec + CLng(pSeconds)

mMin = mMin + (mSec \ 60)
mSec = mSec Mod 60

mHr = mHr + (mMin \ 60)
mMin = mMin Mod 60

'okay, I think we have all the right numbers... lets combine them...

GetEndTimeString = Format(mHr, "00") _
& Format(mMin, "00") _
& Format(mSec, "00")

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

~~~~~~~~~~~~~~~~~~~~~~~~~

to use the function in a query -->

Endtime: GetEndTimeString([strt tm],[dur])


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,

Thank you very very much for your help.
I tried the equation in the query and i got awesome result for end time
calculation.
But i am not familiar with looping structure while writing a function.
can you please guide me on this?

Kowsy

Calculate End Time
---

since end time can always be calculated from the start time and the
duration, there is no need to store it.

If the duration puts the resulting time into the next day, a function
could be written to handle this, here is a simple equation you can test
in a query

Endtime: format(cLng([strt tm]) + cLng([dur]),"000000")

.... this is just a start -- you will need to move this equation into a
function anyway in order to increment the counter when 60 is reached.

how comfortable are you with Visual Basic or with using functions?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Kowsy wrote:
I have a table which has following columns
Log dt as text -->Ex:070101 (yymmdd)
strt tm as text -->Ex:000600(which means 12:06 am)
end tm as text--> no data
dur as text--> Ex: 000030( 30 secs duration)
I want to loop through each record and have to fill up end tm as [strt
tm]+[dur],
in this example [end tm] should be displayed as 000630,and then this end
time should be the start time for next record.
Another scenario:
strt tm-->000630
end tm-->null
dur-->000030
end tm should be updated to 000700 not as 000660 when adding
Can anyone help me on this issue

 

[strive4peace]

Update Query, Reserved Words
---

Hi Kowsy,

you're welcome

I am reluctant to tell you how to store calculated fields, but since you
seem bent on doing it:

an UPDATE Query work work well -- and faster than writing a program to
loop...

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#

~~~~~~~~~~~
to use the QBE (Query-By-Example) grid:

make a new query based on your table

change the type query to Update
from the menu in design view --> Query, Update

this puts an UpdateTo row on the grid

Field --> Endtime
UpdateTo --> GetEndTimeString([strt tm],[dur])

Field --> [Type]
criteria --> "Prg"

then click the RUN (!) button

before running any action queries, back up your database
~~~~~~~~~~~

Type is a reserved word and should not be used for a fieldname

Problem names and reserved words in Access, Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,

Thank you very very much for all your help.
But i want to loop through all records to satisfy a criteria

Table structure:

Type-->text
LOG DT(YYMMDD)-->TEXT
strt tm-->text
end tm-->text
dur-->text

Data in the table:
TYPE LOG DT STRT TM END TM DUR
PRG 070101 003000 010000 003000
COM 070101 003200 000030
PRO 070101 003500 000015
PRO 070101 003515 000045
COM 070101 003600 000030
PRG 070101 010000 013000 003000
COM 070101 011000 000030
PRO 070101 011030 000015
PRO 070101 011045 000200
COM 070101 011245 000030

So My concern is
1)Whenever you have Type as PRG which has both strt tm and end tm
change the end tm of PRG to next line strt tm and calculate the duration
based on the new value
So in the example i stated below
PRG 070101 003000 010000 003000
COM 070101 003200 000030
The result should show as
PRG 070101 003000 003200 000200-->duration is calculated on
new val
COM 070101 003200 003230 000030-->Add the duration to strt
tm and output that in end tm

2)If the Type is not PRG then add the duration to strt tm and calculate the
end time using the function you mentioned before

I want to loop through all of these records and check for the condition and
do the manipulation?

I know i have written an long paragraph
Thanks in advance
Regards
Kowsy

General Function GetEndTimeString
---

Hi Kowsy,

'~~~~~~~~~~~~~~~~~~~~~~~~
Function GetEndTimeString( _
pStart As String _
, pSeconds As String _
) As String

'if one of the parameters is missing, don't calculate anything
If Len(Trim(Nz(pStart, ""))) = 0 Then Exit Function
If Len(Trim(Nz(pSeconds, ""))) = 0 Then Exit Function

'could Dim these as Integers
'but I like to use Longs for calculation reasons

Dim mHr As Long _
, mMin As Long _
, mSec As Long

mHr = CLng(Left(pStart, 2))
mMin = CLng(Mid(pStart, 3, 2))
mSec = CLng(Right(pStart, 2))

mSec = mSec + CLng(pSeconds)

mMin = mMin + (mSec \ 60)
mSec = mSec Mod 60

mHr = mHr + (mMin \ 60)
mMin = mMin Mod 60

'okay, I think we have all the right numbers... lets combine them...

GetEndTimeString = Format(mHr, "00") _
& Format(mMin, "00") _
& Format(mSec, "00")

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

~~~~~~~~~~~~~~~~~~~~~~~~~

to use the function in a query -->

Endtime: GetEndTimeString([strt tm],[dur])


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,

Thank you very very much for your help.
I tried the equation in the query and i got awesome result for end time
calculation.
But i am not familiar with looping structure while writing a function.
can you please guide me on this?

Kowsy
:

Calculate End Time
---

since end time can always be calculated from the start time and the
duration, there is no need to store it.

If the duration puts the resulting time into the next day, a function
could be written to handle this, here is a simple equation you can test
in a query

Endtime: format(cLng([strt tm]) + cLng([dur]),"000000")

.... this is just a start -- you will need to move this equation into a
function anyway in order to increment the counter when 60 is reached.

how comfortable are you with Visual Basic or with using functions?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Kowsy wrote:
I have a table which has following columns
Log dt as text -->Ex:070101 (yymmdd)
strt tm as text -->Ex:000600(which means 12:06 am)
end tm as text--> no data
dur as text--> Ex: 000030( 30 secs duration)
I want to loop through each record and have to fill up end tm as [strt
tm]+[dur],
in this example [end tm] should be displayed as 000630,and then this end
time should be the start time for next record.
Another scenario:
strt tm-->000630
end tm-->null
dur-->000030
end tm should be updated to 000700 not as 000660 when adding
Can anyone help me on this issue