CreatIng A Table With Dates

[matethreat]

I have a form with 2 Active X Calenders. I use these calenders to view information about jobs between 2 dates in several reports.

What I would like to be able to do now is create a temporary table that creates a unique record for each date between the 2 dates selected in the calender.

I then want to use this table to produce a report that checks for dates in my job table where nothing is scheduled.

But, I am not sure how to go about creating that unique table that lists all dates between 2 dates.

Any help would be appreciated.

Destiny Is Not The Chances We Take, But The Descisions We Make.

 

[lupins46]

You could create the table as a permanent table and use that. Then when you want to use it delete all current records from it and add the new dates you want.

The code below assumes you have a table called tDates with one field called mDate.
The form name is assumed to be MyForm and the start and end date calendars are called cStart and cEnd.

It also assumes that you have a command button on your form called MyButton, which runs the process.


Sub MyButton_Click()
Dim sdate As Date
Dim edate As Date
Dim vDate As Date
Dim strsql

sdate = me.cStart.value
edate = me.cEnd.value
strsql = "Delete * from tdates"
CurrentDb.Execute strsql, dbFailOnError

For vDate = sdate To edate Step 1
strsql = "INsert Into tDates (mdate) values (#" & vDate & "#)"
CurrentDb.Execute strsql, dbFailOnError
Next vDate
msgbox "Dates Created"

End Sub

 

[KenReay]

Assuming your
start date and end date are in textbox controls txtStart and txtEnd
your table is called tblDates and it has a single column of type Date/Time called datDate

Dim datDate as Date

datDate = cDate(txtStart)
docmd.runSQL "DELETE FROM tblDates;"
Do Until datDate >= CDate(txtStart)
docmd.runsql "INSERT INTO tblDates (datDate) VALUES (#" & Format(datDate,"yyyy/mm/dd") & "#);"
datDate = DateAdd("d",1,datDate)
Loop

you may like to look at docmd.setwarnings true/false in help, it will allow you to suppress the Access warning messages on Insert


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

I do not understand the need for the individual dates in a 'recordset'. The Between operator of SQL seem like the appropiate method to select all items from an existing recordset with date fields.





MichaelRed

 

[lupins46]

MichaelRed
How would you show the 'empty' days?

 

[MichaelRed]

Why would one want to showq the "empty" datges? The only instance think of (off-hand anyway) is if the report uses a chart, and the consumers of the report want the scale to be 'regular'. Even then, the chart settings can (usually) be set up to provide a uniform presentation of dates bny adjustiing the major / minor divisions and units. In the generic (ledger?) report showing 'empty' dates would appear to just be a waste of real estate.





MichaelRed

 

[lupins46]

'Why would one want to showq the "empty" datges?'
I suggest you take that up with the developer's customer.

 

[MichaelRed]

take that up with the developer's customer

I did not notice this as a customer] requirement, only as an approach to the generation os a specific data set (e.g. the dates in the range which are NOT in the recordset). Perhaps I'm not bright enough to see through to the end, but I did'n note any customer requirement to use a table of dates.




MichaelRed