Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: Attaching date to autonumber field

    Hi,

    I am looking for a way to attach a date to a autonumber field in access. I would like the field to look like the example below.

    Example: 20070306 - 1

    Any help would be appreciated. If you have questions post and I will answer them.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    did you want the autonumber field to increment a date field? Or did you want the number 1 to be representative of March 6th, 2007? Or is it something else?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "attaching" in access is done with string concatenation

    something like this, perhaps --
    Code:
    select format(datecol,"YYYYMMDD") & ' - ' & id as attached_string
      from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    I suspect jcoe wants to start with 1 each day.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, well, that's possible in mysql, but not in access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest you store the fields seperately.
    TableName(UniqueIdentifier, Date, ...)

    Then as r937 mentioned - concatenate the strings wherever neccessary.

    If you want to start with one each day I think that can be done in access, but not easily... Not easily at all.
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's not so tough to do if you need it.

    there's a demo of sequential numbering in the codebank sticky.
    in the one-record table of the demo, add a date field.
    then, when building your reference, check if now() is a new date (vs the one-record table) and if yes, UPDATE the next number in the one-record table to 1
    ...voila! you have daily sequential numbers for now()

    if you want to use dates other than now() then the one-record table becomes a many record table ...find your date (INSERT if not there) and play the sequence game again.

    izy
    currently using SS 2008R2

Posting Permissions

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