I have ODBC to a mainframe computer. When I bring in a field from one
of its tables, it shows it as, for example, 200.000000. I want access
to simply show 200. But access doesn't understand that this is a
number. So just changing the decimal to 0 in a form won't work. In a
query, there is no option to make the decimal 0 because that property
isn't even available, Access believing the field text.

But the field is definately a number and cannot have anything else but
a number in it.

So how do I clean this up?

I use the data in other ways in the database, but for that I paste the
data into tables that have the field already marked as a number. But
I cannot do that for this specific project; it has to work live.

This data is currently in the subform of a form, the subform working
directly off the linked table. But I can have it work off a query
instead.

Thanks,

Matt

Try this
=Format([YourFieldName], "#,###")
--
Please remember to mark this as answered if this solves your problem.


"(E-Mail Removed)" wrote:

> I have ODBC to a mainframe computer. When I bring in a field from one
> of its tables, it shows it as, for example, 200.000000. I want access
> to simply show 200. But access doesn't understand that this is a
> number. So just changing the decimal to 0 in a form won't work. In a
> query, there is no option to make the decimal 0 because that property
> isn't even available, Access believing the field text.
>
> But the field is definately a number and cannot have anything else but
> a number in it.
>
> So how do I clean this up?
>
> I use the data in other ways in the database, but for that I paste the
> data into tables that have the field already marked as a number. But
> I cannot do that for this specific project; it has to work live.
>
> This data is currently in the subform of a form, the subform working
> directly off the linked table. But I can have it work off a query
> instead.
>
> Thanks,
>
> Matt
>

Thanks. It works as far as cleaning it up goes. But the query still
left justifies the data and cells appear null if the values were ".
00000000". I could clean it up by nesting it or using another query, I
suppose. So it changes 45.00000000 to 45 but still doesn't understand
it is a number.

Matt

Try one of the following.

CDBL(TheField)

Or
Val(TheField)

IF the field can hold non-numeric data such as the string "Missing" or
just be null, you cannot use CDBL.
Val will work with strings (not nulls) and Val changes any non-number
into zero.

In a query you could use
IIF IsNumeric(TheField),Val(TheField),Null)

In VBA you would need
IF IsNumeric(TheFieldValue) Then
XVariant = Val(TheFieldValue)
ELSE
XVariant = Null
END IF

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


(E-Mail Removed) wrote:
> I have ODBC to a mainframe computer. When I bring in a field from one
> of its tables, it shows it as, for example, 200.000000. I want access
> to simply show 200. But access doesn't understand that this is a
> number. So just changing the decimal to 0 in a form won't work. In a
> query, there is no option to make the decimal 0 because that property
> isn't even available, Access believing the field text.
>
> But the field is definately a number and cannot have anything else but
> a number in it.
>
> So how do I clean this up?
>
> I use the data in other ways in the database, but for that I paste the
> data into tables that have the field already marked as a number. But
> I cannot do that for this specific project; it has to work live.
>
> This data is currently in the subform of a form, the subform working
> directly off the linked table. But I can have it work off a query
> instead.
>
> Thanks,
>
> Matt

It certainly changes, for example, 50.000000 to 50 and .00000000 to a
null cell, but it also left justifies the results and still doesn't
understand the results are numbers. I suppose I can nest the code to
make .0000000 a ture 0, and perhaps a subsequent query would
understand it as a number. Queries don't seem to interpret a field as
a number if there are nulls in the data.

Thanks,

Matt

Create another field in your query and since it will not be bound to the
table you can change the format to number. The column would look something
like this
RoundedNumbers:IIf([TheFormatedField] Is Null, 0,[TheFormatedField])
Then you can right click your new field and set the format property to
number, and the decimal places to 0.
--
Please remember to mark this as answered if this solves your problem.


"(E-Mail Removed)" wrote:

> It certainly changes, for example, 50.000000 to 50 and .00000000 to a
> null cell, but it also left justifies the results and still doesn't
> understand the results are numbers. I suppose I can nest the code to
> make .0000000 a ture 0, and perhaps a subsequent query would
> understand it as a number. Queries don't seem to interpret a field as
> a number if there are nulls in the data.
>
> Thanks,
>
> Matt
>
>

Thanks. The Val works just fine.

Matt