Convert text (YYMM) to numeric retaining the lead zero or zero's (ie Year 2000)

[Andycam]

I have a DB containing over 4 million rows of data, each with a SALES_PERIOD based on YYMM. Our Developers set it up as Text, and index this item accordingly, I would like to format as numeric with the lead zero to allow quicker indexing, but when I format it it returns to text this is the formula I use?



Format((Val([SALES_PERIOD])), "0000")

Can somebody please help me.

Andycam

[June7]

How would leading zero help with indexing?

Numbers can't have leading zeros, only text strings will retain leading zero.

Why do you need leading zero? Don't all the values already have 4 digits?

[Andycam]

Hi June7

Sorry may have misled you, I want quicker indexing, but the developers tell me that text is much slower than numeric, and you can't use the same filters as numeric. That is why I want the text value as numeric with a leading zero.

>Why do you need leading zero? Don't all the values already have 4 digits?

When the text is changed to numeric it changes from 0908 to 908 that is why I wanted the zero.

Andycam

[orange]

Can you tell us more about your application and why "faster indexing" is required?

In my view, you would only use a numeric value if you were using it with some arithmetic calculation, but there may be other circumstances that would warrant numerics.

[June7]

Well, you can't have the values as numeric and retain the leading zeros. Just can't, period. Numbers don't have leading zeros. If there is a leading zero then it isn't a number, it's a text string.

The data should have been saved as a full date value. Dates are really saved by Access as a number.

Try indexing on the value converted to a date, assuming all dates are years 2000 and later:

DateSerial("20" & Left("0908",2), Mid("0908",3,2), "01")

[Andycam]

Thanks, guys, it looks like I can't get the numerics so I will stick to my text. Thanks again for the help, I will use it as is.

That answers my question.

Andycam