The must be an easier way to do this

[fennerhillpro]

I am working on a database that has errors in the time. Time is recorded in Military time (1059). However, I keep getting error because people have entered 1095 ect. Insted of doing 24 if then statements how can i do one that says if the last to digits are >59 then put in 30 so 1095 would convert to 1030?

Here is what I am doing now (The last line does work):


If {ERLOG.ATIME} = 960 to 999
then
CTime (12,12 ,12 )
else
If {ERLOG.ATIME} = 1060 to 1099
then
CTime (12,12 ,12 )
else
If {ERLOG.ATIME} = 1660 to 1699
then
CTime (12,12 ,12 )
else
If {ERLOG.ATIME} = 1760 to 1799
then
CTime (12,12 ,12 )
else
If {ERLOG.ATIME} = 1860 to 1899
then
CTime (12,12 ,12 )
else
If {ERLOG.ATIME} = 1960 to 1999
then
CTime (12,12 ,12 )
else
time(int({ERLOG.ATIME}/100),remainder({ERLOG.ATIME},100),0)

 

[synapsevampire]

Try something like:

if
{ERLOG.ATIME}-(int({ERLOG.ATIME}*.01)*100) > 59 then
ctime(int({ERLOG.ATIME}),30,0)
else
ctime(int({ERLOG.ATIME}),{ERLOG.ATIME}-(int({ERLOG.ATIME}*.01)*100),0)

-k

 

[synapsevampire]

Sorry, typo on the paste:

if
{ERLOG.ATIME}-(int({ERLOG.ATIME}*.01)*100) > 59 then
ctime(int({ERLOG.ATIME}),30,0)
else
ctime(int({ERLOG.ATIME}*.01),{ERLOG.ATIME}-(int({ERLOG.ATIME}*.01)*100),0)

-k

 

[lbass]

Assuming {ERLOB.ATIME} is a number, create a separate formula {@stringtime}:

if right(totext({ERLOG.ATIME},"0000&quot,2) in "60" to "99" then left(totext({ERLOG.ATIME},"0000&quot,2) + "30"

Then use {@stringtime} in your formula:

time(int(val({@stringtime})/100),remainder(val({@stringtime}),100),0)

Can't really test this, but I think it should work.

-LB