WHERE / BETWEEN for dates 3

[ItIsHardToProgram]

Hello!

As my thread indicates, I am trying to use the BETWEEN function with 2 dates in SQL. I had a feeling I had to convert the DATE to numbers and then reconvert to DATE.

I am wondering how to proceed when using the CAST in order for this to work.

This is my code that isn't currently working, see strSQL for the SQL:

Dim dDateBEG     As Long
Dim dDateEND     As Long

Private Sub DateBEG_AfterUpdate()
dDateBEG = DateBEG.Value
End Sub
Private Sub DateEND_AfterUpdate()
dDateEND = DateEnd.Value
End Sub




Private Sub Creer_Click()

Dim strSQL       As String


strEmplacement = CurrentProject.Path

MsgBox (dDateBEG)
MsgBox (dDateEND)



  
        
        
        strSQL = "SELECT T.IdProjet, T.IDEmployer , SUM(T.LundiRD+T.MardiRD+T.MercrediRD+T.JeudiRD+T.VendrediRD+T.SamediRD+T.DimancheRD)" & _
        "AS [Heures de R&D], T.semaine"
               
        strSQL = strSQL + " FROM tblTimeSheet AS T " & _
                          " WHERE CAST(T.semaine as integer) BETWEEN " & dDateBEG & " AND " & dDateEND & _
                          " GROUP BY T.IdProjet;"
                          
        DoCmd.OpenQuery (strSQL)
     
End Sub

Thanks for any help

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Andrzejek]

Did you try just simple:

SELECT * FROM tblTimeSheet 
WHERE semaine Between #2009/01/24# And #2010/05/16#

because if your semaine is a Date, it should work.

Have fun.

---- Andy

 

[SkipVought]

Did you try Andy's suggestion...

Please do:
Debug.Print strSQL

copy it from Immediate Window run it in Access. See if your SQL is correct and will even run and give you any outcome.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Yes, it runs, and gives me no result what so ever.

It would seem that access dosn't understand my dates, I've tried formating in every possible way without joy, my query looks like this right now and still returns not a single data.


Yes Andy, I just tried that simple querry and it is working, so it gets me wondering, maybe Access can't aggregate dates with idprojet and idemployer!

I would definatly need it to aggregate though...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

SELECT T.IdProjet, T.semaine, T.IDEmployer, SUM(T.LundiRD+T.MardiRD+T.MercrediRD+T.JeudiRD+T.VendrediRD+T.SamediRD+T.DimancheRD) AS [Heures de R&D]
FROM tblTimeSheet AS T
WHERE semaine Between #1/24/2009# And #5/16/2010#
GROUP BY T.IdProjet, T.IdEmployer, t.semaine;

The following is now returning as intended, I will code it in VBA and there shouldnt be any problems... Thanks, I guess I have to reformat my dates.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Andrzejek]

Looks like all you needed to do was:

Private Sub DateBEG_AfterUpdate()
dDateBEG = Format(DateBEG.Value, [blue]"mm/dd/yyyy"[/blue])
End Sub
Private Sub DateEND_AfterUpdate()
dDateEND = Format(DateEnd.Value, [blue]"mm/dd/yyyy"[/blue])
End Sub

Thank for the star, but others here deserve it more than me.

Have fun.

---- Andy

 

[ItIsHardToProgram]

Yes, it is infact what I needed to do.

I have no clue why though! Quite mystified actualy, its not the computers regular date format reading...

Any way! IT works now and as long as it works I am happy.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.