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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

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