Format numeric field in forms

[aanders]

Background:
I have a table (Table 1) with a field (F1) that has a numeric value. The data in this table is imported trough an excel and is going to be updated regularly.

F1 consists of a four digit number (1234) but if the first one, two or three numbers are a zero (ie. 0033) Access only display "33". I have solved this in Querys by using: Format([Table 1.[F1];"0000").

Question:
How do i use Format([Table 1.[F1];"0000") or similar when i display data from Table 1 -> F1 in a form?

 

[James Dudden]

Same way - just put the form into design mode and open the properties for the relevant control and you can add your format to the format property.

 

[missinglinq]

In Form Design View, with F1 selected, go to Properties - Format and set the Format Property to 0000 (no quotes.)

Linq ;0)>

 

[aanders]

missinglinq and James Dudden
That didn't work (The format field appears to be a drop down where i can choose "euro", "date", "true/false" and some other options). F1 appears the same (in the case i loked at, it is "6" instead of "0006".

 

[James Dudden]

So just to confirm you have typed 0000 into the format property for that control with no quotes?

 

[aanders]

So just to confirm you have typed 0000 into the format property for that control with no quotes?

Yes, that is correct.

 

[pr2-eugin]

Can you show us a screenshot of the properties you have set.. It should look something like..

 

[aanders]

pr2-eugin
Tried to, but for some reason my browser dies when i try to attach an image...

But my properties for the F1 field looks exactly like the properties in your post.

 

[missinglinq]

missinglinq and James Dudden
That didn't work (The format field appears to be a drop down where i can choose "euro", "date", "true/false" and some other options).

But like many dropdowns, you can type an entry in without using the dropdown!

The only way I can get a Format of

0000

not to work is if the Field is defined as Text! All Fields composed of digits are not actually numbers!

Linq ;0)>

Linq ;0)>

 

[aanders]

missinglinq
The field appears to be defined as text. And i do not want to change this since i will have to import a LARGE excel-file quite often to Table 1, so it's default import settings should be used.

Is there any way to solve this?

 

[RainLover]

What is the Data in Excel.

Text or Number.

 

[aanders]

RainLover:
No idea. Can field have different properties in Excel?

 

[RainLover]

Every Cell in Excel has properties. This includes Font size, colour etc.

You can set a cell to General or Number or Currency etc.

You need to do the same in Access. You need to set the Data Type. You need to play with this until it accepts the format that you want. But it needs to be a number not text. You can add numbers together but you can't add text.

Once you have this sorted then you can import into the correct type of field namely a number.

Does this help.

 

[aanders]

RainLover
Thanks for the reply!

I really don't want to edit the input data field. It is MASSIVE and if i start to edit it, i will have do remember to edit it many, many times a year since i get a similiar input file 8 - 12 times a year.

Is there no other way around this?

 

[RainLover]

Once you set up your table in Access it never changes.

How many records are you going to import. 1,000,000 or 1,000,000,000.

I feel that I am not understanding your problem. Originally you wanted 0001 to display instead of 1. Do you want a number or text?

 

[aanders]

Once you set up your table in Access it never changes.
And there will not be any problem when i import new data to this table?

How many records are you going to import.
It is in the range of 10 000+.

Originally you wanted 0001 to display instead of 1. Do you want a number or text?
I want to display "0001" instead of 1.

 

[RainLover]

Once you set up your table in Access it never changes.
And there will not be any problem when i import new data to this table?

How many records are you going to import.
It is in the range of 10 000+.

Originally you wanted 0001 to display instead of 1. Do you want a number or text?
I want to display "0001" instead of 1.

I would say that you have all the answers that you need.

The only thing is, And there will not be any problem when i import new data to this table? No one can give you a guarantee at this stage, so just give it a go and see if there are any problems.

 

[aanders]

RainLover
Ok, i will give it a go - but i will come back to haunt you if something goes wrong (Just kidding - thanks for your help!)

 

[missinglinq]

Assuming that you don't need to edit F1, you can

• Create a simple Query, using your Table
• Create a Calculated Field in the Query, call it Format_F1
• Base the Form on the Query

Now to create the Calculated Field, enter this in this in a blank Name Field in the Query Grid:

Format_F1: IIf(Len([F1])=1,"000" & [F1],IIf(Len([F1])=2,"00" & [F1],IIf(Len([F1])=3,"0" & [F1],[F1])))

Just use the Field Format_F1 on your Form instead of the F1 Field.

Linq ;0)>

 

[RainLover]

No problems. Haunt away.

If you have been told something wrong we will help fix it for you.

After all we are only human.