Instr Function Problem

[LJtechnical]

Hi All

I'm having trouble splitting a decimal number into its two components ie if number = 347.9893

Iwant my query to extract as Field1 = 347 and Field2 = 9893,
I think the trouble is the variation with decimal places used as the following works well for Field1 but ont Field2, where I get a range of return values some correct and some not.

Field1: Left([Expr1],InStr([Expr1],".")-1)
Field2: Right([Expr1],InStr([Expr1],".")+1)

Any ideas

 

[SkipVought]

Hi.

A NUMBER is not a STRING.

f1 = Int([Expr1])
f2 = [Expr1] - f1

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[PHV]

Replace this:
Field2: Right([Expr1],InStr([Expr1],".")+1)
By this:
Field2: Mid([Expr1],InStr([Expr1],".")+1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

PHV,

Why use string functions on a number unless it's an academic exersize?

You've got NUMBER to TEXT conversions and TEXT to NUMBER conversions going on behind the scene. If it were not for the fact that VB covers yer butt in this way, it would crater with 'type missmatches'.

And furthermore, it obscures the fact that numbers are handled differently than strings -- a BIG problem with lots of uses that come to TT ESPECIALLY with respect to Date/Time values where there is even MORE confusion.
[tt]
d
e
s
c
e
n
ding. Ahhhhhh, I feel better![/tt]

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[PHV]

Why use string functions on a number
Just because the OP said that the first calc was OK, so I corrected the second.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

I understand. I've done that sort of thing, too. Just posted a reply regarding shared workbooks that I would NEVER recommend to anyone. Touché

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}