simple formatting

[fishqqq]

I have a text field (where the underlaying table field is set to text
and not number) and i am trying to limit the decimal place of a number
i am populating this field with to 2 decimals.

i know if the field was a Number field instead of a text field this
would not be a problem but how do i get Access2010 to round up to 2
decimals when this isn't set to a number field ( i need this field to
be a text field)

when i set the format option of the field (in the form) to :
([Budget], 2)
before i save it access automatically changes it to "([Bu"d"get], 2)"

how do i set the format to show only 2 decimals???

btw sometimes i need this field populated to read NO BUDGET which is
why it is set as a text field and not number field

thanks
Steve

 

[John W. Vinson]

I have a text field (where the underlaying table field is set to text
and not number) and i am trying to limit the decimal place of a number
i am populating this field with to 2 decimals.

i know if the field was a Number field instead of a text field this
would not be a problem but how do i get Access2010 to round up to 2
decimals when this isn't set to a number field ( i need this field to
be a text field)

when i set the format option of the field (in the form) to :
([Budget], 2)
before i save it access automatically changes it to "([Bu"d"get], 2)"

how do i set the format to show only 2 decimals???

btw sometimes i need this field populated to read NO BUDGET which is
why it is set as a text field and not number field

thanks
Steve

Use the Format() VBA function to cast the number into the text string you
want. Leave the Format property of the field blank - it isn't the same as the
function!

If the data is being manually entered, you could use code in the textbox's
AfterUpdate event:

Private Sub textbox_AfterUpdate()
If IsNumeric(Me!textbox) Then
Me!textbox = Format(Val(Me!textbox), "#.00")
End If
End Sub
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[fishqqq]

I have a text field (where the underlaying table field is set to text
and not number) and i am trying to limit the decimal place of a number
i am populating this field with to 2 decimals.

i know if the field was a Number field instead of a text field this
would not be a problem but how do i get Access2010 to round up to 2
decimals when this isn't set to a number field ( i need this field to
be a text field)

when i set the format option of the field (in the form) to :
([Budget], 2)
before i save it access automatically changes it to "([Bu"d"get], 2)"

how do i set the format to show only 2 decimals???

btw sometimes i need this field populated to read NO BUDGET which is
why it is set as a text field and not number field

thanks
Steve

Use the Format() VBA function to cast the number into the text string you
want. Leave the Format property of the field blank - it isn't the same asthe
function!

If the data is being manually entered, you could use code in the textbox's
AfterUpdate event:

Private Sub textbox_AfterUpdate()
If IsNumeric(Me!textbox) Then
   Me!textbox = Format(Val(Me!textbox), "#.00")
End If
End Sub
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Sorry John,
i don't follow,
this field already has an afterupdate macro attached to it. And I
don't know how to use the VBA function into the string - i don't
understand how to do this?

 

[Access Developer]

this field already has an afterupdate
macro attached to it. And I don't
know how to use the VBA function
into the string - i don't understand
how to do this?

First, it would be a good idea to convert the macro to VBA code, or explain
in detail and ask for help, so you can add the code John gave you either
before or after the code derived from the macro.

