Calculated field to change a string to a number

[Cyberwombat]

I have a simple Access database that I use to keep track of persons assigned to projects. The project numbers can be a mix of alphanumeric data such as 3205.A, 3205.11 etc. Currently I have the field type set to text but that results in abnormal sorting: 3205.100 comes before 3205.11, for example.

I had hoped to overcome this by using a calculated field to convert the value in the project number from a text to a numeric value, but VAL() is not a valid function for a calculated field.

Is there another way to deal with this? Access tables do not have triggers like SQL Server uses, and I'm not about to migrate to SQL just for that functionality.

Thanks - Dan

[June7]

Do calc to fix the value in query.

Calculated fields were intended for simple calcs like: Price * Quantity

[rpeare]

if you're storing this as a text value you would likely have to break the string into two parts, what comes before the decimal and what comes after, then sort your data based on the left and right parts of the string.


so

left([Fieldname], instr([Fieldname], "."))

might give you the left side and

right([Fieldname], instrrev([fieldname], ".")

might give you the right side.

You will likely have to play around with the second part of the left and right function to properly show string you want then pehaps change them to integers (for sorting purposes) like this:

cint(left([Fieldname], instr([Fieldname], ".")))