Search by date

[Nils Pettersson]

Hi,

I'm using Access 2003. I've created a form that looks up a list of records
from a query.

In the form, apart from showing records from the underlying query I've also
included a text box for the user to put a date in. This text box should done
refresh the query to show only records with the date entered by the user.
Basically a search box for dates.

When I run the form, it returns empty.

I've search for solutions and I was not able to find one. I've tried using a
function procedure I created called "fncDate()" in the criteria of the query
and even directly referring to the text box in the form to no avail.

I've got the idea in my head that the form will show records from the query
by yesterdays date by default and that the user can change the date on the
text box then hit return or tab and the records will refresh itself.

I hope you can help me out. Thanks.

 

[Jim Bunton]

Say the field to enter the query date in is called QueryDate
The first two subs below you create by clicking the appropriae entry on the
forms/ filed property list in design mode [if the property list isn't
displaying right click the object and select properties from the drop down.

then:
Private Sub Form_Open(Cancel As Integer)
me.Querydate = DateDiff("d",-1, Date())
me.recordsource = ConstructSql()
me.requery
end sub


Private Sub Querydate_AfterUpdate()
me.recordsource = ConstructSql()
me.requery
End Sub

Private Function ConstructSql() as string
Dim sql as string
sql = "SELECT Transactions.TranId, Transactions.TranDate,
Transactions.Who"
sql=sql & "FROM Transactions"
sql=sql & " WHERE Trandate = " & CvDate(me.querydate)
ConstructSql = sql
End Function

This is written 'freehand' so may contain some inaccuracies!
Any way when you have it working you can add buttons to flip the date back
and forwards a day at a time and fun things like that!

 

[Nils Pettersson]

Thanks for the quick reply, Jim. What I was trying to do was almost correct.
You see, I was using a query that I've created for this form and used it as
the recordsource. Then I put the reference to the textbox as the criteria in
the query.

Then in the textbox default value property I've put in a date as a test
thinking that when I run the form the default value date will automatically
become the criteria of the query. But nothing happened. It only worked when I
followed your example :

Private Sub Form_Open(Cancel As Integer)
me.recordsource = "myQuery"
me.requery
end sub

And then my other mistake was instead of using the afterupdate event for the
textbox, I used the lostfocus event with me.requery only.

Thanks again! This is was a great of fresh air. I can now stop pulling my
hair out.

Say the field to enter the query date in is called QueryDate
The first two subs below you create by clicking the appropriae entry on the
forms/ filed property list in design mode [if the property list isn't
displaying right click the object and select properties from the drop down.

then:
Private Sub Form_Open(Cancel As Integer)
me.Querydate = DateDiff("d",-1, Date())
me.recordsource = ConstructSql()
me.requery
end sub


Private Sub Querydate_AfterUpdate()
me.recordsource = ConstructSql()
me.requery
End Sub

Private Function ConstructSql() as string
Dim sql as string
sql = "SELECT Transactions.TranId, Transactions.TranDate,
Transactions.Who"
sql=sql & "FROM Transactions"
sql=sql & " WHERE Trandate = " & CvDate(me.querydate)
ConstructSql = sql
End Function

This is written 'freehand' so may contain some inaccuracies!
Any way when you have it working you can add buttons to flip the date back
and forwards a day at a time and fun things like that!

Hi,

I'm using Access 2003. I've created a form that looks up a list of records
from a query.

In the form, apart from showing records from the underlying query I've
also
included a text box for the user to put a date in. This text box should
done
refresh the query to show only records with the date entered by the user.
Basically a search box for dates.

When I run the form, it returns empty.

I've search for solutions and I was not able to find one. I've tried using
a
function procedure I created called "fncDate()" in the criteria of the
query
and even directly referring to the text box in the form to no avail.

I've got the idea in my head that the form will show records from the
query
by yesterdays date by default and that the user can change the date on the
text box then hit return or tab and the records will refresh itself.

I hope you can help me out. Thanks.