In my database I have a field Spec No which is an auto number type, I have added a format so that the number is prefixed with "M000".
However on my form the pre-fix does not show up in the column on the flexgrid, so if I do a sort on one of the other columns and then sort the Spec No. column numerically I get 1,10,11,2, etc.
How can I get the numbers to run in proper numerical order, I have tried to Format, FormatNumber etc etc , even tried shouting at it but that didn't work either. I have also trawled the net for the last 3 hours without any success either.
I have a form with a DAO data control and flexgrid on it.

Help please      

Hi gandalf,
The problem is that the letters you are trying to prefix to the number are recognised by the format proceedure as part of the format - "M" would display a month, "0" is a placeholder for a number.
What you need to do is retrieve the value from the flexgrid into a string variable, adding the prefix, then put it back into the cell.
Replace your format line with :-

Dim strSpecNo as String strSpecNo  = "M000" & FlexGrid1.Text FlexGrid1.Text = strSpecNo

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hi Geoffs

Thanks for the reply, which works up to a point, when I first open the form the Spec No column had no prefixes attached to the numbers. When I click on one of the other columns to sort and then sort again by the SpecNo column it adds the "M000" prefix to one of the numbers. This adding the prefix happens everytime i click the column header, the consequence is that once all the SpecNo's have the prefix added the program then adds a second prefix to one of the numbers on the next click, giving me a SpecNo of M000M000001. Hmmmm

Any ideas.   

I have solved the problem by using the following code to sort the columns,

Private Sub MSFlexGrid1_Click()
MSFlexGrid1.Sort = 3
End Sub

I can then add the prefix to the number afterwards when I want to copy the data onto another form.

Thanks for your help.   

Hi gandalf
Good solution. But why not try modifying the SpecNo when it is created by using the routine we discussed in your thread "Appending to an Auto-number". Then store it in the database in addition to the Autonumber field which remains as the primary index.
Although that will increase the size of the database slightly I believe that it will be a good trade-off 'cos the flexgrid will load much faster without having to go backwards and forwards with formatting the data and then re-loading into the cell.
Of course you would have to run a small routine to create the new modified SpecNo for existing records, but that would just be a case of running the SQL that you use in the main "new number" function .
What do you think?

  

____________________________
multi-tasking - the ability to hang more than one app. at the same time.