Need help converting a Sined field

[Gene]

I'm sure its already been posted but I wasn't able to find anything on this.
I have a table that has a field "Unit Price" which is a sined field. I need
to convert the field to regular number or even better an implied decimal(eg.,
10058=100.58) But dont worry too much about that. Just need a query or a way
to convert the sined field into a large interger. Thanks advance.
Gene

 

[Rick B]

Not sure what you mean by sined field. Do you mean that is can be positive
or negative (signed)?

In any case, to convert it as you indicate, just dived by 100. 10058/100
=100.58

If you want to change the entire table create an update query to change the
value in the field.

 

[Rick B]

On post per question please.

Netiquette FAQ
http://www.mvps.org/access/netiquette.htm

 

[Wayne-I-M]

Hi Gene

Providing that the contents of the field are numerals then you can simply
change the format of the field to number.

To alter this new number to 123.45 from 12345 use an update query (divide
the field by 100)

Hope this helps

Oh before I would change the format and save I would try it on a copy of the
table first - just in case

 

[strive4peace]

Hi Gene,

if you are wanting the DATA TYPE of the resulting colum to be specified,
you can use a data conversion function such as

cLng -- convert to Long Integer (pos and neg ... BIG whole numbers)
cInt -- convert to Integer (limit 32K positive or neg)
cCur -- convert to currency (pos and neg)
etc

about the only numeric data type in Access that is not signed is byte,
which is only good for 0-255

if you want to DISPLAY the column in a particulat format:

turn on the properties window in the design view of the query
(right-click anywhere on query layout and choose Properties from
shortcut menu)

once the properties window is showing, you can type a format for your
number by clicking in the appropriate column and filling the format
property (if you choose from the drop-list, you can also specify the
Decimal property -- specifying Decimal without Format has no effect).


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[John Nurick]

If you mean SIGNED fields, the function below may help. If they also
need converting from EBCDIC, see
http://www.discinterchange.com/TechTalk_signed_fields_.html


This function - author unknown - has been posted in the newsgroups a few
times. Start by importing the signed fields into a text field; then add
a number field to the table; next use fncZonedToNumber in an update
query to populate the number field; and finally delete the text field.


If the last two digits in the signed field represent cents, your update
query will need to divide the values returned by fncZonedToNumber by
100.

Function fncZonedToNumber(ZonedValue As Variant) As Variant


Dim strValue As String
Dim strLast As String


If IsNull(ZonedValue) Then
fncZonedToNumber = Null
ElseIf VarType(ZonedValue) <> vbString Then
fncZonedToNumber = CVErr(5) ' invalid argument
ElseIf Len(ZonedValue) = 0 Then
fncZonedToNumber = Null
Else
strLast = Right(ZonedValue, 1)
strValue = Left(ZonedValue, Len(ZonedValue) - 1)


If InStr(1, "0123456789", strLast, vbBinaryCompare) Then
strValue = strValue & strLast
ElseIf InStr(1, "ABCDEFGHI", strLast, vbBinaryCompare) Then
strValue = strValue & Chr(Asc(strLast) - 16)
ElseIf InStr(1, "JKLMNOPQR", strLast, vbBinaryCompare) Then
strValue = "-" & strValue & Chr(Asc(strLast) - 25)
ElseIf StrComp(strLast, "{", vbBinaryCompare) = 0 Then
strValue = strValue & "0"
ElseIf StrComp(strLast, "}", vbBinaryCompare) = 0 Then
strValue = "-" & strValue & "0"
Else
fncZonedToNumber = CVErr(5) ' invalid argument
Exit Function
End If


fncZonedToNumber = Val(strValue)
End If


End Function