 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / June 2007Format of number
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 > 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 [quoted text clipped - 5 lines] > > Thanks 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 > 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 [quoted text clipped - 10 lines] > > > > Thanks 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 > 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 [quoted text clipped - 14 lines] > > > > > > Thanks 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 > 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 [quoted text clipped - 22 lines] > > > > > > > > Thanks Jackie ignore previous, I have now got it working, thanks > 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 [quoted text clipped - 22 lines] > > > > > > > > Thanks 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 > 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 [quoted text clipped - 22 lines] > > > > > > > > Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.