Changing The Data Type from Float to???

Question

I have a object with a data type of float, I need to change the data type to something that will allow a character to be added to the number but I don't want to use the wrong data type. The object is a simple number like 26273 and I would like to add a character like 26273B2. What data type would be wise to use that would not cause problems with the previous data all ready saved?

Answer 1

You will want to use the Text type. That will allow numbers and characters.

Answer 2

I guess that field isn't used to do calculations so TEXT would be good for you.

Here is an useful table of MS Access field types.

Answer 3

Possibly nvarchar if you wish to use a smaller field than text, and fix the length. varchar if you do not wish to use international character sets. nvarchar, I believe has more efficient indexing in most databases than text fields

Answer 4

You may want to add a separate field for the suffic and concatenate the two fields in various read only forms and reports.

One reason is sequencing of data. When you put a number in a text field the following non intuitive sorting happens In your situation something like

26273B2
262893C4
26295A2
262966Q3
26297M3

will now happen. Whereas with the numeric field and a text field it will be

26273 B2
26295 A2
26297 M3
262893 C4
262966 Q3

Well, I tried to insert a space in front of the first three numbers in the above list but I have no idea how to force that with this HTML editor.

Answer 5

Use a NVARCHAR column (a.k.a. Text) but if you only want to allow only digits and one or zero letters then you will need to add a CHECK constraint or column-level Validation Rule. Let's assume you currently allow only positive integer values values between 1 and 9999999 (seven digits), you new table structure could look like this (Access database engine syntax in ANSI-92 Query Mode):

CREATE TABLE AccountNumbers (
account_nbr VARCHAR(8) NOT NULL UNIQUE, 
CHECK (
          RIGHT('0000000' & account_nbr, 8) LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][A-Z][0-9][0-9][0-9][0-9][0-9][0-9]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][0-9][A-Z][0-9][0-9][0-9][0-9][0-9]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][0-9][0-9][A-Z][0-9][0-9][0-9][0-9]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][0-9][0-9][0-9][A-Z][0-9][0-9][0-9]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][0-9][0-9][0-9][0-9][A-Z][0-9][0-9]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][A-Z][0-9]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'
       OR RIGHT('0000000' & account_nbr, 8) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
      )
);