Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Sep 2006
    Posts
    60

    Unanswered: incrementing date fields

    Hi - I have a date field in an Access table which I originally defined as text since it was supposed to be a sort of record number. Now, I have to "auto-increment" the "year" part like this:

    03/06/01
    03/06/02
    03/06/03 etc.

    I tried this in Excel, (where it originally came from) and used the fill-down, series and captured the code from the macro. I don't think the code would work in Access though.

    Can anyone point me in the right direction here? Can Access increment the "year" portion through code or otherwise?

    Many thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes ... Try using the DateAdd function ... In code or otherwise ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    since you have it in a text format try this: dateadd(year,1,cdate([field]))

  4. #4
    Join Date
    Sep 2006
    Posts
    60
    Quote Originally Posted by ODaniels
    since you have it in a text format try this: dateadd(year,1,cdate([field]))
    Well, DateAdd, huh? Who knew? Certainly not me. LOL

    ODaniels - I tried your formula as a new column and Access automatically put a bracket around year giving it a parameter -- [year]. I put in 2001 and it returned column as #ERROR#. I'm using a form in Datasheet mode.

    What am I doing wrong?

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Where "year" is, put "yyyy" (literally) ...

    DateAdd Function:

    DateAdd(Interval,Increment,Source Date)
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Sep 2006
    Posts
    60
    Maybe I should clarify by saying that this fld is supposed to auto-increment, like the auto-number, with the right-most part incrementing. So, as soon as a user comes to a new record in the Datasheet it should immediately increment like this:

    for example: 03/06/01
    03/06/02
    new record 03/06/03

    So, I'm working on it as well...

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here's the question: Are you adding the new record or allowing Access to add it for you?
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Sep 2006
    Posts
    60
    I've nearly got it, I put the formula as:

    ADJ NUM: DateAdd("y",1,CDate([ADJ_NUM]))

    so I get, 03/07/2001
    03/07/2002
    etc. where it should be:

    03/06/2001, 03/06/2002, and so on. I think Access is starting from the middle portion too, where there was 06.

    I want the user to enter a new record and have the ADJ_NUM increment just like auto-number does (courtesy of my boss) as soon as a record is started.

    Dang, why do the seemingly simplest things end up taking the most amount of effort?

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    THAT is because you're using the WRONG interval. For "Year" use: "yyyy". Not 1 y, 4 y's ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Sep 2006
    Posts
    60
    Thanks, I put it as "yyyy". BTW, I had to set the interval to 0 rather than one, it was seeing my ADJ_NUM and incrementing by one. Now, it sees the fld and stays in sync.

    Last part of my problem is how to pare down:

    3/6/2001 for example, to 03/06/01. I'm thinking some kind of string function to do this. So, it's off to the lab to experiment on my creation...Bwa-ha-ha-haaaaaaaa!!!!

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Format ... Format(YourDate,"mm/dd/yy")
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Sep 2006
    Posts
    60
    Great minds think alike, I found Format also, using Help. So, I added a column pointing to the other column with the DateAdd and CDATE function.

    Now, I have to make the Format column auto-increment in my datasheet. Searching through Google for ideas.

    Many thanks for your help, guys...

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by mike21152
    Great minds think alike, I found Format also, using Help. So, I added a column pointing to the other column with the DateAdd and CDATE function.

    Now, I have to make the Format column auto-increment in my datasheet. Searching through Google for ideas.

    Many thanks for your help, guys...
    The control you use (hopefully a textbox) has the Format method ...
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Sep 2006
    Posts
    60
    Owen, at this time, I'm using a query to represent all the fields with function flds included. At some point, I'll turn the query into a datasheet form. My users are used to entering their data in Excel anyway.

    So, to answer your last post, yes, I'll use the column with the Format, but, if I leave the other columns that it's pointing to, out, I'll get a parameter input box asking me for the missing field. Is that what you're asking?

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by mike21152
    Owen, at this time, I'm using a query to represent all the fields with function flds included. At some point, I'll turn the query into a datasheet form. My users are used to entering their data in Excel anyway.

    So, to answer your last post, yes, I'll use the column with the Format, but, if I leave the other columns that it's pointing to, out, I'll get a parameter input box asking me for the missing field. Is that what you're asking?
    Not asking anything ... I'm telling. The rest of your query is of no revelance to this discussion... It can stay or go at your desire ... As for the "new record", you're not gonna find any way to "automate" the "autoincrementation" of the date ... Bite the bullet and add an "Add" feature/option/button to your form so that you can do it gracefully.
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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