Have to cycle through parameter values twice to pull info.

Ron:

I think the problem probably is that because you’ve used spaces in the form
name you need to enclose it in brackets whenever you refer to it in the query:

[Search By Date]

Ken Sheridan
Stafford, England


"Ron Weaver" wrote:

Ken I tried this and got the following message:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", "&''',specified on this form or
report does not exist.
This is the code I entered, showing the form my list box and text boxes are
in:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate = Forms!Search By Date!txtStart OR Forms!Search By Date!Start IS
NULL)
AND
(EndDate Forms!Search By Date!txtEnd + 1 OR Forms!Search By Date!End IS
NULL)
ORDER BY LastName, FirstName;
Do you see anything wrong with this, or did I miss a step?
"Ken Sheridan" wrote:

Ron:

Its pretty straightforward. Lets assume the form is called frmMyForm, the
two text boxes on the form are called txtStart and txtEnd and the list box is
called lstMyList. There are various ways you can update the list when dates
are entered in the text boxes, but lets say you want to update the list as
soon as either text box’s value is changed. Lets also assume that you want
each text box to an optional parameter, i.e. if you enter a date in txtStart
only you’ll get all rows on or after that date, if you enter a date in txtEnd
only you’ll get all rows on or before that date, if you enter both you’ll get
all rows on or after the first date and on or before the second date.

To do the above, as well as referencing the text boxes for a value you also
test for them being Null. As you are dealing with dates its also as well to
declare the parameters as DateTime as a value in short date format can
otherwise be interpreted as an arithmetical expression. So the RowSource
property for the list box would be along these lines:

PARAMETERS
Forms!frmMyForm!txtStart DATETIME,
Forms!frmMyForm!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate = Forms!frmMyForm!txtStart OR Forms!frmMyForm!Start IS NULL)
AND
(SatrtDate Forms!frmMyForm!txtEnd + 1 OR Forms!frmMyForm!End IS NULL)
ORDER BY LastName, FirstName;

The first expression within parentheses will evaluate independently to TRUE
if the date in the row is on or after txtStart or if txtStart is Null, i.e.
left blank. Similarly the second expression will evaluate to TRUE if the
date is before one day after txtEnd or txtEnd is Null. Defining the range in
this way is better than using BETWEEN….AND as that will not return any rows
on the last date if the values contain a non-zero time of day, i.e. the time
would have to be midnight at the start of the day, which is what you get if
you enter a date into a date/time column without a time of day. However,
non-zero times of day can easily creep in unseen, the usual culprit being the
inappropriate use of Now() as the default value. On the whole it is best to
play safe and assume that dates might also include a non-zero time of day.

To update the list box when a value is entered into either of the text boxes
you requery the list box in the AfterUpdate event procedure of each text box
with:

Me.lstMyList.Requery

You can further enhance this if you wish by including some validation code
in the BeforeUpdate event procedure of each text box to do things such as
check that a valid date has been entered (or the control has been made
Null),or that if both text boxes have dates txtEnd does not contain an
earlier date than txtStart. If the validation fails you cancel the update
with:

Cancel = True

This prevents the AfterUpdate event from firing so the list box is not
requeried until the validation criteria have been satisfied.

Ken Sheridan
Stafford, England

"Ron Weaver" wrote:

I have a form with a list box connected to a query. I am trying to limit the
list to criteria between two dates. It finally works, but not until I cycle
through the beginning and ending dates twice. Once in a while it works on the
first try. I know I am entering the correct values. Also, is there a tutorial
out there That shows how to create this same function on the form with two
text boxes?

Ken: I got it working. Thanks.

"Ken Sheridan" wrote:

Ron:

I think the problem probably is that because you’ve used spaces in the form
name you need to enclose it in brackets whenever you refer to it in the query:

[Search By Date]

Ken Sheridan
Stafford, England


"Ron Weaver" wrote:

Ken I tried this and got the following message:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", "&''',specified on this form or
report does not exist.
This is the code I entered, showing the form my list box and text boxes are
in:
Forms!Search By Date!txtStart DATETIME,
Forms!Search By Date!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate = Forms!Search By Date!txtStart OR Forms!Search By Date!Start IS
NULL)
AND
(EndDate Forms!Search By Date!txtEnd + 1 OR Forms!Search By Date!End IS
NULL)
ORDER BY LastName, FirstName;
Do you see anything wrong with this, or did I miss a step?
"Ken Sheridan" wrote:

Ron:

Its pretty straightforward. Lets assume the form is called frmMyForm, the
two text boxes on the form are called txtStart and txtEnd and the list box is
called lstMyList. There are various ways you can update the list when dates
are entered in the text boxes, but lets say you want to update the list as
soon as either text box’s value is changed. Lets also assume that you want
each text box to an optional parameter, i.e. if you enter a date in txtStart
only you’ll get all rows on or after that date, if you enter a date in txtEnd
only you’ll get all rows on or before that date, if you enter both you’ll get
all rows on or after the first date and on or before the second date.

To do the above, as well as referencing the text boxes for a value you also
test for them being Null. As you are dealing with dates its also as well to
declare the parameters as DateTime as a value in short date format can
otherwise be interpreted as an arithmetical expression. So the RowSource
property for the list box would be along these lines:

PARAMETERS
Forms!frmMyForm!txtStart DATETIME,
Forms!frmMyForm!txtEnd DATETIME;
SELECT StartDate, OrderID, LastName & ", " & FirstName AS
[Customer Name], Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE
(StartDate = Forms!frmMyForm!txtStart OR Forms!frmMyForm!Start IS NULL)
AND
(SatrtDate Forms!frmMyForm!txtEnd + 1 OR Forms!frmMyForm!End IS NULL)
ORDER BY LastName, FirstName;

The first expression within parentheses will evaluate independently to TRUE
if the date in the row is on or after txtStart or if txtStart is Null, i.e.
left blank. Similarly the second expression will evaluate to TRUE if the
date is before one day after txtEnd or txtEnd is Null. Defining the range in
this way is better than using BETWEEN….AND as that will not return any rows
on the last date if the values contain a non-zero time of day, i.e. the time
would have to be midnight at the start of the day, which is what you get if
you enter a date into a date/time column without a time of day. However,
non-zero times of day can easily creep in unseen, the usual culprit being the
inappropriate use of Now() as the default value. On the whole it is best to
play safe and assume that dates might also include a non-zero time of day.

To update the list box when a value is entered into either of the text boxes
you requery the list box in the AfterUpdate event procedure of each text box
with:

Me.lstMyList.Requery

You can further enhance this if you wish by including some validation code
in the BeforeUpdate event procedure of each text box to do things such as
check that a valid date has been entered (or the control has been made
Null),or that if both text boxes have dates txtEnd does not contain an
earlier date than txtStart. If the validation fails you cancel the update
with:

Cancel = True

This prevents the AfterUpdate event from firing so the list box is not
requeried until the validation criteria have been satisfied.

Ken Sheridan
Stafford, England

"Ron Weaver" wrote:

I have a form with a list box connected to a query. I am trying to limit the
list to criteria between two dates. It finally works, but not until I cycle
through the beginning and ending dates twice. Once in a while it works on the
first try. I know I am entering the correct values. Also, is there a tutorial
out there That shows how to create this same function on the form with two
text boxes?