Query to add 15 mins until value reached

[Taff]

Hi All,

Not sure if this is possible but here goes.

I have a form with a time in the format "hh:nn". What I would like to do is create a query that takes the value from my form and adds a row for each 15 minute increment until it reaches a certain time i.e. 16:30.

So if the time on my form is 14:00 the query would display:-

14:15
14:30
14:45
and so on.

Thanks for any help on this.

Taff.

 

[aleb]

Option Compare Database
Option Explicit

Public Function ExecuteDDL(strSQL As String)
Dim db As DAO.Database
Set db = CurrentDb
db.Execute strSQL
Set db = Nothing
End Function

Public Sub CreateTempT()
Dim s As String
s = "CREATE TABLE Yourtemptable (ID Counter, MyTime DateTime)"
ExecuteDDL (s)
End Sub

Public Sub DeleteTempT()
Dim s As String
s = "DROP TABLE Yourtemptable"
ExecuteDDL (s)
End Sub

Public Function FillTempT(tstart, tend, tInterval)
Dim db As DAO.Database, rst As DAO.Recordset, k As Single
Set db = CurrentDb
Set rst = db.OpenRecordset("Yourtemptable", dbOpenDynaset)
With rst
For k = CDate(tstart) To CDate(tend) Step CDate(tInterval)

.AddNew
!MyTime = CDate(k)
.Update
Next
End With
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Set db = Nothing
End Function

Will you figure out the rest ?

Tips: Button will first create the temp table then run the query which will utilize the filltempt function, display a form then event will delete the temp table.

 

[Mile-O]

Just want to add for Taff the reason why aleb has given a solution that involves creating a new table. You ask for a query that does something (i.e. incrementation) but a query can't do that on its own - it can only work with what records it has in a table although it can refer to forms and functions.

 

[The_Doc_Man]

Further comment: This smells of a scheduler-type application. Further, the question makes me immediately think of someone having a flat-file design. If you really need a table of times like that, you are probably doing it wrong, at least slightly. Access is not like either Excel or Outlook, both of which would address this issue differently. Both of which would also work more like flat files for a scheduler.

Search this forum for many discussions on Scheduler and Calendar as topic names. You will find that this sort of question has come up quite often.