Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    90

    Unanswered: MS Access 97 - Auto "Increase" a letter

    Hi guys and gals.

    I have a table with a field with the default value as "A". I need create a query (preferable to using VB), that when a new record is created the letter "increases" by one - i.e. the new record's value in this field is automatically "B", then the next new one will be automatically "C" and so on.

    Any ideas as to how I can achieve this? Preferably using a query.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what happens after you have 26 rows in this table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    90
    Never going to happen. I can sort out debbuging after I've got this function sorted. Just need this function.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    check this out --
    A default value is the value that is entered in a field any time a new record is added to a table and no value is specified for that particular column. To set a default value for a field, use the DEFAULT keyword after declaring the field type in either an ADD COLUMN or ALTER COLUMN clause.
    Code:
    ALTER TABLE tblCustomers
       ALTER COLUMN Address TEXT(40) DEFAULT Unknown
    Notice that the default value is not enclosed in single quotes. If it were, the quotes would also be inserted into the record. The DEFAULT keyword can also be used in a CREATE TABLE statement.
    Code:
    CREATE TABLE tblCustomers (
       CustomerID INTEGER CONSTRAINT PK_tblCustomers
          PRIMARY KEY, 
       [Last Name] TEXT(50) NOT NULL,
       [First Name] TEXT(50) NOT NULL,
       Phone TEXT(10),
       Email TEXT(50),
       Address TEXT(40) DEFAULT Unknown)
    Note The DEFAULT statement can be executed only through the Jet OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.

    -- Intermediate Microsoft Jet SQL for Access 2000
    sure enough, i tested this in the SQL View window, and she's a no work

    so my guess would be
    Code:
    ALTER TABLE yourtable
       ALTER COLUMN yourfield CHAR(1) DEFAULT B
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •