kevcar40 said:
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
