convert gb or mb to zeros or decimal places

[Bubba]

I know it's possible, just don't know how to do it. I have a
spreadsheet that I imported into access. Two of the columns in the
table have Hard Drive space values listed for example 2.45 GB and 453
MB, you get the picture. Both the GB and MB assorted values exist in
both columns.

Is there anyway to convert the GB or MB to 0's, or even better yet
decimal places so I can look for values < or > to 6gb. I already tried
a query, but it seems that in it's current format, access can't
properly run the query, because it's only looking at the numeric value
and not the gb or mb attached to it.

Or is it simpler to do in Excel?

TIA,

Brian

[Allen Browne]

Strictly, 1KB is 1024 bytes.
1MB is 1024 squared.
1GB is 1024 cubed.

Open the Immediate Window (Ctrl+G), and enter:
? CLng(1024) * 1024
1 048 576

? CLng(1024) * 1024 * 1024
1 073 741 824

The conversion to Long is necessary because of the size of the numbers. To
go much larger, you may need to convert to Decimal, i.e. use CDec() instead
of CLng().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bubba" <n3***@hotmail. com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

I know it's possible, just don't know how to do it. I have a
spreadsheet that I imported into access. Two of the columns in the
table have Hard Drive space values listed for example 2.45 GB and 453
MB, you get the picture. Both the GB and MB assorted values exist in
both columns.

Is there anyway to convert the GB or MB to 0's, or even better yet
decimal places so I can look for values < or > to 6gb. I already tried
a query, but it seems that in it's current format, access can't
properly run the query, because it's only looking at the numeric value
and not the gb or mb attached to it.

Or is it simpler to do in Excel?

TIA,

Brian

[Phil Stanton]

Probably need a column in your query calling the following function where
InputStr is defined as text in your table

Function Convert(InputSt r As String) As String

If IsNumeric(Input Str) Then
Convert = CDec(InputStr)
Exit Function
End If

If Right(InputStr, 2) = "mb" Then
Convert = CDec(Left(Input Str, Len(InputStr) - 2) * 1000000)
Exit Function
End If
If Right(InputStr, 2) = "gb" Then
Convert = CDec(Left(Input Str, Len(InputStr) - 2) * 1000000000)
Exit Function
End If

End Function

Phil
"Bubba" <n3***@hotmail. com> wrote in message
news:cc******** *************** ***@posting.goo gle.com...

I know it's possible, just don't know how to do it. I have a
spreadsheet that I imported into access. Two of the columns in the
table have Hard Drive space values listed for example 2.45 GB and 453
MB, you get the picture. Both the GB and MB assorted values exist in
both columns.

Is there anyway to convert the GB or MB to 0's, or even better yet
decimal places so I can look for values < or > to 6gb. I already tried
a query, but it seems that in it's current format, access can't
properly run the query, because it's only looking at the numeric value
and not the gb or mb attached to it.

Or is it simpler to do in Excel?

TIA,

Brian