formating Text to Numbers in Query

[uatkd1]

Hi Everyone,

I have created a query and made a table from it, but one of my fields is showing up as a "Text" field when I create the table. I want it to show up as a "Number" field. I am using an expression to extract numbers from it and the expression i am using is:

MPIN: Left$([coeprvid_1],Len([coeprvid_1])-4)

I want the field name "MPIN" to show up as a "number" field instead of a text field . I think it is showing up as a text field because I did this expression. Can you please let me know what i need to do to alter my expression to get it to come in as a "number" field?

Thank you very much in advance....

 

[nanscombe]

To convert it to a number try it as:

MPIN: Val(Left$([coeprvid_1],Len([coeprvid_1])-4))

The Val() function converts a string to a number.


ETA: This might give an error if you have any empty fields though.

 

[boblarson]

Also, don't use Left$. Use LEFT.

Left$ (any of the functions with $) are OLD syntax just hanging around for backwards compatibility.

 

[nanscombe]

After so many years of using Access 97, it's no wonder I still use them.

Must stop it.

 

[uatkd1]

Thank you to both of you! it worked