test for string

[RickM]

Still working on the same problem.
Known error between SCO Unix and Informix when being read by Microsoft
Access.
Example. Informix displays 72.599999 in "mydata" and Access reads it as
72.5: Note the ":" or string
I need an if statement, such as
iif([mydata]="string", Val(mydata)+.1,[mydata])
I've got to test otherwise Val() returns a value 10 cents to low and the
billing department thinks thats unacceptable. Go figure?
But I'll be darned if I can figure out how to test "mydata" to determine if
it's a number or a string.
Thanks for reading and any help you can provide.
Rick

 

[Rickm]

I've been doing some testing and this works

test3: IIf(IIf(Right([value],1)=":",0,1)=0,Val([value])+0.1,[value])*1

value test3
72.60 72.6
72.599 72.599
72.5: 72.6

But isn't there a better way?
Thanks
Rick

 

[Ken Snell [MVP]]

If the presence of a : character is what you're seeking to trap:

test3: Val([value] - (0.1*(IsNumeric([value])=False)))

--

Ken Snell
<MS ACCESS MVP>

I've been doing some testing and this works

test3: IIf(IIf(Right([value],1)=":",0,1)=0,Val([value])+0.1,[value])*1

value test3
72.60 72.6
72.599 72.599
72.5: 72.6

But isn't there a better way?
Thanks
Rick

Still working on the same problem.
Known error between SCO Unix and Informix when being read by Microsoft
Access.
Example. Informix displays 72.599999 in "mydata" and Access reads it as
72.5: Note the ":" or string
I need an if statement, such as
iif([mydata]="string", Val(mydata)+.1,[mydata])
I've got to test otherwise Val() returns a value 10 cents to low and the
billing department thinks thats unacceptable. Go figure?
But I'll be darned if I can figure out how to test "mydata" to determine if
it's a number or a string.
Thanks for reading and any help you can provide.
Rick

 

[RickM]

Thanks, I knew there had to be a better way. One note. I was still getting
the error with your formula until I made the following () change. Appears
to work now, but is it what you intended?

yours: Val([occ_amt] - (0.1*(IsNumeric([occ_amt])=False)))
changed to: Val([occ-amt])- (0.1*(IsNumeric([occ_amt])=False))

Thanks so much for the help and Happy Holidays to you and yours
Rick
--


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

If the presence of a : character is what you're seeking to trap:

test3: Val([value] - (0.1*(IsNumeric([value])=False)))

--

Ken Snell
<MS ACCESS MVP>

I've been doing some testing and this works

test3: IIf(IIf(Right([value],1)=":",0,1)=0,Val([value])+0.1,[value])*1

value test3
72.60 72.6
72.599 72.599
72.5: 72.6

But isn't there a better way?
Thanks
Rick

Still working on the same problem.
Known error between SCO Unix and Informix when being read by Microsoft
Access.
Example. Informix displays 72.599999 in "mydata" and Access reads it as
72.5: Note the ":" or string
I need an if statement, such as
iif([mydata]="string", Val(mydata)+.1,[mydata])
I've got to test otherwise Val() returns a value 10 cents to low and the
billing department thinks thats unacceptable. Go figure?
But I'll be darned if I can figure out how to test "mydata" to determine if
it's a number or a string.
Thanks for reading and any help you can provide.
Rick

 

[Ken Snell [MVP]]

Your correction is correct; I erroneously put the Val function's closing
argument in the wrong place. Good catch. Merry Christmas!
--

Ken Snell
<MS ACCESS MVP>

Thanks, I knew there had to be a better way. One note. I was still
getting the error with your formula until I made the following () change.
Appears to work now, but is it what you intended?

yours: Val([occ_amt] - (0.1*(IsNumeric([occ_amt])=False)))
changed to: Val([occ-amt])- (0.1*(IsNumeric([occ_amt])=False))

Thanks so much for the help and Happy Holidays to you and yours
Rick
--


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

If the presence of a : character is what you're seeking to trap:

test3: Val([value] - (0.1*(IsNumeric([value])=False)))

--

Ken Snell
<MS ACCESS MVP>

I've been doing some testing and this works

test3: IIf(IIf(Right([value],1)=":",0,1)=0,Val([value])+0.1,[value])*1

value test3
72.60 72.6
72.599 72.599
72.5: 72.6

But isn't there a better way?
Thanks
Rick

Still working on the same problem.
Known error between SCO Unix and Informix when being read by Microsoft
Access.
Example. Informix displays 72.599999 in "mydata" and Access reads it
as
72.5: Note the ":" or string
I need an if statement, such as
iif([mydata]="string", Val(mydata)+.1,[mydata])
I've got to test otherwise Val() returns a value 10 cents to low and
the
billing department thinks thats unacceptable. Go figure?
But I'll be darned if I can figure out how to test "mydata" to
determine
if
it's a number or a string.
Thanks for reading and any help you can provide.
Rick