Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

dates + times

Discussion in 'Information Technology' started by kevcar40, Jul 28, 2006.

Tags:
  1. kevcar40

    kevcar40
    Expand Collapse
    Guest

    hi
    I am developing a Database to capure faults in a product
    and i want to return the results by the days number of faults
    problem is
    Access starts the day at 00:00

    i need to tract over the working day ie 06:00 to 05:59 next
    day(24hours)


    is there anyway to set the start end time in access
    if not can anyone help with a query that will do this
    thanks

    kevin
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Pacific Oil slicks spotted in search for missing Malaysia Airlines plane (Live updates) Breaking News Mar 8, 2014
    India Candidates flock to Dera Sacha Sauda Breaking News Jan 17, 2012
    India Seven SAD Candidates of SGPC Win Unopposed Breaking News Aug 27, 2011
    GurmatChanan Updates Gurbani Download Aug 27, 2011

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Why not simply subtract 6 hours from the times so that they coincide with
    how Access sees things?

    Look at DateAdd("h", -6, MyTime) instead of MyTime.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "kevcar40" <kevcar40@btinternet.com> wrote in message
    news:1151571994.898010.237820@i40g2000cwc.googlegroups.com...
    > hi
    > I am developing a Database to capure faults in a product
    > and i want to return the results by the days number of faults
    > problem is
    > Access starts the day at 00:00
    >
    > i need to tract over the working day ie 06:00 to 05:59 next
    > day(24hours)
    >
    >
    > is there anyway to set the start end time in access
    > if not can anyone help with a query that will do this
    > thanks
    >
    > kevin
    >
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    kevcar40 wrote:
    > I am developing a Database to capure faults in a product
    > and i want to return the results by the days number of faults
    > problem is
    > Access starts the day at 00:00
    >
    > i need to tract over the working day ie 06:00 to 05:59 next
    > day(24hours)
    >
    > is there anyway to set the start end time in access
    > if not can anyone help with a query that will do this


    Although I'm not exactly sure what you want to achieve, I'd suggest you
    create a caldendar auxiliary table with the start and end dates for
    each (business) day for your enterprise. A few decades' worth of data
    is still fairly 'cheap'.

    You could join the calendar table to other tables containing date/time
    values to find out e.g. on which business date a value falls.

    Here's some code to create such a calendar table in a new database:

    Sub CreatCalendarTimes()
    Dim cat
    Set cat = CreateObject("ADOX.Catalog")
    With cat
    .Create _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\DropMe.mdb"
    With .ActiveConnection
    .Execute _
    "CREATE TABLE Calendar (" & _
    " dt DATETIME NOT NULL," & _
    " start_date DATETIME NOT NULL," & _
    " end_date DATETIME NOT NULL," & _
    " CHECK (start_date < end_date)" & _
    " );"

    .Execute _
    "INSERT INTO Calendar (dt, start_date," & _
    " end_date) VALUES (#1990-01-01#," & _
    " #1990-01-01 06:00:00#," & _
    " #1990-01-02 05:59:00#);"

    Dim sql
    sql = _
    "INSERT INTO Calendar (dt, start_date," & _
    " end_date)" & _
    " SELECT CDATE(Units.nbr + Tens.nbr + Hundreds.nbr" & _
    " + Thousands.nbr + TenThousands.nbr) AS" & _
    " dt, CDATE(Units.nbr + Tens.nbr + Hundreds.nbr" & _
    " + Thousands.nbr + TenThousands.nbr) + TIMESERIAL(6,0,0)" & _
    " AS start_time, CDATE(Units.nbr + Tens.nbr" & _
    " + Hundreds.nbr + Thousands.nbr + TenThousands.nbr)" & _
    " + 1 + TIMESERIAL(5,59,0) AS end_time FROM" & _
    " (SELECT nbr FROM (SELECT 0 AS nbr FROM" & _
    " Calendar UNION ALL SELECT 1 FROM Calendar" & _
    " UNION ALL SELECT 2 FROM Calendar UNION" & _
    " ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
    " 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
    " Calendar UNION ALL SELECT 6 FROM Calendar" & _
    " UNION ALL SELECT 7 FROM Calendar UNION" & _
    " ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
    " 9 FROM Calendar) AS Digits) AS Units, (SELECT" & _
    " nbr * 10 AS nbr FROM (SELECT 0 AS nbr FROM" & _
    " Calendar UNION ALL SELECT 1 FROM Calendar" & _
    " UNION ALL SELECT 2 FROM Calendar UNION" & _
    " ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
    " 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
    " Calendar UNION ALL SELECT 6 FROM Calendar"
    sql = sql & _
    " UNION ALL SELECT 7 FROM Calendar UNION" & _
    " ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
    " 9 FROM Calendar) AS Digits) AS Tens, (SELECT" & _
    " nbr * 100 AS nbr FROM (SELECT 0 AS nbr" & _
    " FROM Calendar UNION ALL SELECT 1 FROM Calendar" & _
    " UNION ALL SELECT 2 FROM Calendar UNION" & _
    " ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
    " 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
    " Calendar UNION ALL SELECT 6 FROM Calendar" & _
    " UNION ALL SELECT 7 FROM Calendar UNION" & _
    " ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
    " 9 FROM Calendar) AS Digits ) AS Hundreds," & _
    " (SELECT nbr * 1000 AS nbr FROM (SELECT" & _
    " 0 AS nbr FROM Calendar UNION ALL SELECT" & _
    " 1 FROM Calendar UNION ALL SELECT 2 FROM" & _
    " Calendar UNION ALL SELECT 3 FROM Calendar" & _
    " UNION ALL SELECT 4 FROM Calendar UNION" & _
    " ALL SELECT 5 FROM Calendar UNION ALL SELECT" & _
    " 6 FROM Calendar UNION ALL SELECT 7 FROM" & _
    " Calendar UNION ALL SELECT 8 FROM Calendar" & _
    " UNION ALL SELECT 9 FROM Calendar ) AS Digits)" & _
    " AS Thousands, (SELECT nbr * 10000 AS nbr"
    sql = sql & _
    " FROM (SELECT 0 AS nbr FROM Calendar UNION" & _
    " ALL SELECT 1 FROM Calendar UNION ALL SELECT" & _
    " 2 FROM Calendar UNION ALL SELECT 3 FROM" & _
    " Calendar UNION ALL SELECT 4 FROM Calendar" & _
    " UNION ALL SELECT 5 FROM Calendar UNION" & _
    " ALL SELECT 6 FROM Calendar UNION ALL SELECT" & _
    " 7 FROM Calendar UNION ALL SELECT 8 FROM" & _
    " Calendar UNION ALL SELECT 9 FROM Calendar)" & _
    " AS Digits) AS TenThousands WHERE Units.nbr" & _
    " + Tens.nbr + Hundreds.nbr + Thousands.nbr" & _
    " + TenThousands.nbr BETWEEN CLNG(#1990-01-02#)" & _
    " AND CLNG(#2020-12-31#);"
    .Execute sql

    Dim rs
    Set rs = .Execute( _
    "SELECT MIN(dt) AS min_date," & _
    " MAX(dt) AS max_date" & _
    " FROM Calendar;")
    MsgBox rs.GetString
    rs.Close

    End With
    Set .ActiveConnection = Nothing
    End With
    End Sub
     
  5. kevcar40

    kevcar40
    Expand Collapse
    Guest

    Jamie Collins wrote:

    > kevcar40 wrote:
    > > I am developing a Database to capure faults in a product
    > > and i want to return the results by the days number of faults
    > > problem is
    > > Access starts the day at 00:00
    > >
    > > i need to tract over the working day ie 06:00 to 05:59 next
    > > day(24hours)
    > >
    > > is there anyway to set the start end time in access
    > > if not can anyone help with a query that will do this

    >
    > Although I'm not exactly sure what you want to achieve, I'd suggest you
    > create a caldendar auxiliary table with the start and end dates for
    > each (business) day for your enterprise. A few decades' worth of data
    > is still fairly 'cheap'.
    >
    > You could join the calendar table to other tables containing date/time
    > values to find out e.g. on which business date a value falls.
    >
    > Here's some code to create such a calendar table in a new database:
    >
    > Sub CreatCalendarTimes()
    > Dim cat
    > Set cat = CreateObject("ADOX.Catalog")
    > With cat
    > .Create _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=C:\DropMe.mdb"
    > With .ActiveConnection
    > .Execute _
    > "CREATE TABLE Calendar (" & _
    > " dt DATETIME NOT NULL," & _
    > " start_date DATETIME NOT NULL," & _
    > " end_date DATETIME NOT NULL," & _
    > " CHECK (start_date < end_date)" & _
    > " );"
    >
    > .Execute _
    > "INSERT INTO Calendar (dt, start_date," & _
    > " end_date) VALUES (#1990-01-01#," & _
    > " #1990-01-01 06:00:00#," & _
    > " #1990-01-02 05:59:00#);"
    >
    > Dim sql
    > sql = _
    > "INSERT INTO Calendar (dt, start_date," & _
    > " end_date)" & _
    > " SELECT CDATE(Units.nbr + Tens.nbr + Hundreds.nbr" & _
    > " + Thousands.nbr + TenThousands.nbr) AS" & _
    > " dt, CDATE(Units.nbr + Tens.nbr + Hundreds.nbr" & _
    > " + Thousands.nbr + TenThousands.nbr) + TIMESERIAL(6,0,0)" & _
    > " AS start_time, CDATE(Units.nbr + Tens.nbr" & _
    > " + Hundreds.nbr + Thousands.nbr + TenThousands.nbr)" & _
    > " + 1 + TIMESERIAL(5,59,0) AS end_time FROM" & _
    > " (SELECT nbr FROM (SELECT 0 AS nbr FROM" & _
    > " Calendar UNION ALL SELECT 1 FROM Calendar" & _
    > " UNION ALL SELECT 2 FROM Calendar UNION" & _
    > " ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
    > " 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
    > " Calendar UNION ALL SELECT 6 FROM Calendar" & _
    > " UNION ALL SELECT 7 FROM Calendar UNION" & _
    > " ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
    > " 9 FROM Calendar) AS Digits) AS Units, (SELECT" & _
    > " nbr * 10 AS nbr FROM (SELECT 0 AS nbr FROM" & _
    > " Calendar UNION ALL SELECT 1 FROM Calendar" & _
    > " UNION ALL SELECT 2 FROM Calendar UNION" & _
    > " ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
    > " 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
    > " Calendar UNION ALL SELECT 6 FROM Calendar"
    > sql = sql & _
    > " UNION ALL SELECT 7 FROM Calendar UNION" & _
    > " ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
    > " 9 FROM Calendar) AS Digits) AS Tens, (SELECT" & _
    > " nbr * 100 AS nbr FROM (SELECT 0 AS nbr" & _
    > " FROM Calendar UNION ALL SELECT 1 FROM Calendar" & _
    > " UNION ALL SELECT 2 FROM Calendar UNION" & _
    > " ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
    > " 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
    > " Calendar UNION ALL SELECT 6 FROM Calendar" & _
    > " UNION ALL SELECT 7 FROM Calendar UNION" & _
    > " ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
    > " 9 FROM Calendar) AS Digits ) AS Hundreds," & _
    > " (SELECT nbr * 1000 AS nbr FROM (SELECT" & _
    > " 0 AS nbr FROM Calendar UNION ALL SELECT" & _
    > " 1 FROM Calendar UNION ALL SELECT 2 FROM" & _
    > " Calendar UNION ALL SELECT 3 FROM Calendar" & _
    > " UNION ALL SELECT 4 FROM Calendar UNION" & _
    > " ALL SELECT 5 FROM Calendar UNION ALL SELECT" & _
    > " 6 FROM Calendar UNION ALL SELECT 7 FROM" & _
    > " Calendar UNION ALL SELECT 8 FROM Calendar" & _
    > " UNION ALL SELECT 9 FROM Calendar ) AS Digits)" & _
    > " AS Thousands, (SELECT nbr * 10000 AS nbr"
    > sql = sql & _
    > " FROM (SELECT 0 AS nbr FROM Calendar UNION" & _
    > " ALL SELECT 1 FROM Calendar UNION ALL SELECT" & _
    > " 2 FROM Calendar UNION ALL SELECT 3 FROM" & _
    > " Calendar UNION ALL SELECT 4 FROM Calendar" & _
    > " UNION ALL SELECT 5 FROM Calendar UNION" & _
    > " ALL SELECT 6 FROM Calendar UNION ALL SELECT" & _
    > " 7 FROM Calendar UNION ALL SELECT 8 FROM" & _
    > " Calendar UNION ALL SELECT 9 FROM Calendar)" & _
    > " AS Digits) AS TenThousands WHERE Units.nbr" & _
    > " + Tens.nbr + Hundreds.nbr + Thousands.nbr" & _
    > " + TenThousands.nbr BETWEEN CLNG(#1990-01-02#)" & _
    > " AND CLNG(#2020-12-31#);"
    > .Execute sql
    >
    > Dim rs
    > Set rs = .Execute( _
    > "SELECT MIN(dt) AS min_date," & _
    > " MAX(dt) AS max_date" & _
    > " FROM Calendar;")
    > MsgBox rs.GetString
    > rs.Close
    >
    > End With
    > Set .ActiveConnection = Nothing
    > End With
    > End Sub




    thank you both
    great help
     

Share This Page