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

 

[TorF]

Dates in query must be embrace between #

Dim SeqText As String, Sdate As String, Edate As String
Sdate = Text2
Edate = Text4
SeqText = "SELECT * FROM Q_Final WHERE DateDone BETWEEN #" + Sdate + "# AND #" + Edate + "#"

 

[Kevsim]

TorF , Thank you for the info, however I am still having problems. 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. 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

 

[TorF]

Your query SeqText seems good.
But you don't use SeqText ! You don't modify the Q_FinalData query.

You can change the query using this:
Private Sub Command4_Click()
Dim SeqText As String
Dim stDocName As String
Dim db As Database
Dim Query As QueryDef

SeqText = "SELECT * FROM Q_FinalData WHERE DateDone BETWEEN #" + Text0 + "# AND #" + Text2 + "#"
stDocName = "Q_FinalData"

Set db = CurrentDb()
Set Query = db.QueryDefs(stDocName)
Query.sql = SeqText
DoCmd.OpenQuery stDocName, acNormal, acEdit
End Sub


Also you can use a query with parameters, but I don't exactly know its usage.
IE: you send to query DateStart and DateEnd parameters, so you don't have the modify every time the query sql.
Maybe someone else can explain how using parametred queries...

 

[Kevsim]

TorF, Once more thank you for the info. When I tried to run the code, an error message appears highlighting “Dim db As Database” and the error message “User defined-type not defined” could you please advise what may be wrong.
I am running Access 2000.
kevsim

 

[DonBerry]

Kevsim you prpbably need to go into references and check "microsoft DAO 3.6 object library" also move it up in priorty in the list closer to the top. I believe Access 2000 defaults to ADO.

Don

 

[Kevsim]

DonBerry, Thank you for the advise, it worked OK. However, I am still having a problem when I run the following code. After I run the code and try to open the query, I receive the error message “Circular reference caused by Query2” I can no longer edit the query in design view. At first I thought it may have been the last line of code, I deleted the line but the problem still existed. I then deleted the query and made another, first run through the code and the same problem. When I check SQL View in the query, it is as follows -
SELECT *
FROM Query2
WHERE DateWorkDone BETWEEN #3/12/02# AND #5/12/02#;
If I make a new query and paste in the above SQL it gives the same error without running the code. I would appreciate assistance in locating the problem.

Private Sub Command4_Click()
Dim db As Database
Dim Query As QueryDef
Dim Sdate, Edate
Dim SeqText As String
Dim stDocName As String
Stop
Sdate = Text0
Edate = Text2
SeqText = "SELECT * FROM Query2 WHERE DateWorkDone BETWEEN #" + Text0 + "# AND #" + Text2 + "#"
Set db = CurrentDb()
stDocName = "Query2"
Set Query = db.QueryDefs(stDocName)
Query.sql = SeqText
DoCmd.OpenQuery stDocName, acNormal, acEdit

kevsim

 

[TorF]

You update the SQL request in Query2, and this request takes data from Query2 too ! This cannot works...

You have to modify "From Query2" ("From TableX" ?) or the stDocName.