Numbers as Text: When to Store Numbers in a Text Field in Microsoft Access. Val Function.

[@ryeaton]

Thank you for the video. I have a topic suggestion that relates to this one: At work, I deal with part numbers from many vendors. They all use a different format: some mix letters and numbers, some are numbers only, some may use periods or hyphens in their numbers, and some use leading zeros. When the database is exported, it is usually in an Excel or CSV format. I notice that Excel doesn’t always handle the part number well: the leading zero might be dropped or large, all-numeric part numbers are displayed in scientific notation and won’t switch until you press F2 and then Enter for each affected cell. Don’t get me started on non-printable characters that find their way in to the database when someone copies the number from a web page or a PDF and our program doesn’t use the TRIM function or check for proper data entry (I’ve asked they add this. They’ve been "looking into it" for a year.). So, when importing into Access to do what I needed to do, there are always a bunch of records that don’t covert properly. I can’t be the only one who experiences this, so with your 20+ years of experience with Access and Excel, perhaps you have a video's worth of tips and tricks to deal with these types of issues. I love the videos. Live long and prosper.

[@599CD]

If you're exporting the data as text (CSV) and your Access fields are TEXT then the data should be enclosed in quotes, which means Excel should have no problem importing them as text.

[@mikenyc1589]

Thx Richard for this...Actually I thought this would would be my fix to a long time issue.......Horse Racing Program numbers......Say you have a 12 horse field......It would come out 1-10-11-12-2 -3-4 etc..Your video would have fixed this but sometimes theres a letter in there for coupled horses ...like this example 1-2-2b-3-3c-4-5-6-7-8-9-10.......your fix would leave out the letters which is needed...I guess I'll live with the issue....

[@599CD]

You can still store them as text and sort whichever way you want (alpha or numeric).

[@z1b900Kawi]

The first couple of times I tried the val function it worked just fine, now the last dozen times, when I select sort in Design view or sort in datasheet view, I keep getting an error and upon clicking the help on the popup get sent to microsoft's "Data type mismatch in criteria expression. (Error 3464)". 95% of the automotive part numbers and none in this table have any alpha mixed in the numeric. Trying to sort 5, 6, 7 & 8 digit (text data type) into numerical order. Thx

[@z1b900Kawi]

In finding other sources of data, the part numbers sometimes have leading zeros, which I can't compare to non-leading zero numbers and get a match, so is the calculated value creating the criteria mismatch in trying use that value as a basis for a second query? TIA

[@599CD]

It's hard for me to help you with this without seeing a sample of the data so why don't you go ahead and email this to me along with some screenshots of your data and explain exactly what you're trying to do. This might make an interesting video. [e-mail]

[@urielramirez27]

Be careful saying that of your SSN. I was hoping you talked about one exception to the numeroliteral test: when having number type will be faster performing than using text type.

[@599CD]

I only said that my SSN begins with zero. Still lots of combinations there. :) And yes, I'll be doing a video in the future about the performance of different data types.