list of dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey,

I have a query that looks up data in 'tblInventory' and returns
information that is between a date range. IE 'Between #7/1/05# AND
#7/31/05#. The problem is that there is not always data for each date in the
range. I am looking for a way to have the query return ALL dates in the
range.

Any ideas?

Thanks,
Ernst.
 
Create a table with consecutive numbers zero through the maximum number of
days to fill. Create a query with your table and the count table not joined.

SELECT YourTable.Start, YourTable.End, [start]+[Count] AS [Interval Date]
FROM YourTable, [Count]
WHERE (((Count.Count)<=DateDiff("d",[start],[end])));
 
Create a table with consecutive numbers zero through the maximum number of
days to fill. Create a query with your table and the count table not joined.
Click to expand...

Ok... tblCount has 32 records from 0 to 31.
SELECT YourTable.Start, YourTable.End, [start]+[Count] AS [Interval Date]
FROM YourTable, [Count]
WHERE (((Count.Count)<=DateDiff("d",[start],[end])));
Click to expand...

Actually there is only one field... [InvDate] has records between two
dates... There is no [Start] AND [Stop] not quite sure how to fix this...

Ernst.
 
I jumped the gun on posting. Here is what I should have posted.

Create a table with consecutive numbers zero through the maximum number of
days to fill. Then the two queries from the SQL below.

SELECT tblInventory.InvDate, DateSerial(Year([Enter start
date]),Month([Enter start date]),Day([Enter start date])) AS Start,
DateSerial(Year([Enter end date]),Month([Enter end date]),Day([Enter end
date])) AS [End]
FROM tblInventory
WHERE (((tblInventory.InvDate) Between [Enter start date] And [Enter end
date]));

SELECT [Start]+[Count] AS [Interval Date], Query33.Start, Query33.End
FROM [Count], Query33
WHERE (((Count.Count)<=DateDiff("d",[Start],[End])))
GROUP BY [Start]+[Count], Query33.Start, Query33.End;


Ernst Guckel said:
Create a table with consecutive numbers zero through the maximum number of
days to fill. Create a query with your table and the count table not joined.
Click to expand...

Ok... tblCount has 32 records from 0 to 31.
SELECT YourTable.Start, YourTable.End, [start]+[Count] AS [Interval Date]
FROM YourTable, [Count]
WHERE (((Count.Count)<=DateDiff("d",[start],[end])));
Click to expand...

Actually there is only one field... [InvDate] has records between two
dates... There is no [Start] AND [Stop] not quite sure how to fix this...

Ernst.

Click to expand...
 
Thanks... I was able to modify your first post to suit my needs... I ended
up with this: Works great :) Thanks again...

Private Sub Report_Open(Cancel As Integer)

Dim qSQL As String
Dim sStart As String
Dim sStop As String
Dim sQuote As String

sQuote = Chr(34)
sStart =
Format(FirstDay(DateSerial(Year(Forms("frmStartup").cldMain.Form.txtInvDate),
_
Month(Forms("frmStartup").cldMain.Form.txtInvDate), 1)), STRING_DATE)
sStop =
LastDay(DateSerial(Year(Forms("frmStartup").cldMain.Form.txtInvDate), _
Month(Forms("frmStartup").cldMain.Form.txtInvDate), 1))

qSQL = "SELECT DISTINCT " & sStart & "+[DayNumber] AS [Interval Date], " _
& "ltCount.DayNumber FROM ltCount, tblInventory WHERE " _
& "(((" & sStart & "+[DayNumber]) Between Firstday(" & sStart & ") And " _
& sStop & ") AND ((ltCount.DayNumber)<=DateDiff(" & sQuote & "d" &
sQuote & "," & sStart & "," & sStop & ")));"

Me.RecordSource = qSQL
End Sub

KARL DEWEY said:
I jumped the gun on posting. Here is what I should have posted.

Create a table with consecutive numbers zero through the maximum number of
days to fill. Then the two queries from the SQL below.

SELECT tblInventory.InvDate, DateSerial(Year([Enter start
date]),Month([Enter start date]),Day([Enter start date])) AS Start,
DateSerial(Year([Enter end date]),Month([Enter end date]),Day([Enter end
date])) AS [End]
FROM tblInventory
WHERE (((tblInventory.InvDate) Between [Enter start date] And [Enter end
date]));

SELECT [Start]+[Count] AS [Interval Date], Query33.Start, Query33.End
FROM [Count], Query33
WHERE (((Count.Count)<=DateDiff("d",[Start],[End])))
GROUP BY [Start]+[Count], Query33.Start, Query33.End;


Ernst Guckel said:
Create a table with consecutive numbers zero through the maximum number of
days to fill. Create a query with your table and the count table not joined.
Click to expand...

Ok... tblCount has 32 records from 0 to 31.
SELECT YourTable.Start, YourTable.End, [start]+[Count] AS [Interval Date]
FROM YourTable, [Count]
WHERE (((Count.Count)<=DateDiff("d",[start],[end])));
Click to expand...

Actually there is only one field... [InvDate] has records between two
dates... There is no [Start] AND [Stop] not quite sure how to fix this...

Ernst.

:

Hey,

I have a query that looks up data in 'tblInventory' and returns
information that is between a date range. IE 'Between #7/1/05# AND
#7/31/05#. The problem is that there is not always data for each date in the
range. I am looking for a way to have the query return ALL dates in the
range.

Any ideas?

Thanks,
Ernst.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top