Pull only numbers from text field, convert kb to mb. 2

[ponderdj]

Hello,

I have a table with fields: [buying program](contains buyers),[selling program](contains sellers), and [CCN](contains bandwidth).

I need to pull out total bandwidth sold from each seller to each buyer. The [CCN] field is a text field, containing things like 1.5MB, 512KB, and Trash Text that I'll want to ignore.

I'm having trouble pulling only the numbers out of the text field and then converting KB to MB ex: 512KB=.512MB
----> .512

Thanks

 

[PHV]

Val([CCN])/IIf(InStr([CCN],'KB'),1000,1)

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

 

[Golom]

I think its

Val([CCN])/IIf(InStr([CCN],'KB'),[b][COLOR=red]1024[/color][/b],1)

 

[PHV]

Golom, the OP asked for 512KB=.512MB

 

[MichaelRed]

Yes, he asked for the WRONG calculation.





MichaelRed

 

[Golom]

PHV

512KB=.512MB ?

But it doesn't!

512KB = 512 * 1024 = 524288 Bytes

524288 / ( 1024 * 1024 ) = 0.500 MB

 

[ponderdj]

I'll try it out now. Yes, I did ask for the wrong calculation, but it's been a long day. Once again thanks for the help.

 

[ponderdj]

I'm getting an "Undefined function Val in expression" using:

SELECT [Selling Program], [Buying Program], Val([CCN])/IIf(InStr([CCN],'KB'),1024,1) AS Bandwidth
FROM tblISCCSAsbaseline
ORDER BY [Selling Program];

By the way, I'm using Access 2000.

 

[PHV]

Create your own Val function in a standard code module:
Public Function myVal(myString)
myVal = Val(myString)
End Function

And then:
SELECT [Selling Program], [Buying Program], myVal([CCN])/IIf(InStr([CCN],'KB'),1024,1) AS Bandwidth
FROM tblISCCSAsbaseline
ORDER BY [Selling Program];

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