Database Help PT II

Discussion in 'NZ Computing' started by SharkFOA, Oct 22, 2003.

  1. SharkFOA

    SharkFOA Guest

    Hi,


    Almost there. All I need now is a way to generate a table of dates, i.e.
    21/10/03 until hell freezes over. As basic as possible. WindowsXP &
    Office2003 Access. Any help appreciated.


    Cheers,



    --
    SharkFOA
    SharkFOA, Oct 22, 2003
    #1
    1. Advertising

  2. SharkFOA

    Warwick Guest

    On Wed, 22 Oct 2003 21:21:36 +1300, "SharkFOA" <>
    wrote:

    >Hi,
    >
    >
    >Almost there. All I need now is a way to generate a table of dates, i.e.
    >21/10/03 until hell freezes over. As basic as possible. WindowsXP &
    >Office2003 Access. Any help appreciated.
    >
    >
    >Cheers,


    Well tomorrow = today + 1.
    So start somewhere and increment each successive day by one.
    I can't remember how MS's datetime type works, I think its a double
    with the integer part representing the day (counting from somedate the
    century before last) and the the part after the decimal point it the
    milliseconds expired since midnight (or something). I do remember that
    MS deliberately ignored a leap year at the beginning of last century.
    Might seem trivial but I once used excel to debug and test my own date
    class (and Zellers congruence) and this fact caused me no end of
    confusion.

    HTH
    Warwick
    Warwick, Oct 22, 2003
    #2
    1. Advertising

  3. SharkFOA

    Joe Black Guest

    > Almost there. All I need now is a way to generate a table of dates, i.e.
    > 21/10/03 until hell freezes over. As basic as possible. WindowsXP &
    > Office2003 Access. Any help appreciated.
    >
    > Cheers,
    > --
    > SharkFOA
    >


    Hi SharkFOA

    Here is one way in VBA (you will need to set a reference to DAO)

    Sub AddDates()
    Dim rst As DAO.Recordset
    Dim i As Integer, NumDatesRequired As Integer
    Dim MyDate As Date

    Set rst = CurrentDb.OpenRecordset("tblDates", dbOpenDynaset)
    MyDate = Date 'set first date to today (or whenever)
    NumDatesRequired = 10 'how many dates do you want
    For i = 1 To NumDatesRequired
    rst.AddNew
    rst!DateFieldName = MyDate
    rst.Update
    MyDate = DateAdd("d", 1, MyDate)
    Next i

    Set rst = Nothing
    End Sub

    It is unusual to need a table of dates, do you mind me asking why you need
    it?

    - Joe
    Joe Black, Oct 22, 2003
    #3
  4. SharkFOA

    SharkFOA Guest

    "Joe Black" <> wrote in message
    news:2NAlb.187135$...
    > > Almost there. All I need now is a way to generate a table of dates, i.e.
    > > 21/10/03 until hell freezes over. As basic as possible. WindowsXP &
    > > Office2003 Access. Any help appreciated.
    > >
    > > Cheers,
    > > --
    > > SharkFOA
    > >

    >
    > Hi SharkFOA
    >
    > Here is one way in VBA (you will need to set a reference to DAO)
    >
    > Sub AddDates()
    > Dim rst As DAO.Recordset
    > Dim i As Integer, NumDatesRequired As Integer
    > Dim MyDate As Date
    >
    > Set rst = CurrentDb.OpenRecordset("tblDates", dbOpenDynaset)
    > MyDate = Date 'set first date to today (or whenever)
    > NumDatesRequired = 10 'how many dates do you want
    > For i = 1 To NumDatesRequired
    > rst.AddNew
    > rst!DateFieldName = MyDate
    > rst.Update
    > MyDate = DateAdd("d", 1, MyDate)
    > Next i
    >
    > Set rst = Nothing
    > End Sub
    >
    > It is unusual to need a table of dates, do you mind me asking why you need
    > it?


    Hi Joe,

    Thanks for the response.I have, with a great deal of help, set up a booking
    database which produces a grid from a crosstab query that has room numbers
    down the side and dates across the top and puts an 'x' in each box that
    shows when it is occupied. The crosstab comes back with the error 'too many
    crosstab column headers' as I have cut and pasted the date from a series in
    excel. I need one field that will generate all the dates I need so that I
    can use a from and to date so that they can see a certain period. I have
    done the rest in SQL in Access, cut and pasted, from helpful other people.
    VBA is a mystery to me, I'm afraid.


    Cheers,


    --
    SharkFOA
    SharkFOA, Oct 22, 2003
    #4
  5. Warwick wrote:
    > On Wed, 22 Oct 2003 21:21:36 +1300, "SharkFOA" <>
    > wrote:
    >
    >
    >>Hi,
    >>
    >>
    >>Almost there. All I need now is a way to generate a table of dates, i.e.
    >>21/10/03 until hell freezes over. As basic as possible. WindowsXP &
    >>Office2003 Access. Any help appreciated.
    >>
    >>
    >>Cheers,

    >
    >
    > Well tomorrow = today + 1.
    > So start somewhere and increment each successive day by one.
    > I can't remember how MS's datetime type works, I think its a double
    > with the integer part representing the day (counting from somedate the
    > century before last) and the the part after the decimal point it the
    > milliseconds expired since midnight (or something). I do remember that
    > MS deliberately ignored a leap year at the beginning of last century.
    > Might seem trivial but I once used excel to debug and test my own date
    > class (and Zellers congruence) and this fact caused me no end of
    > confusion.
    >
    > HTH
    > Warwick
    >


    Excel actually adds a leap year when it is not supposed to. It makes
    1900 a leap year and it shouldn't be.

    In the Gregorian calendar, there is a leap year every year divisible by
    four except for years which are both divisible by 100 and not divisible
    by 400.
    http://scienceworld.wolfram.com/astronomy/LeapYear.html

    One of the Y2K issues was that 19"00" was not a leap year but 20"00" is.

    It is weird though because excel does know that 2100, 2200, etc are not
    leap years.

    Excel time format is just days from 12:00am on 0/1/1900 (no such day).
    so 1 = 1/1/1900 12:00am, 1.5 = 1/1/1900 12:00pm, etc

    In excel there is also an option to use the 1904 date system, in which
    0 = 1/1/1904 12:00am, 0.5 = 1/1/1904 12:00pm , 1 = 2/1/1904 12:00am, etc

    Warren
    Warren Mannington, Oct 22, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guy Cox
    Replies:
    1
    Views:
    1,194
    sharon
    Jul 9, 2003
  2. Fluker
    Replies:
    0
    Views:
    1,030
    Fluker
    Jul 9, 2003
  3. Robert
    Replies:
    7
    Views:
    2,267
    Johnretlaw
    Dec 20, 2008
  4. DataBase DataBase DataBase DataBase

    , Sep 26, 2012, in forum: Computer Information
    Replies:
    0
    Views:
    929
  5. Database Database Database Database

    , Sep 27, 2012, in forum: Computer Information
    Replies:
    0
    Views:
    790
Loading...

Share This Page