One step leads to another 1

[detgharris]

Dhookom you seem to answer all my questions, and here I am, yet again, with another question. You answered my question about creating an outer join to show all dates, which is the perfect solution, but now I've been searching posts trying to find out how to create a table that will automatically put a consecutive date in each record for the time period that I direct with a query by form.... Hope I didn't confuse you, I'm so confused...... Anyway thanks for all your previous help and I'm sure you're help on this problem.

 

[Darrylles]

Hi det,

Anything is possible, but, that doesn't necessarily mean do it that way.

Why add many virtually empty records - (ready to be filled-in), when they can be added as required, individually - incrementing the new date based on the latest record date?

ATB

Darrylle






Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[detgharris]

I'm not sure I understand what you're saying...
What I'm trying to accomplish is kind of a table on the fly... instead of having a table with 365 records in it, it would fill up with only the dates between "startdate" and "enddate" and then go away after I've printed the report. That way when I want to run a two week report, with all the days(including Sat & Sun) even if they're contain null values, I could do an outer join and get every day... Confused again, sorry....

 

[Darrylles]

Det,

OK. It would have been good to state the thread from your previous solution, as it sounds like you need to convert this to an append query in some way.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[MajP]

If you want to fill a table with consecutive dates between a range and then clear out that data afterwards:

Public Sub fillDates(dtmStart As Date, dtmEnd As Date)
  Dim strSql As String
  Dim dtmTempDate As Date
  Const tbl = "tblDates"
  Const fld = "dtmDate"
  'delete the old values
  strSql = "Delete * from " & tbl
  CurrentDb.Execute strSql
  If IsDate(dtmStart) Then dtmTempDate = dtmStart
  'add the new values
  If IsDate(dtmStart) And IsDate(dtmEnd) And dtmEnd > dtmStart Then
    Do Until dtmTempDate > dtmEnd
      strSql = "INSERT INTO " & tbl & " (" & fld & ") VALUES (#" & dtmTempDate & "#)"
      CurrentDb.Execute strSql
      dtmTempDate = dtmTempDate + 1
    Loop
  End If
End Sub

just change the name of the tbl and fld in the constants.