Numeric AND Text

[Guest]

Hi All,

I have a database form which should contain numeric data in a currency
format. However in a few cases we receive a text response such as 90% of fund
instead of £1,234.56.

So i have had to change the field to text, however my users now have to
enter the £ and , and . themselves, which sometimes they forget.

I was wondering if anyone knew of a way to make Access understand that if
only numbers are entered, to make it into a currency format OR if letters are
entered to leave it as text.

I have tried 'format' and 'input mask' but these don't seem to work, can
anyone offer some guidance?

Thanks
Emma

 

[RobFMS]

Emma

I would force the application to allow numbers only. If the field should
contain a dollar amount, then it should only contain a dollar amount. If
there are cases where the user wants to enter a percentage, then create
another field that only allows the entry of percentage. When that field is
entered, then it should calculate the appropriate number and push it to the
dollar amount field.

HTH

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Guest]

The question is, how does the text data get into your table?
I would suggest you pre-edit the incomming data and either programmatically
(if you can) or manually correct the data prior to putting it into your live
table.

 

[Guest]

My users have to write the data they are given which could be "1234.56" or
"90% of fund" etc. I wish to be able to show the field as a currency IF the
field only contains numbers, so the user would type 1234.56 and the form
would show £1,234.56 Or they would type 90% of fund and it would show the
same text "90% of fund". I don't mind what is stored in the table, it is the
visual on the form i am concerned with.

Thanks

 

[Guest]

If that is what you want to do, then in the form's Current Event and the
After Update Event of the control:

If IsNumeric(Me.SomeTextBox) Then
Me.SomeTextBox = Format(Me.SomeTextBox, "Currency")
End If

I haven't tested this to see if it will work exactly like you want it, but
it should be the general idea.