Parse field and perform calculation 2

[Freefall69]

I am running Access 2016 and receiving a file from an external system which lists average and max usage for each interface. The challenge is that the information is all listed in one field to include the usage in Kbps, Mbps, and Gbps and need to do a calculation in that field based on value listed and have the result only contain the calculated value in G-bytes. I need to first look at the field and determine if shown in Kbps. Mbps, or Gbps and then based on that perform the calculation and remove test in resulting field. I am uncertain in the best way to accomplish this and would appreciate any assistance to help obtain the desired results. I have attached an image showing the report values received from system and desired output as well. Goal is to convert all values to be in same unit of measurement of Gbps by examining the field and then performing required calculation.

 

[Andrzejek]

So, you want to convert the [blue]BLUE[/blue] values to BLACK values
[pre]
[blue]
481.23K bps 512.28K bps[/blue] 0.00048 0.00051[blue]
5.20M bps 13.21M bps[/blue] 0.0052 0.013[blue]
4.22G bps 6.91G bps[/blue] 4.22 6.81
[/pre]
How about something simple like:

If InStr(YourValue, "K bps") Then
    [green]'Take value from before 'K bps' and do your magic[/green]
ElseIf InStr(YourValue, "M bps") Then
    [green]'Take value from before 'M bps' and do your magic[/green]
Else
[green]    'Take value from before 'G bps'[/green]
End If

or

Select Case Right(Trim(YourField), 5)
    Case "K bps"

    Case "M bps"

    Case "G bps"

    Case Else

End Select

You could also do it by [tt]IIF[/tt] in your Select statement.


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy,

Thanks for the suggestions. I have it working as desired now.

 

[Andrzejek]

Would you share your solution for the benefit of others who may have the same issue?


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Freefall69,
If Andy's suggestions helped find the solution, please mark his post as "Great post!" so we know it is closed and he gets some kudos.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Freefall69]

Andy,

Actually it's not working. I was able to strip the units (K bps, M bps, and G bps) and thought I could just trim the field values and do calculation but value lengths do vary from 0 to 5 numbers

So in using the InStr statement I have entered the following which is not working correctly to obtain the desired values because additional manipulation must be performed.

CODE
If InStr(YourValue, "K bps") Then
{Average Inbound}/1000^2
ElseIf InStr(YourValue, "M bps") Then
{Average Inbound}/1000
Else
'Take value from before 'G bps'
End If

I believe the values need to be stripped out of field to do calculation but uncertain how to accomplish. Was trying to get something working using few different methods but hopefully can accomplish with just InStr statement for each one (Average Inbound, Max Inbound, etc..)

 

[strongm]

We can cheat a bit to avoid having to strip anything ourselves... have a look at the documentation for the Val function ...

 

[Andrzejek]

>Actually it's not working
What is not working? it is hard to help you if we don't know what you have... :-(

If you would show your actual code, we may try to decipher it


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I would create a small public function in a standard module that would calculate the value:

Public Function GetGbps(strValue As String) As Double
    Dim dblValue As Double
    dblValue = Val(strValue)
    Select Case Right(strValue, 5)
        Case "K bps"
            GetGbps = dblValue / 1000000
        Case "M bps"
            GetGbps = dblValue / 1000
        Case "G bps"
            GetGbps = Val(strValue)
    End Select
End Function

Save this function in a new module named "modConversions". You can then use the function anywhere you would use a standard, built-in function.

You could add an argument to send in if you want G bps, K bps, or M bps returned.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

I know it is 'splitting hair' here...

    Select Case Right(strValue, 5)
        Case "K bps"
            GetGbps = dblValue / 1000000
        Case "M bps"
            GetGbps = dblValue / 1000
        Case "G bps"
            GetGbps = [red]dblValue [/red]
    End Select

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Good catch Andy!

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

And just to have a little fun with it...

Public Function GetGbps(strValue As String) As Double
    Dim dblValue As Double
    dblValue = Val(strValue)
    Select Case Right(strValue, 5)
        Case "K bps"
            GetGbps = dblValue / 1000000
        Case "M bps"
            GetGbps = dblValue / 1000
        Case "G bps"
            GetGbps = dblValue[blue]
        Case Else
            MsgBox "What do you want me to do with this: " & strValue [/blue]
    End Select
End Function

---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Andy/Duane,

Thanks for your help. This is now working correctly.

 

[Freefall69]

Subsequent to running for entire dataset there is an additional calculation that is needed. Since the case statement looks for (strValue, 5) it converts all K bps, M bps but have now discovered some that are just bps where that will not work. Most of the bps are "0" but some also have some values. So need to add an additional case with (strValue, 4) but uncertain how to incorporate this into existing statement. This will also be in most cases 0/10000000000 where the desired result would be "0" or require calculation is there is a value.

Select Case Right(strValue, 4)
Case "bps"
GetGbps = dblValue / 10000000000

 

[Andrzejek]

Public Function GetGbps(strValue As String) As Double
    Dim dblValue As Double
    dblValue = Val(strValue)
    Select Case Right(strValue, 5)
        Case "K bps"
            GetGbps = dblValue / 1000000
        Case "M bps"
            GetGbps = dblValue / 1000
        Case "G bps"
            GetGbps = dblValue[blue]
        Case Else
            Select Case Right(strValue, 3)
                Case "bps"
                    If dblValue = 0 Then
                        ...
                    Else
                        ...
                    End If
                Case Else
                    MsgBox "What do you want me to do with this: " & strValue 
            End Select            
[/blue]    End Select
End Function

PS. And I was trying to be funny with this MsgBox....

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I would modify Andy’s code and substitute the bps calculation into the Case Else.

Also, you didn't provide a sample of the just bps values. Do they have a space prior to the bps like " bps" or is there no space between the number and the letters?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

BTW, you example would never work:

[pre]
Select Case Right(strValue, [highlight #FCE94F]4[/highlight])
Case "[highlight #FCE94F]bps[/highlight]"
GetGbps = dblValue / 10000000000
[/pre]
You are checking last [red]4[/red] characters of the [tt]strValue[/tt] variable, but you compare it to [red]3[/red] characters "bps" :-(


---- Andy

There is a great need for a sarcasm font.

 

[Freefall69]

Thanks for the additional guidance. Working now for all values. I see where that would not work now with the case statement I tried for bps.