Text Formatting (1 Viewer)

Juett · Oct 10, 2019

I have simple piece of text formatting that adds a degrees symbol to any value entered into a text field in a form (also the table).

Code:

@°

This works fine - you type 0.3 and it becomes 0.3°

What I would like to do is have the ability to automatically add the degrees symbol to numbers only, and not for text. E.g: 0.3° or Hello (but not Hello°).

Can you put some kind IF statement or formatting rule inside the format properties box that can do this?

Thanks very much.

NauticalGent · Oct 10, 2019

Have a look at the IsNumeric() Function: If IsNumeric(txtYourTxtBox) Then...

CJ_London · Oct 10, 2019

you can't do it with the format properties - they require a consistent datatype (text, number, date)

and text only has two states - either there is some text, or there isn't (null)

numbers have 4 states - positive, negative, zero and null

so your field will have to be a text type (sounds like it is anyway) and use the isnumeric function in the control afterupdate event as suggested by John to add the ° character.

Juett · Oct 10, 2019

Thanks guys,

Could anyone point me in the right direction to some form of working example of this...I've taken a look around but I can't figure out how to use this function to get what I want.

theDBguy · Oct 10, 2019

Hi. Does it not matter what the numeric value is? For example, is it okay for the user to enter 1000 degrees? Just curious...

Juett · Oct 10, 2019

Well, technically, the values permissible for this particular field are between 0 and 1 or the words "N/A" and "FAIL". I can set that up in the validation property easily enough, but if its relevant to this, that's what it is.

NauticalGent · Oct 10, 2019

In the AfterUpdate Event of your Text filed type:

Code:

If IsNumeric(Me.txtYourTextBox) Then
     Me.txtYourTextBox = Me.txtYourTextBox & Chr(0176)
End If

theDBguy · Oct 10, 2019

Juett said:
Well, technically, the values permissible for this particular field are between 0 and 1 or the words "N/A" and "FAIL". I can set that up in the validation property easily enough, but if its relevant to this, that's what it is.

Hi. Thanks for the clarification. What is the significance of this field? Just wondering if you're wrongly mixing two types of data in one field.

arnelgp · Oct 10, 2019

add Tag (sp_format) to the control (textbox) on your form that you want to format.
then add a function in your form or Module that will do the format.
add code to Form's current and beforeUpdate event to call the function:

Code:

Public Function myFormat(ctl As Access.Control)
If ctl.Tag = "sp_format" Then
    If IsNumeric(ctl.value) Then _
        ctl.Format = "@°"
End If
End Function


Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call Form_Current
End Sub

Private Sub Form_Current()
    Dim ctl As Control
    For Each ctl In Me.Controls
        Call myFormat(ctl)
    Next
End Sub

CJ_London · Oct 10, 2019

Well, technically, the values permissible for this particular field are between 0 and 1 or the words "N/A" and "FAIL". I can set that up in the validation property easily enough, but if its relevant to this, that's what it is.

you can use the format property in that case for numeric types

if you can work with the basis of null=N/A and -1=FAIL then a format property set to

0.0"°";[RED]"Fail";0.0"°";"N/A"

should do the trick

Juett · Oct 10, 2019

John's solution worked perfectly.

Thanks very much.

theDBguy · Oct 10, 2019

Juett said:
John's solution worked perfectly.

Thanks very much.

Hi. Glad to hear you got it sorted out. Good luck with your project.

NauticalGent · Oct 10, 2019

Glad you got it to work. That being said, CJ’s format property is a better solution for what you described.

Additionally, Arnel’s Solution would be better if there are more than one field that this applies too.

Either way, I’m glad we could help.

Text Formatting (1 Viewer)

Juett

Registered User.

NauticalGent

Ignore List Poster Boy

CJ_London

Super Moderator

Juett

Registered User.

theDBguy

I’m here to help

Juett

Registered User.

NauticalGent

Ignore List Poster Boy

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

CJ_London

Super Moderator

Juett

Registered User.

theDBguy

I’m here to help

NauticalGent

Ignore List Poster Boy

Similar threads

Users who are viewing this thread