Val(T) converts a text value in a variable, or a control, to a numeric value
(provided the text value is numeric characters, numeric operator {e.g., +
or -} characters, commas, and decimal points.

Format then converts a number into characters as specified for display.

That's why John first used Val to convert to a number, then used format to
create the text value you want.

And, because "fields" do not have "After Update" events, I am working on the
assumption that you mean a "Control" on a "Form". (Note: the Form itself can
have an After Update, but that's probably not useful for your purposes.)

Larry Linson, Microsoft Office Access MVP

 

[fishqqq]

 > this field already has an afterupdate
 > macro attached to it. And I don't
 > know how to use the VBA function
 > into the string - i don't understand
 > how to do this?

First, it would be a good idea to convert the macro to VBA code, or explain
in detail and ask for help, so you can add the code John gave you either
before or after the code derived from the macro.

Val(T) converts a text value in a variable, or a control, to a numeric value
(provided the text value is numeric characters, numeric operator {e.g., +
or -} characters, commas, and decimal points.

Format then converts a number into characters as specified for display.

That's why John first used Val to convert to a number, then used format to
create the text value you want.

And, because "fields" do not have "After Update" events, I am working on the
assumption that you mean a "Control" on a "Form". (Note: the Form itself can
have an After Update, but that's probably not useful for your purposes.)

 Larry Linson, Microsoft Office Access MVP

Thanks for trying to explain this to me Larry but i think i'm getting
more confused and the problem is likely quite easy to fix....

first off have a subform in a form
The subform is a continuous subform
The field giving me problems in the subform is a TEXT field that the
user will sometimes put Text in and other times the form will be
updated with a Number.

The number is actually a volume calculation which usually ends up with
many decimal places. ie 100.123456789 etc.
I need to get this to show (in the subform) as 100.12 (format it to 2
decimal places in other words)
(The end use of this is the user can see what the volume calculation
is and leave that data in place for the customer OR if we have agree
pricing for this particular customer the user will overwrite the data
to read " AS AGREED" - showing the customer that the volume
calculation isn't important in this case as we have previously agreed
pricing in place for him. - hope that didn't confusing things more...)

When i go into the property sheet of the form itself the Format
control should allow me to take the TEXT or NUMBERS and format them
somewhat.

What makes sense to me is to use some formatting that will identify if
the data is TEXT (and leave it alone) or NUMBERS (and round them to 2
decimal places). But i don't know what this formatting should be.

I"m sorry if i'm not getting what both you and John are saying but I
simply don't understand. The Val(T) he is referring to is a mystery to
me (even after a google search).

Can you walk me through this please?
Tks
Steve

 

[John W. Vinson]

Thanks for trying to explain this to me Larry but i think i'm getting
more confused and the problem is likely quite easy to fix....

first off have a subform in a form
The subform is a continuous subform
The field giving me problems in the subform is a TEXT field that the
user will sometimes put Text in and other times the form will be
updated with a Number.

The number is actually a volume calculation which usually ends up with
many decimal places. ie 100.123456789 etc.
I need to get this to show (in the subform) as 100.12 (format it to 2
decimal places in other words)

You have not said how you do the calculation.

Leave the Format property of this field blank. Since the field may contain
text or numbers, there is no Format setting that will help you!

Instead, in whatever calculation you're doing - and you still haven't said! -
convert the result to a Text value, e.g. set the field to

Format([Length]*[Width]*[Height], "#.00")

Obviously you will need to adapt this to whatever calculation you're actually
doing.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Steve Long]

Thanks for trying to explain this to me Larry but i think i'm getting
more confused and the problem is likely quite easy to fix....

first off have a subform in a form
The subform is a continuous subform
The field giving me problems in the subform is a TEXT field that the
user will sometimes put Text in and other times the form will be
updated with a Number.

The number is actually a volume calculation which usually ends up with
many decimal places. ie 100.123456789 etc.
I need to get this to show (in the subform) as 100.12 (format it to 2
decimal places in other words)

You have not said how you do the calculation.

Leave the Format property of this field blank. Since the field may contain
text or numbers, there is no Format setting that will help you!

Instead, in whatever calculation you're doing - and you still haven't said! -
convert the result to a Text value, e.g. set the field to

Format([Length]*[Width]*[Height], "#.00")

Obviously you will need to adapt this to whatever calculation you're actually
doing.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,
the calculation is done on a different form altogether.
fShpSpec is a form that the user updates to compare the actual weight
of a shipment with it's chargeable weight .
This form has a field [text260] with it's control set as follows:
=Sum([Lock Total Vol Wgt Kgs]), format = standard and decimal places
set to 3 (we need 3 decimals on this form)

As soon as the calculations are made on this form 'some ' of the data
is xfered to another table
these fields are transferred: [ref], [no of pcs], [gross wgt],[charge
wgt]
*note [charge wgt] is actually CHARGEABLE WEIGHT (which is the higher
of the two values - actual weight of the shipment and the volume
weight of the shipment)
to complicate things even more the [gross wgt] field is a number field
and the [charge wgt] field is a text field. This is because sometime
we need a number in this field and other times we need to overwrite
the number and insert the word "as agree" or other text.

So when the [charge wgt] number gets pasted into this field it usually
is a number with many decimals in it. I need this field to take that
number and round it up to 2 decimals (again remember this is for
numbers only - for text i don't need any formatting done)

This form is then actually being used as a subform (but i don't think
this portion concerns this issue).

to get the volume weight of the shipment i have a field with the
control property set as follows: =(([Lock L]*[Lock W]*[Lock H])*[Lock
QTY])/IIf([Combo229]="CM",6000,366)
(if we format the number to 2 decimals at this stage - it will not
serve the purpose required on this form which needs the number
formated to 3 decimals. Remember there are two forms that need this
number - one = 2decimals the other = 3 decimals)

I hope this helps you understand better how the number is calculated

As you can see it's a bit complicated and i'm almost certain you are
going to suggest a much better way to approach this design but i am so
into this now that i really don't want to redesign how it all works
together. i really just need this darn text field to round up my
numbers to 2 digits.

It always surprises me how difficult something this simple can be.

I appreciate you being patient with me on this John
Steve

 

[John W. Vinson]

So when the [charge wgt] number gets pasted into this field it usually
is a number with many decimals in it. I need this field to take that
number and round it up to 2 decimals (again remember this is for
numbers only - for text i don't need any formatting done)

ok... try this then: put the following code in this textbox's AfterUpdate
event. I'm assuming that the textbox is named "charge wt", Access will change
the blank to an underscore in the Sub name (note: it's best not to use blanks
in control names!)

Private Sub charge_wt_AfterUpdate()
If IsNumeric(Me![charge wt]) Then
Me![charge wt] = Round(Me![charge wt], 2)
End If
End Sub

And... the reason that you're finding this so complicated is that you set it
up in a complicated and nonstandard way. Fields should be atomic; having a
field that is numeric (and needs to support numeric operations such as
rounding), except on alternate Tuesdays when it's got to be Text, IS
complicated and makes your code complicated!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Steve Long]

So when the [charge wgt] number gets pasted into this field it usually
is a number with many decimals in it. I need this field to take that
number and round it up to 2 decimals (again remember this is for
numbers only - for text i don't need any formatting done)

ok... try this then: put the following code in this textbox's AfterUpdate
event. I'm assuming that the textbox is named "charge wt", Access will change
the blank to an underscore in the Sub name (note: it's best not to use blanks
in control names!)

Private Sub charge_wt_AfterUpdate()
If IsNumeric(Me![charge wt]) Then
   Me![charge wt] = Round(Me![charge wt], 2)
End If
End Sub

And... the reason that you're finding this so complicated is that you setit
up in a complicated and nonstandard way. Fields should be atomic; having a
field that is numeric (and needs to support numeric operations such as
rounding), except on alternate Tuesdays when it's got to be Text, IS
complicated and makes your code complicated!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

this 'kinda' works John
When the user manually enters a number with 3 or more decimals into
the field it will round up to 2 decimals just like i require BUT the
value for this field is being pulled from another form (which
calculates the greater of the two between actual weight and volume
weight) an usually returns a value with 3 or more decimals.

I have a macro that runs which opens form[ShpSpec] and pulls this
inforation
so i guess i need to know a way of runniing the afterupdate code you
provided above AFTER my macro updates the field.

the macro is as follows:
item: [Forms]![fXferMAWBPCSWGT]![Charge Wgt]
expression: [Forms]![fShpSpec]![Text147]

I see the action "runcode" in the list available - and i thought i
could paste :

If IsNumeric(Me![charge wt]) Then
Me![charge wt] = Round(Me![charge wt], 2)

here but there is only 1 line available in the runcode item - so this
won't work.

Can you offer any suggestions?

thanks
Steve

 

[John W. Vinson]

I have a macro that runs which opens form[ShpSpec] and pulls this
inforation

You didn't say that, and I don't use Macros much - VBA code is much more
flexible and powerful. But I suspect you could do this in the Macro:

the macro is as follows:
item: [Forms]![fXferMAWBPCSWGT]![Charge Wgt]
expression: Round([Forms]![fShpSpec]![Text147],2)

or else use the Round() expression in the initial calculation on fShpSpec/
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[fishqqq]

I have a macro that runs which opens form[ShpSpec] and pulls this
inforation

You didn't say that, and I don't use Macros much - VBA code is much more
flexible and powerful. But I suspect you could do this in the Macro:

the macro is as follows:
item: [Forms]![fXferMAWBPCSWGT]![Charge Wgt]
expression: Round([Forms]![fShpSpec]![Text147],2)

or else use the Round() expression in the initial calculation on fShpSpec/
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thanks John, that simple solution actually works perfectly!

I'm running into another problem now
how do you do calcuations on text fields?

the number calcuated above is one of a few charges that i need to add
together

I have a button with a macro in the click event as follows:

item = [Forms]![AWB Main Info]![Text251]
expression = round([Weight Charge Prepaid]+[Valuation Charge Prepaid]+
[Tax Prepaid]+[Total Other Charges Due Agent Prepaid]+[Total Other
Charges Due Carrier Prepaid],2)

All the data types are set to TEXT which i believe is stopping my
macro from being able to add up all the charges.

Is there something special i need to do to this expression to tell it
"if" the value is a number then add it to the next number etc and "If"
the value is a text then ignor it?

thanks
Steve

 

[John W. Vinson]

I'm running into another problem now
how do you do calcuations on text fields?

Well... ideally, don't store numbers in Text fields.

Fields in relational databases should be "atomic" - having only one value and
one kind of value. Storing text data in some records, and numeric data in some
other records, is simply BAD DESIGN, unnecessary, and the source of many of
your problems!

the number calcuated above is one of a few charges that i need to add
together

I have a button with a macro in the click event as follows:

item = [Forms]![AWB Main Info]![Text251]
expression = round([Weight Charge Prepaid]+[Valuation Charge Prepaid]+
[Tax Prepaid]+[Total Other Charges Due Agent Prepaid]+[Total Other
Charges Due Carrier Prepaid],2)

All the data types are set to TEXT which i believe is stopping my
macro from being able to add up all the charges.

Is there something special i need to do to this expression to tell it
"if" the value is a number then add it to the next number etc and "If"
the value is a text then ignor it?

It'll be ugly, but you can use

IIF(IsNumeric([Weight Charge Prepaid]), Val([Weight Charge Prepaid], 0) +
IIF(IsNumeric([Valuation Charge Prepaid]), Val([Valuation Charge Prepaid], 0)
+ <etc etc>

This should work but it's an ugly, inefficient getaround which would not be
necessary if your fields were properly Currency datatypes.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Stuart McCall]

I'm running into another problem now
how do you do calcuations on text fields?

Well... ideally, don't store numbers in Text fields.

Fields in relational databases should be "atomic" - having only one value
and
one kind of value. Storing text data in some records, and numeric data in
some
other records, is simply BAD DESIGN, unnecessary, and the source of many
of
your problems!

the number calcuated above is one of a few charges that i need to add
together

I have a button with a macro in the click event as follows:

item = [Forms]![AWB Main Info]![Text251]
expression = round([Weight Charge Prepaid]+[Valuation Charge Prepaid]+
[Tax Prepaid]+[Total Other Charges Due Agent Prepaid]+[Total Other
Charges Due Carrier Prepaid],2)

All the data types are set to TEXT which i believe is stopping my
macro from being able to add up all the charges.

Is there something special i need to do to this expression to tell it
"if" the value is a number then add it to the next number etc and "If"
the value is a text then ignor it?

It'll be ugly, but you can use

IIF(IsNumeric([Weight Charge Prepaid]), Val([Weight Charge Prepaid], 0) +
IIF(IsNumeric([Valuation Charge Prepaid]), Val([Valuation Charge Prepaid],
0)
+ <etc etc>

This should work but it's an ugly, inefficient getaround which would not
be
necessary if your fields were properly Currency datatypes.

I couldn't agree more, but the expression can be much simpler. Val just
returns 0 if no numerics are found in it's argument, so the iif and
isnumeric aren't required (in this scenario)

 

[John W. Vinson]

I couldn't agree more, but the expression can be much simpler. Val just
returns 0 if no numerics are found in it's argument, so the iif and
isnumeric aren't required (in this scenario)

Thanks, Stuart. Good point! But... Does Val() return an error for NULL fields?
If so you'ld need to use

Val(NZ([field]))

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Stuart McCall]

I couldn't agree more, but the expression can be much simpler. Val just
returns 0 if no numerics are found in it's argument, so the iif and
isnumeric aren't required (in this scenario)

Thanks, Stuart. Good point! But... Does Val() return an error for NULL
fields?
If so you'ld need to use

Val(NZ([field]))

Yep, you're right. Incidentally, that would have also been required in the
calls to Val in your iif expression, thereby making it even uglier <g>.
Hopefully it should be becoming obvious to Steve that normalization is the
key to solving all the problems he's having (not to mention others I suspect
he'll encounter further down the road).

 

[fishqqq]

@myunrealbox.com>
wrote:

Thanks, Stuart. Good point! But... Does Val() return an error for NULL
fields?
If so you'ld need to use

Val(NZ([field]))

Yep, you're right. Incidentally, that would have also been required in the
calls to Val in your iif expression, thereby making it even uglier <g>.
Hopefully it should be becoming obvious to Steve that normalization is the
key to solving all the problems he's having (not to mention others I suspect
he'll encounter further down the road).

--

            John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Thanks for all the input guys. I know i'm in over my head here and
problems like this are how i seem to be picking this up.... In future
i will design the txt vs number fields more carefully

In this case i think i've found a rather simple solution but i still
require your help with the expression.

I have 4 fields that are set as text fields and will have numbers and
text in them depending on how they are used.
I"m going to add an addition 4 fields that i will set as number fields
and then xfer the data from the text fields ONLY if the values in them
are numeric. Once in the new number fields i will do the calculations
required and xfer back the value into the original "total field" (hope
you followed all that...)

so what i need assistance with is how to determine if the data in the
text field is a number (in which case transfer it to the correct
number field) or a text (in which case leave it alone). If it's not
too complicated i would also like the numbers in the new number fields
to be rounded to 2 decimal places.

here's what i have in my macro.

setvalue = Forms![AWB Main Info]![xWgt Chg PP]
expression = IIF(IsNumeric([Weight Charge Prepaid]),Val([Weight Charge
Prepaid])

(i tried to borrow from your previous thread - from what i can make
out the IIF(Is Numeric... is saying if the value is a number then do
something. THis expression isn't doing what i require which is : if
the value is a number then put that number in the [xWgt Chg PP]field
and round it to 2 decimals, and if it isn't a number then don't move
anything into the [xWgt Chg PP]field

Can you help me with this expression?

thanks
Steve

 

[John W. Vinson]

I have 4 fields that are set as text fields and will have numbers and
text in them depending on how they are used.
I"m going to add an addition 4 fields that i will set as number fields
and then xfer the data from the text fields ONLY if the values in them
are numeric. Once in the new number fields i will do the calculations
required and xfer back the value into the original "total field" (hope
you followed all that...)

Again:

YOUR DESIGN IS WRONG.

IT WILL JUST CAUSE YOU TROUBLE.

Sorry to yell, but I think now is the time to correct your problems, rather
than making the table more and more complicated and redundant!

Each field should be "atomic", having only one kind of data. If you have text
data it should be in a text field; if you have numeric data it should be in a
different field, a Number or Currency datatype field.

If you have one field with two or three different purposes, different kinds of
data, then you will have unending problems with calculations, with data entry,
with formatting, and with maintaining all the complicated VBA code that you
will need to maintain all of the above.

Your choice - pay now, or pay later! I'd "pay now" by correcting the table
structure so that you're not using the same field for different kinds of data.

so what i need assistance with is how to determine if the data in the
text field is a number (in which case transfer it to the correct
number field) or a text (in which case leave it alone). If it's not
too complicated i would also like the numbers in the new number fields
to be rounded to 2 decimal places.

here's what i have in my macro.

setvalue = Forms![AWB Main Info]![xWgt Chg PP]
expression = IIF(IsNumeric([Weight Charge Prepaid]),Val([Weight Charge
Prepaid])

If you insist on doing it this way, the expression would be

setvalue = Forms![AWB Main Info]![xWgt Chg PP]
expression = IIF(IsNumeric([Weight Charge Prepaid]),Round(Val([Weight Charge
Prepaid]),2), Null)

The table field to which the textbox control [xWgt Chg PP] should be of
Currency (not Number) datatype.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[fishqqq]

I have 4 fields that are set as text fields and will have numbers and
text in them depending on how they are used.
I"m going to add an addition 4 fields that i will set as number fields
and then xfer the data from the text fields ONLY if the values in them
are numeric. Once in the new number fields i will do the calculations
required and xfer back the value into the original "total field" (hope
you followed all that...)

Again:

YOUR DESIGN IS WRONG.

IT WILL JUST CAUSE YOU TROUBLE.

Sorry to yell, but I think now is the time to correct your problems, rather
than making the table more and more complicated and redundant!

Each field should be "atomic", having only one kind of data. If you have text
data it should be in a text field; if you have numeric data it should be in a
different field, a Number or Currency datatype field.

If you have one field with two or three different purposes, different kinds of
data, then you will have unending problems with calculations, with data entry,
with formatting, and with maintaining all the complicated VBA code that you
will need to maintain all of the above.

Your choice - pay now, or pay later! I'd "pay now" by correcting the table
structure so that you're not using the same field for different kinds of data.

so what i need assistance with is how to determine if the data in the
text field is a number (in which case transfer it to the correct
number field) or a text (in which case leave it alone). If it's not
too complicated i would also like the numbers in the new number fields
to be rounded to 2 decimal places.

here's what i have in my macro.

setvalue = Forms![AWB Main Info]![xWgt Chg PP]
expression = IIF(IsNumeric([Weight Charge Prepaid]),Val([Weight Charge
Prepaid])

If you insist on doing it this way, the expression would be

setvalue = Forms![AWB Main Info]![xWgt Chg PP]
expression = IIF(IsNumeric([Weight Charge Prepaid]),Round(Val([Weight Charge
Prepaid]),2), Null)

The table field to which the textbox control [xWgt Chg PP] should be of
Currency (not Number) datatype.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

My little work around seems to be working very well - with your
assistance. Thanks for all the help!

 

[Access Developer]

It's not really possible, is it, that you are too dense to understand that
he said, "if you don't fix it, and get the db structure right _SOONER OR
LATER_ it'll rise up to bite you in the tender places"? Of course, maye
it's someone else who'll have to live with the mess you leave behind?

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

I have 4 fields that are set as text fields and will have numbers and
text in them depending on how they are used.
I"m going to add an addition 4 fields that i will set as number fields
and then xfer the data from the text fields ONLY if the values in them
are numeric. Once in the new number fields i will do the calculations
required and xfer back the value into the original "total field" (hope
you followed all that...)

Again:

YOUR DESIGN IS WRONG.

IT WILL JUST CAUSE YOU TROUBLE.

Sorry to yell, but I think now is the time to correct your problems,
rather
than making the table more and more complicated and redundant!

Each field should be "atomic", having only one kind of data. If you have
text
data it should be in a text field; if you have numeric data it should be
in a
different field, a Number or Currency datatype field.

If you have one field with two or three different purposes, different
kinds of
data, then you will have unending problems with calculations, with data
entry,
with formatting, and with maintaining all the complicated VBA code that
you
will need to maintain all of the above.

Your choice - pay now, or pay later! I'd "pay now" by correcting the table
structure so that you're not using the same field for different kinds of
data.

so what i need assistance with is how to determine if the data in the
text field is a number (in which case transfer it to the correct
number field) or a text (in which case leave it alone). If it's not
too complicated i would also like the numbers in the new number fields
to be rounded to 2 decimal places.

here's what i have in my macro.

setvalue = Forms![AWB Main Info]![xWgt Chg PP]
expression = IIF(IsNumeric([Weight Charge Prepaid]),Val([Weight Charge
Prepaid])

If you insist on doing it this way, the expression would be

setvalue = Forms![AWB Main Info]![xWgt Chg PP]
expression = IIF(IsNumeric([Weight Charge Prepaid]),Round(Val([Weight
Charge
Prepaid]),2), Null)

The table field to which the textbox control [xWgt Chg PP] should be of
Currency (not Number) datatype.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

My little work around seems to be working very well - with your
assistance. Thanks for all the help!