[RESOLVED] Access sql, return all dates

[shragel]

I am using access and i would like to create a sql query that would return all dates.

For example

Select AllDate From Dates Where Date Betwwen #7/01/05# And #7/5/05#

it should return

7/01/05
7/02/05
7/03/05
7/04/05
7/05/05


Without having to create a table Dates.

Thanks for any help.

[GaryMazzone]

If you don't have a table Dates whay would you want to query the database?

Use a for loop or a Do Loop to generate the dates and store them in some sort of structure.

[Static]

create a function like this:

Private Function AllDates(Dates() As Date, dStart As Date, dEnd As Date)
ReDim Dates(0)
For x = 0 To DateDiff("d", dStart, dEnd)
    Dates(x) = DateAdd("d", x, dStart)
    If x <> DateDiff("d", dStart, dEnd) Then
        ReDim Preserve Dates(x + 1)
    End If
Next
End Function

use like:
Dim Dates() As Date
AllDates Dates(), #8/1/2005#, #8/10/2005#

now Dates contains 8/1 to 8/10

[shragel]

I figured out an alternative way. But i would still like to know if you can do it thru a query.

[Static]

this may work.. but not for Access... this I think is PLSQL

SELECT TRUNC(SYSDATE)||','||TRUNC(SYSDATE - 1)||','||TRUNC(SYSDATE - 2) FROM DUAL

[RobDog888]

You do have a field "AllDate" which contains dates? If so a small modification to your original query is all you need.

Select AllDate From Dates Where AllDate Between #7/01/05# And #7/5/05#

Edit: Just seen that you dont want the table Dates. Hmm...

[Static]

Rob.. there isnt even a TABLE with any dates...
shragel is trying to return a range of dates from without having a table with dates in them

[shragel]

no. no fields and no table

Just like doing

select #7/01/05# from AnyTable

Would return 7/01/05

I want something that will return a range of dates.

[RobDog888]

Ok, then what needs to happen is is a dynamic field alias in the sql statement.

[Static]

shragel is this Access ? PLSQL?

[shragel]

how do you do that?

[shragel]

Access

[RobDog888]

But if there is no table then whats the point? You need to have a table or query to select from else this is not a query type issue.

What exactly are you trying to do?

[shragel]

Lets say i have a table with two fields Date, Amount

I want to crerate a report with every date of a rang and the sum amount of that day. But i want every day to come up in the report not only days that an amount has been entered.

[Static]

ok Trying this:
SELECT #8/1/2005#,#8/2/2005#

worked.. but it returns each Field as a date

so if you did

rs.open "SELECT #8/1/2005#,#8/2/2005#"

then rs.fields(0).value = 8/1/2005

[RobDog888]

Even with that you would still need to add in the Amount field. What table is Amount coming from? Are you using Access reporting or CR? If CR you can use "Repeat page header on every page" option, or such. Then only need to worry about the amounts as the dates will be displayed on every page in a textbox.

[shragel]

I would make it 0

For example
Select Mydate Sum(Amount) From MyTable Where Date between #7/01/05# AND #7/05/05# Group By MyDate

will return

7/01/05 50
7/03/05 60
7/04/05 110

I want to get

7/01/05 50
7/02/05 0
7/03/05 60
7/04/05 110
7/05/05 0

[RobDog888]

Ok, you could LEFT JOIN that query (without the MyDate) on to Geoff's latest sql example. The LEFT JOIN says to take everything from the first table and only join in the rows from the second table where the join is equal.

Giving you:
7/01/05 50
7/02/05
7/03/05 60
7/04/05 110
7/05/05

[shragel]

Geoff's latest sql example

Which Example

[RobDog888]

"SELECT #8/1/2005#, #8/2/2005#, #8/3/2005#, #8/4/2005#, #8/5/2005#, Sum(Amount) FROM MyTable WHERE MyDate BETWEEN #8/1/2005# AND #8/5/2005# GROUP BY #8/1/2005#, #8/2/2005#, #8/3/2005#, #8/4/2005#, #8/5/2005#;"

I think that may do it even easier.

[shragel]

Leaving now. Will try it in the afternoon, Thanks all

[Static]

Problem:

that will return 1 Row
Fields 0 - 4 will be a date, field 5 will be the sum...
Im trying to figure out a way to have rows.. not cols with it

[Static]

wait.. if you dont have a date in the table.. the sum() will be the same in every row?

this is getting confusing...
where is amount coming from? another table?
ah.. wait.. you need to return Days even if there is no data for that day!
does the table with the amount have a date field? if so what is it?

[RobDog888]

I think this issue is best solved in the report and not in the query. Which reporting are you using?

[Static]

ok .. best way is to create a temp table fill with dates..use for query then drop table

oConn.Execute "CREATE TABLE tmpDates (Dates DATE)"
For x = 0 To 10
    oConn.Execute "INSERT INTO tmpDates VALUES(#" & DateAdd("d", x, Date) & "#)"
Next
rs.Open "SELECT tmpDates.Dates, Sum(IIf(IsNull([Table2].[Amount]),0,[Table2].[AMount])) AS Amount " & _
        "FROM tmpDates LEFT JOIN Table2 ON tmpDates.Dates = Table2.DateField " & _
        "GROUP BY tmpDates.Dates", oConn
Do While Not rs.EOF
    Debug.Print rs.Fields(0).Value & ": " & rs.Fields(1).Value
    rs.MoveNext
Loop
rs.Close
oConn.Execute "DROP TABLE tmpDates"

resulted in this:

8/3/2005: 25
8/4/2005: 0
8/5/2005: 16
8/6/2005: 0
8/7/2005: 0
8/8/2005: 0
8/9/2005: 0
8/10/2005: 0
8/11/2005: 0
8/12/2005: 0
8/13/2005: 0

[techgnome]

Ok.... bottom line...there isn't a way to automagicaly do this in Access.... If this were SQL Serv, or something that allowed stored procs, then there's a solution.... but in this case there isn't a direct, do it all in one query, and make it work in Access solution. You have to have some kind of data source that can supply all the dates in a given range. That's where the displayed date is going to come from. It can't come from the table with the amount in it, because it simply does not exist. Once you have the date source, then you can left join it to the amounts table, using the date. Then and only then can you have contiguous, dates, with their related amounts for a given range, even when no amount exist.

EDIT: I've just seen [LGS]Static's last post.... while not very elegant, it does get the job done.... or at least seems to... But one thing that was never answered was what kind of report is this? Access? simply data in a grid? Crystal? OTher?


Tg

[RobDog888]

TG is right and verified my LEFT JOIN proposal (#18). But the poster doesnt want to have to create another table Geoff.

[Static]

Rob,
this:
"SELECT #8/1/2005#, #8/2/2005#, #8/3/2005#, #8/4/2005#, #8/5/2005#, etc..

will return:
Field1.... Field2.... Field3... etc
8/1/2005 8/2/2005 8/3/2005 8/4/2005 8/5/2005

not
8/1
8/2
8/3
right?
or am I looking at it wrong?
It seemed to in my tests...

[RobDog888]

Yes, we both made that mistake Fields and not Rows in one field.

[shragel]

Thanks All. It seems it cannot be done. I was ComponentOne VSReport. Its very similar to access reporting.