Convert Number to Symbol in Text Box

[Jim Ory]

Using WinXP and Access2002

I have a database where part of a text field contains letters and a number
(yes, just one number). The different locations of the numbers can be as
follows:

X (N) XX; XX (N) X; X (N) XXX; XX (N) XX; and XX (N) XXX, where 'X' is a
letter and (N) is the number. In the text field where the number happens to
be 'zero', I'd like to replace the 'zero' with 'zero with line through' or
the Times New Roman symbol of character code 00D8 from Unicode Hex.

I think this can be done in a module, so that every time a 'zero' is typed,
the symbol will appear. No calculations are based on this text box, but will
it affect other database objects? And will it also work in queries? And most
importantly, How?

Thank you for your suggestions and advice.

 

[Ken Sheridan]

Jim:

Try putting the following in the control's KeyPress event procedure:

If KeyAscii = 48 Then KeyAscii = 216

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Jim:

Sorry, I forgot to answer the second part of your question:

It shouldn't have any effect on other objects, and in queries the relevant
rows will be returned if, for instance, a string expression which contains
the character is used as a value for a criterion on the field in question.

Ken Sheridan
Stafford, England

 

[John Vinson]

Using WinXP and Access2002

I have a database where part of a text field contains letters and a number
(yes, just one number). The different locations of the numbers can be as
follows:

X (N) XX; XX (N) X; X (N) XXX; XX (N) XX; and XX (N) XXX, where 'X' is a
letter and (N) is the number. In the text field where the number happens to
be 'zero', I'd like to replace the 'zero' with 'zero with line through' or
the Times New Roman symbol of character code 00D8 from Unicode Hex.

I think this can be done in a module, so that every time a 'zero' is typed,
the symbol will appear. No calculations are based on this text box, but will
it affect other database objects? And will it also work in queries? And most
importantly, How?

Thank you for your suggestions and advice.

A Module is not necessary; you can use a simple Update query using the
Replace function:

UPDATE yourtable
SET thisfield = Replace([thisfield], "0", Chr(216))
WHERE thisfield LIKE "*0*";

This won't affect any other database objects, but obviously you'll
need to search for Chr(216) instead of 0 in any search criteria.

If you just want this for *DISPLAY* on a form, you can use the Replace
expression as the Control Source of a textbox on the form. The textbox
won't be editable though.

John W. Vinson[MVP]

 

[Jim Ory]

Ken,
Thanks, a nice and simple solution. Works just like I wanted.

 

[Jim Ory]

John,
Thank you for the Updae Query code. Works great. Not only is it for
*Display* on the form, I have one report that uses the symbol also to make
reading much easier.
--
Jim Ory

Using WinXP and Access2002

I have a database where part of a text field contains letters and a number
(yes, just one number). The different locations of the numbers can be as
follows:

X (N) XX; XX (N) X; X (N) XXX; XX (N) XX; and XX (N) XXX, where 'X' is a
letter and (N) is the number. In the text field where the number happens to
be 'zero', I'd like to replace the 'zero' with 'zero with line through' or
the Times New Roman symbol of character code 00D8 from Unicode Hex.

I think this can be done in a module, so that every time a 'zero' is typed,
the symbol will appear. No calculations are based on this text box, but will
it affect other database objects? And will it also work in queries? And most
importantly, How?

Thank you for your suggestions and advice.

A Module is not necessary; you can use a simple Update query using the
Replace function:

UPDATE yourtable
SET thisfield = Replace([thisfield], "0", Chr(216))
WHERE thisfield LIKE "*0*";

This won't affect any other database objects, but obviously you'll
need to search for Chr(216) instead of 0 in any search criteria.

If you just want this for *DISPLAY* on a form, you can use the Replace
expression as the Control Source of a textbox on the form. The textbox
won't be editable though.

John W. Vinson[MVP]