Filtering A Query By Start And End Date

[kevsim]

I have asked this question before, received some answers, but still can not fathom how to write the code in VB, everything I tried failed.
I have a query and want to filter the data by “Date Done”, and using a text box to enter the start date and another to enter the end date. I would appreciate how to do this using VB coding, would I also have to base this query on an Unbound form? I am using variables to accept Start and End date. Q_Final is the query I am trying to filter.

Dim SeqText
Sdate = Text2
Edate= Text4
SeqText = "SELECT * FROM Q_Final WHERE DateDone BETWEEN Sdate AND Edate”

Is the code above correct and if not what else is missing?
Your advise would be appreciated.
kevsim

 

[edtab]

Here's an example of selecting a date range (from - to) which a user has input on a form. You have to modify it accordingly.

SELECT tblOrders.RequiredDate, tblOrders.OrderDate, tblOrders.CompanyName, tblOrders.OrderedBy, tblOrders.CustomerPO
FROM tblOrders
WHERE (((tblOrders.RequiredDate) Between [forms]![frmDateRange]![FromDate] And [forms]![frmDateRange]![ToDate]))
ORDER BY tblOrders.RequiredDate;


edtab

 

[kevsim]

Edtab, Thank you for the info,I tried the code you provided but it did not work, so tried the following. I am using an unbound form with the below code. On the form I have Text Box 0 and Text Box 2, where I enter the dates to sort between. I also have a command button where the below code is in the On Click event procedure. After I enter the dates, I press the button. When the code operates there are no errors, the query opens the query in datasheet view, however, the dates are still not filtered. I selected the dates Dec 1 2002 for text box 0 and Dec 3 2002 for text box 2, but all other dates and info appear from Dec 3 on. I hate to be a pain, but could you please advise what I am doing wrong.
Private Sub Command4_Click()
Dim Sdate, Edate
Dim SeqText As String
Dim stDocName As String
Sdate = Text0
Edate = Text2
SeqText = "SELECT * FROM Q_FinalData WHERE DateDone BETWEEN #" + Sdate + "# AND #" + Edate + "#"
stDocName = "Q_FinalData"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End Sub
kevsim

 

[raskew]

Couple of problems:

SeqText does nothing. It's not related in any way to
Q_FinalData.

Use ampersands '&' rather than plus marks '+' in
your code.

Here's a working sample that you could adapt to
your requirements.

Function DateDone(sdate As Date, edate As Date)
Dim db As DATABASE
Dim qd As QueryDef
Dim SeqText As String

Set db = CurrentDb
SeqText = "SELECT Orders1.*" _
    & " FROM Orders1" _
    & " WHERE (([OrderDate] Between #" & sdate & "# And #" & edate & "#));"
Debug.Print SeqText
Set qd = db.CreateQueryDef("tQuery", SeqText)
docmd.OpenQuery ("tquery")
docmd.DeleteObject acQuery, "tQuery"
End Function

 

[Rich]

Only trouble is Bob that kevsim is in Oz, and will almost certainly therefore have to change the format of the dates to US style,
try
Dim db As DATABASE
Dim qd As QueryDef
Dim SeqText As String

Set db = CurrentDb
SeqText = "SELECT Orders1.*" _
& " FROM Orders1" _
& " WHERE (([OrderDate] Between #" & Format([sdate],"mm/dd/yyyy") & "# And #" & Format([edate],"mm/dd/yyyy") & "#));"
Debug.Print SeqText
Set qd = db.CreateQueryDef("tQuery", SeqText)
docmd.OpenQuery ("tquery")
docmd.DeleteObject acQuery, "tQuery"
End Function

 

[raskew]

Rich-

Good point! The date-format business between nations is just a real ugly problem. When I worked in a NATO Headquarters, with seven different nationalities represented, we resolved the problem by requiring everyone, regardless of nationality, to use the Medium Date format, e.g. (12-Dec-02). It was something everyone could understand and comply with. Closest we could come to an international format.

Bob

 

[kevsim]

I thank you all for the replies, I note the code is written as a Function, how woud I run the function when I press the command button using the On Click event for the date filtering?

 

[raskew]

Here's the code for a form with controls Text0, Text2 and Command4. The text boxes are formatted as Medium Date. Also made a minor change to Function DateDone in order to delete and recreate the query each time Command4 is clicked.

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Call DateDone(Me!Text0, Me!Text2)

Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click
    
End Sub

Function DateDone(sdate As Date, edate As Date)
Dim db As Database
Dim qd As QueryDef
Dim SeqText As String, Test As String, tName As String

Set db = CurrentDb
tName = "tQuery"
SeqText = "SELECT Orders.*" _
    & " FROM Orders" _
    & " WHERE (([OrderDate] Between #" & sdate & "# And #" & edate & "#));"
On Error Resume Next
'Does query tName exist?  If true, delete it;
Test = db.QueryDefs(tName).Name
If Err <> 3265 Then
   DoCmd.DeleteObject acQuery, tName
End If

Set qd = db.CreateQueryDef("tQuery", SeqText)
DoCmd.OpenQuery ("tquery")

End Function

 

[kevsim]

raskew, Thank you for the code, it is much appreciated, I will now try and use it.
Best Regards,
kevsim

 

[OPMCoordinator]

firstly, those in nz use uk english, and i strongly suspect that those in oz do the same!

secondly, i find if i am having trouble with sql in vb, i build it in the query design grid if possible and view it as SQL code and copy/paste as necessary. often a missed ( or ; or something is all that the problem is!

sounds like a fairly striaght forward select query based on the two text boxes. use the build option in the criteria bit of the query grid and check out the code!

Mike C
Kiwi and proud of it!

ps even simpler - build the query the normal way in the grid and make the button open the query!