Make the RowSource property of the list box a query which references the txt
boxes on the form, e.g.
PARAMETERS
Form!txtUpdateFrom DATETIME,
Form!txtUpdateTO DATETIME;
SELECT SomeField, SomeOtherField
FROM tblCourse
WHERE DateChanged >= Form!txtUpdateFrom
AND DateChanged < Form!txtUpdateTo + 1;
In the AfterUpdate event procedure of each text box put:
Me.List135.Requery
When the value in each text box is updated the list should be repopulated
with records within the date range.
Three things to note about the above query (which need not be a saved query
BTW, just an SQL statement as the RowSource property); firstly the parameters
are declared as DATETIME. This is because a value entered as a short date
format could be interpreted as an arithmetic expression rather than a date
otherwise, and give the wrong result; secondly, the form is referenced by the
Form property not by a full reference to the form by name. You could do the
latter, but when the controls are on the same form its not necessary;
thirdly, the date range is defined in the above way rather than by a
BETWEEN….AND operation. The above method is more bullet-proof as it caters
for sloppy data, i.e. where a value in the DateChanged column could
inadvertently include a non zero time of day as well as the date. This can
easily happen if precautions have not been taken to prevent it at design
time, and the time of day will be unseen if the value is formatted as a date
format. The usual culprit is the inappropriate use of the Now() function as
a default value.
Incidentally I'd recommend you get into the habit of renaming controls to
something meaningful as soon as they are added to a form rather than
accepting a default name like List135. Don't rename a control if code has
been entered into one of its event procedures, however; this will break the
link between the control and the code.
Ken Sheridan
Stafford, England
shn said:
I am trying to create a search form. I want my user to have the ability
to search records in a particular date range. I would like the results
to populate a list box where the user can double click the item needed
and open up the record in another form. I have been successful with the
double click feature in this list box although I am having trouble
getting the date range search to work and I am unsure how to get the
results to show up in the list box.
Here is my data:
List box name: List135
From Date Text Box Name: txtupdatefrom
To Date Text Box Name: txtupdateto
Field in table to be searched: [tblCourse].[DateChanged]
Thank you in advance for any help that you can provide.
