By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

convert gb or mb to zeros or decimal places

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.google.c om...
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

Nov 12 '05 #2

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

Function Convert(InputStr As String) As String

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

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

End Function

Phil
"Bubba" <n3***@hotmail.com> wrote in message
news:cc**************************@posting.google.c om...
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.