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.

 

[SkipVought]

Hi,

Just do the conversion #YourYYYY/MM/DDDateSTRING# in the SQL...

WHERE T.semaine BETWEEN #" & Format(DateBEG,'yyyy/mm/dd') & "# AND #" & Format(DateEND,'yyyy/bb/dd') & "#"

Skip,

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

 

[PWise]

try

 " WHERE T.semaine  BETWEEN #" & dDateBEG & "# AND  #" & dDateEND   "# GROUP BY T.IdProjet;"

 

[PHV]

CAST isn't an JetSQL operator, so I guess you should ask in a more relevant forum like, say, a SQL Server one.

Anyway, what is the datatype of tblTimeSheet.semaine ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Some assumtion:
semaine field in your tblTimeSheet table is a Date, right?
DateBEG and DateEND are some controls (date picker maybe?) that return Date, right?

Then:

Dim dDateBEG [blue]As Date[/blue]
Dim dDateEND [blue]As Date[/blue]

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

Code not tested.

Have fun.

---- Andy

 

[ItIsHardToProgram]

Sorry for posting in the wrong post, It was the only forum I found at the time for SQL.

Andy's assumptions are correct, Tbltimesheet.semaine is a Date,I had tried running the 2 as dates before and it did not work.

In fact I tried your corrections, considering andy's the same as yours PHV, and I am still getting an error on the cmd trigger.

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;"
                          
        [b]DoCmd.OpenQuery (strSQL)[/b]

End Sub

This is what Im using. The runtime error is on DoCmd.



Thank you for your prompt responses, I could not find any threads anywhere on this, besides really complex ones with CAST / Convert

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

 

[ItIsHardToProgram]

As for your fix Skip I can't even get it to work, vba dosn't accept the way format is entered. >.<

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

 

[PHV]

I'd try something like this:

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
...
strSQL = strSQL & " FROM tblTimeSheet AS T " & _
  " WHERE T.semaine BETWEEN '" & dDateBEG & "' AND '" & dDateEND & "' " & _
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

Hey!

Still getting a runtime error (7874) on the following PHV suggestion:

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

Thanks for continued help!

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

 

[ItIsHardToProgram]

FYI I have created a thread on SQL Server: Reporting services

You might want to relocate there and lock the thread!

Thanks.

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

 

[PHV]

What is the WHOLE error message ?

Anyway, your GROUP BY clause should be:
" GROUP BY T.IdProjet, T.IDEmployer, T.semaine"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

Yes, so I put the function into the Acess built in query creator, and it asked for group by's for all 3 mentioned above, wich I did, then I get a criteria mismatch.

The whole error message is that there is a run time error (error 7874)

Access can't find the following object: "Select etc....)

I fixed the group by, now I get a criteria error in the querry view.

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

 

[ItIsHardToProgram]

Sorry criteria mimsatch = 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.

 

[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.

 

[PHV]

So, in the SQL view of the query, replace the single quotes with [!]#[/!] for the Between opreands.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

The following gives me a syntax error on the WHERE clause:

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 T.semaine Between #2009-1-24# And #20010-5-16#
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.

 

[Andrzejek]

Instead of
[tt]DoCmd.OpenQuery (strSQL)[/tt]

Please do:
[tt]Debug.Print strSQL[/tt]

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

Have fun.

---- Andy

 

[PHV]

Replace this:
#20010-5-16#
with this:
#2010-5-16#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

I have, still no data...

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

 

[ItIsHardToProgram]

So I fell on the following that helped greatly on how to use dates through SQL.

http://www.eggheadcafe.com/software/aspnet/32567949/using-between-to-seach-fo.aspx

Even then, I seem to be unable to get my querry to return any data. I am able to run it in VBA now.

Any help would be greatly appreciated.

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