problem with Len function

[jpl458]

working in older type database that has a field call FeePaid which contains dollars and cents stored as text, and in a strange way. $5.50 is stored as "5.50;0;0;0;0;0;0;0;0". Zero dolars would be stored as "0;0;0;0;0;0;0;0;0". In other words, there are always 16 characters directly to the right of the dollar amount.

In lieu of writing a function that uses a loop to find the length of the dollar amount I tried the following in the QBE grid:

FeePaidNew: Left(FeePaid, Len(FeePaid]-16)

But it gives me a data mismatch error. The problem is in the
"-16". It seems like this should work, and if it did would be a lot easier than writing a function.

Any help or suggestion would be appreciated.

Thanks

jpl

 

[dhookom]

Try:

FeePaidNew: Left(FeePaid, Len(FeePaid][red][b])[/b][/red]-16)

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Left(FeePaid, Len(FeePaid)-16)

change the right brakket to a prentices

 

[vbajock]

If it turns out those extra characters actually contain valid data, you might also want to look at the Split function. If you passed Split the ";" as the delimiter, your first array element would always be the dollar amount and the other elements in the array would contain any other numerical data that you could iterate through if you needed it. Looking at that weird data string, I would almost guess that whatever the original programming language is for that database is using the same kind of "Split" logic somewhere.

 

[dhookom]

PWise's suggestion should work. I see I forg

FeePaidNew: Left(FeePaid, Len([red][b][[/b][/red]FeePaid][red][b])[/b][/red]-16)
[code]


[COLOR=Blue][B][I]Duane
[url=http://www.access.hookom.net/]Hook'D on Access[/url]
[url=https://mvp.support.microsoft.com/profile/Duane.Hookom]MS Access MVP[/url][/I][/B][/color]

 

[Qik3Coder]

You could come at it from the other direction (untested):

FeePaidNew = Mid(FeePaid, 0, InStr(FeePaid, ";"))

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[strongm]

FeePaidNew:Val(FeePaid)