0

We are working on an application using MS Acces 2003 and SQL Server 2005.
We are saving a fields that contain a text data for example 002215.28 but we want to display it on the screen in a special format for example like this 00 22 15.28.

To do so on the Form Design toolbar and in the Format property box, we type a custom format
00 00 00.##\.####
but when we open the form screen the data appear like it was saved in the table

Please may you advise

Nick Dandoulakis
  • 42,588
  • 16
  • 104
  • 136
CREFLY
  • 19
  • 1
  • 3

1 Answers1

1

It seems that the column may be a text column, if so, you need say, @@ @@ @@\.@@

To format the control using number formats, you must first convert to number with, say, Val:

=Val([TextString])

Make sure that the control does not have the same name as the column, call it, say txtTextString.

There are various disadvantages to this, including:

  • The control is not editable
  • Val will return zero for alphas, giving 00 00 00..
  • Val will return an error for Null values

The last two are not difficult to work around.

If the first is a problem, you may have to consider some VBA to fill the field.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thanks Renou But when I am using the @ in the format of this field, it's affecting the value from the right to the left and not from the left to the right so If I have 001122 and the format is @@ @@ @@ teh result is 00 11 22 which is good but if I have 001122.33 the result will be 00112 2. 33 we are working to find a good format Any help will be appreciated Thank you – CREFLY Jun 30 '10 at 06:55