MS Access IF/THEN

[douglasw32]

If over 100 then - 100 + 60 so I can turn all of the DRF 1:12.3 to 72.3 etc.

How does this get written as an expression in access?

Thanks ahead of time, i know someone knows.

Doug

[rrbauer]

Where the time is expressed as M:SS.F, to convert it to seconds just multiply M by 60 and add SS.F to that result. (Your result field must be defined to handle the decimal fraction). This will handle any value of M including 0.

[douglasw32]

But I do not know how to breaj M and S apart in access... so just M can be multiplied and not the whole amount.

:mad:

[JoeG]

I'm just heading to bed, but use some sql formulas:

Example:
Time: M:SS.F

LEFT(Time,1) will give M
RIGHT(Time, 4) will give SS.F


so
(60*(LEFT(Time,1)))+RIGHT(Time, 4)

You may have to use a function to convert each part to a value, but you'll get the hang of it.

[JoeG]

BTW, I think all these racing PPs have some screw ball formats. I use formulas to extract data from harness charts and they have a call such as 4°°/2Q or X7IHR/8T. Always fun trying to get the information out of these into a useful form.

Good luck!

[sjk]

You probably need to locate the : to cope with 59.2 and/or :59.2.

If your time is the string field1

x: InStr([Field1],":") locates the separator and

time: IIf([x]=0,Val([field1]),IIf([x]=1,Val(Right([field1],Len([field1])-1)),Val(Right([field1],Len([field1])-[x]))+60*Val(Left([field1],[x]-1))))

calculates the time.

[douglasw32]

Soooooo CLOSE !!!

SJK and Joe,


But I see why the ":" is not being picked up.

Yhe DRF export has time over 60 as just a 100

So 1:12.4 is simply a 112

and I need it to be a 72 ?

[sjk]

Are you saying that the time is always a two or three digit number? That would make it simpler:

val([field1])-iif([field1]>99,40*val(left([field1],1)),0)

[traynor]

Use a compound conditional activated by the length of the string that is being converted. In English, if you read a time as a string and it is 1:12.26, it is in hundredths. If it is 1:12.2, it is in fifths.

If x is the number, Then

If >= 100 then
x -= 40 (same as x = x - 40)
End If

Good Luck

[douglasw32]

BINGO!

this worked
val([field1])-iif([field1]>99,40*val(left([field1],1)),0)


THANK YO SO MUCH !

[Tom]

You probably need to locate the : to cope with 59.2 and/or :59.2.

If your time is the string field1

x: InStr([Field1],":") locates the separator and

time: IIf([x]=0,Val([field1]),IIf([x]=1,Val(Right([field1],Len([field1])-1)),Val(Right([field1],Len([field1])-[x]))+60*Val(Left([field1],[x]-1))))

calculates the time.

Holy Mackeral!
I think it would be easier to RETYPE all the times!:eek:

Just goes to show how much the data whores know/care aobut thier customers.

[rrbauer]

Can you type with one hand and eat a banana with the other?


:)

[Tom]

I eat the bananas with my feet!
Didn't you ever watch TARZAN?:lol:

[Murph]

This is the reason Thorostats added the option to view and save our par times in seconds format as well as min:sec

It's a bit easier to make your own figures with this conversion feature. Just enter your race times in seconds and you can make your calculations straight away.

Murph

[douglasw32]

Well if ANYONE can help, I would love it !

I figure if this works and you can apply logic there must be a way if filed 53 is a 1 meaning a winning race and field 54 is Lengths back to tell 54 that the lengths back are actually the winning margin...

any formula to say hey access if 53 was a 1 lets make 54 a 0

[the_fat_man]

Here's what I do in Perl

sub removecolon {

my ($value) = shift (@_);
if ($value =~ /(.*) : (.*)/) {

return $1 * 60 + $2;
}else {
return $value;
}
}

(No space between the : and the ) or ( )

[raybo]

Well if ANYONE can help, I would love it !

I figure if this works and you can apply logic there must be a way if filed 53 is a 1 meaning a winning race and field 54 is Lengths back to tell 54 that the lengths back are actually the winning margin...

any formula to say hey access if 53 was a 1 lets make 54 a 0

This is what I do in Excel, should be similar in Access:

in field54 (in Excel this would be a cell, say "A54", if the 1 is in cell "A53"), enter this formula in "A54":

=If(A53=1,0,A54)

The commas in the formula represent "then" and "else" respectively.

[ranchwest]

Interesting. The 50 cent TSN files have the time in seconds only and they have one field with lead/beaten and another field for just beaten. None of the above worries are applicable.

[raybo]

Interesting. The 50 cent TSN files have the time in seconds only and they have one field with lead/beaten and another field for just beaten. None of the above worries are applicable.

Yeah, Bris files, too. Converting everything to seconds makes it all easy.