Format of number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am bringing in a text file, modifying it for number formats, and then using
it in an OLAP within Excel. However one of the field is Period Number, 1 to
12, and I have tried formatting this to a number but it always ends up in the
OLAP as a general format which will not sort correctly, therefore periods
10,11, and 12 follow period 1 and are before period 2.

Any ideas about how I can overcome this ?

Thanks
 
Since they are only going to be 1 to 12, the easiest fix would be to prefix a
zero to the sigle digit numbers i.e. 01, 02, 03 etc

Steve
 
Steve, yes I think this would work, hoever I am not nimble in Access, there
are hundreds of thousands of rows, are you able to prescribe a quick way in
which I might add the 0 to all of these
 
Your field will need to be a text field instead of number. Run an update
query to change the existing field or to populate a new field. The formula
should be like:
ChangePeriod: Format([Period],"00")

Hope this helps.
Jackie
 
Jackie, I have created the query and in the 3rd row down it says "Update to"
and this is where I have put your expression, the relevant field being called
Period as you guessed. Is this the exact syntax because I am getting a syntax
error message.

Thanks

Jackie L said:
Your field will need to be a text field instead of number. Run an update
query to change the existing field or to populate a new field. The formula
should be like:
ChangePeriod: Format([Period],"00")

Hope this helps.
Jackie

Hood said:
Steve, yes I think this would work, hoever I am not nimble in Access, there
are hundreds of thousands of rows, are you able to prescribe a quick way in
which I might add the 0 to all of these
Click to expand...
Click to expand...
 
Jackie

ignore previous, I have now got it working, thanks

Jackie L said:
Your field will need to be a text field instead of number. Run an update
query to change the existing field or to populate a new field. The formula
should be like:
ChangePeriod: Format([Period],"00")

Hope this helps.
Jackie

Hood said:
Steve, yes I think this would work, hoever I am not nimble in Access, there
are hundreds of thousands of rows, are you able to prescribe a quick way in
which I might add the 0 to all of these
Click to expand...
Click to expand...
 
Jackie

although I thought I had it working it is not. This is what I put

Format([Field8]),"00")

It did work on one test file but I now cannot recreate it, I get

The expression you entered contains invalid syntax, or you need to enclose
your text data in quotes

Any further tips, I can hardly credit that this one small issue is
scuppering my while project !!

Thanks for any advice you can give

Jackie L said:
Your field will need to be a text field instead of number. Run an update
query to change the existing field or to populate a new field. The formula
should be like:
ChangePeriod: Format([Period],"00")

Hope this helps.
Jackie

Hood said:
Steve, yes I think this would work, hoever I am not nimble in Access, there
are hundreds of thousands of rows, are you able to prescribe a quick way in
which I might add the 0 to all of these
Click to expand...
Click to expand...
 
Back
Top