Using WHERE / BETWEEN with dates

[ItIsHardToProgram]

As I have been suggested, I am posting my problem in a more appropriate forum.

I am trying to use the between clause with dates in a VBA generated SQL. I have the following code wich errors on the DoCmD.

Option Compare Database

Dim dDateBEG     As Date
Dim dDateEND     As Date

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


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 T.semaine BETWEEN #" & dDateBEG & "# AND #" & dDateEND & _
                "# GROUP BY T.IdProjet;"
                          
        DoCmd.OpenQuery (strSQL)
     
    


'DoCmd.SetWarnings True
End Sub

Thank you!

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

 

[RiverGuy]

My guess is that you want to use single quotes instead of pound signs around your dates.

 

[ItIsHardToProgram]

As PHV suggested on the other thread (JET SQL SERVER)

My code is currently as is:

Option Compare Database

Dim dDateBEG     As String
Dim dDateEND     As String

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




Private Sub Creer_Click()

Dim strSQL       As String


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 T.semaine BETWEEN '" & dDateBEG & "' AND '" & dDateEND & "' " & _
                " GROUP BY T.IdProjet;"
                          
        DoCmd.OpenQuery (strSQL)
     
    


'DoCmd.SetWarnings True
End Sub

It still runtimes error(7874) on the Query open.

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

 

[RiverGuy]

I see what it is. You're selecting 3 columns and aggregating 1. You're then grouping by just one column. You need to group by T.IDEmployer and T.semaine as well.

 

[ItIsHardToProgram]

Aggregating 3, yet still getting the same error!

When putting the querry in the querry creator, it says data type mismatch in criteria selection.

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

 

[RiverGuy]

So then your datetime columns are not defined as datetime?

 

[ItIsHardToProgram]

Moving on, I tried removing the ' from the function and it finaly returns a querry.

Unfortunatly that querry is empty even though there are tons of data inbetween the 2 selected dates. The data is also currently sorted by dates, for testing, in case it only traps in between without recognizing that 2009-01-24 < 2009-04-21 for example.

Here is what the specific querry would look like:

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
FROM tblTimeSheet AS T
WHERE T.semaine Between 2009-1-24 And 20010-4-25
GROUP BY T.IdProjet, T.IdEmployer, t.semaine;

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

 

[ousoonerjoe]

Correct me if I'm wrong here, but the last date in the WHERE line has a 5 digit year. Also, without using ' or # the system will try to just do math against the date shown. For example, 2009-1-24 = 1986 and 20010-4-25 = 19984. Verify your data type for the column.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[NiceArms]

I had a whole world of pain with dates and vba/sql queries.
Use Hash # around the date fields in your query:

"WHERE Adate between #" & Datevar & "#" etc, etc.

Private Sub button_Click()
    Dim SQLstr As String
    Dim returnval As Integer
    Dim startdate As Date
    Dim enddate As Date
    
    startdate = Format("01/01/2009", "yyyy-MM-dd")
    enddate = Format("19/01/2009", "yyyy-MM-dd")
    
    SQLstr = "SELECT SUM(amount) AS AMOUNT1 FROM TABLE1 WHERE adate [b]BETWEEN #" & startdate & "# AND #" & enddate & "#"[/b]
        
    With CurrentDb.OpenRecordset(SQLstr)
        If Not .EOF Then
                text1.Value = .Fields("AMOUNT1")
                .Close
        Else
                MsgBox ("no data")
                .Close
        End If
    End With
End Sub

Also as "ousoonerjoe" said make sure the data type for the date field is correct.

/Nice